Mixing transaction-savvy InnoDB and transaction-ignorant MyISAM adds quite a
wrinkle to it. I don't know enuf about the innards to dig any deeper.
Potential workaround: do it in chunks:
for (a,z) = ...
INSERT...SELECT ... WHERE key between a and z
> -----Original Message-----
> From: Baron Schwartz [mailto:baron@stripped]
> Sent: Tuesday, October 24, 2006 6:09 AM
> To: internals@stripped
> Subject: Re: Statement gets written to binlog after deadlock
>
> In this particular case, the program would have retried the
> statement that deadlocked:
>
> INSERT into MyISAM temp table
> SELECT from InnoDB table join MyISAM temp table join MyISAM temp table
>
> The tables it's selecting from have millions of rows. I
> wonder if that has something
> to do with it. The statement takes an hour sometimes, and I
> can see the transaction's
> undo log entries increase then reset to zero over and over.
> I wonder if this means
> some entries are being flushed to a log even though the
> statement hasn't finished.
> (I'm just guessing; I don't really know what it means). I
> have always been curious why
> the undo log entries would grow, then start over at zero,
> then grow again -- if the
> transaction is really being serialized into the binlog, I'd
> think nothing could be
> written until everything is done, so I'd expect the undo log
> entries to steadily
> increase until it's done.
>
> However, that's just one case. I'm pretty sure we have small
> deadlocks that cause the
> same problem, but I can't find any examples right now.
>
> Rick James wrote:
> > What action to you take when the deadlock causes the
> transaction to fail?
> > Maybe there is something subtle going on there.
> >
> >> -----Original Message-----
> >> From: Baron Schwartz [mailto:baron@stripped]
> >> Sent: Monday, October 23, 2006 10:29 AM
> >> To: internals@stripped
> >> Subject: Statement gets written to binlog after deadlock
> >>
> >> We have been having persistent issues with statements causing
> >> deadlock, yet being
> >> written to the binlog anyway, thus replicated through to the
> >> slave, where of course
> >> there is no deadlock. The error message is "Query caused
> >> different errors on master
> >> and slave. Error on master: 'Deadlock found when trying to
> >> get lock; try restarting
> >> transaction' (1213), Error on slave: 'no error' (0)"
> >>
> >> I can only find one bug related to this:
> >> http://bugs.mysql.com/bug.php?id=12495
> >>
> >> The problem with filing a bug report on it is that it's not
> >> repeatable. We see it 8 or
> >> ten times a week, then not at all for several weeks. I don't
> >> think we can possibly
> >> file a usable bug report. We have lots of deadlocks, but
> >> most of them don't cause the
> >> error.
> >>
> >> Does anyone have any ideas about how to solve this issue, or
> >> create a good bug report
> >> that won't stagnate? Should we just add comments to the
> >> existing bug report and hope
> >> that gains it some attention?
> >>
> >> We're using MySQL's 5.0.21 release.
> >>
> >> Thanks
> >> Baron
> >>
> >> --
> >> Baron Schwartz
> >> http://www.xaprb.com/
> >>
> >> --
> >> MySQL Internals Mailing List
> >> For list archives: http://lists.mysql.com/internals
> >> To unsubscribe:
> >> http://lists.mysql.com/internals?unsub=1
> >>
> >>
> >
>
> --
> Baron Schwartz
> http://www.xaprb.com/
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?unsub=1
>
>
