MySQL Bugs: #12495: Replication

Bug #12495	Replication
Submitted:	10 Aug 2005 14:04	Modified:	24 Oct 2005 4:05
Reporter:	Florian Rissner	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.8 and 4.1.13	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
got a strange error which stopped replication:

050810 15:38:54 [ERROR] Slave: 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). Default database: [..], Error_code: 0

why does slave stop, due to the statement caused no error on slave?

will slave replicate any statement if i set slave-skip-errors=0 (because error, which stopped slave, is 0)?

master = 4.1.8, slave = 4.1.13

How to repeat:
create any deadlock on master and watch slave

Suggested fix:
only report a warning if query creates errorcode 0 on slave an continue working

Could you please provide a test case how you did the deadlock
and other information which permits to repeat the behavior reported.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I've encountered the same situation here.

The deadlock was caused by a shell script running the following commands:

mysql -u list_admin -e "drop table listing_temp"
mysql -u list_admin -e "create table listing_temp as select licenses.id,hosts.hostid,hosts.serialnumber,licenses.partnumber,parts.description, licenses.license_key, licenses.expiration, licenses.users from licenses,hosts,parts
 where hosts.status = 'A' and
  hosts.id = licenses.hid and
 licenses.partnumber = parts.partnumber"

In the binary log files on both the master, and the relay log file in the slave (makes sense, since it's a copy), the "drop listing_temp" command is completely missing!

So in my case, the slave actually reported the error "table already exists"

Reading further into the log files, the missing "drop listing_temp" entry occurs about once every 100 runs of the script (~3 days).

Master:  mysql  Ver 12.21 Distrib 4.0.15, for portbld-freebsd4.9 (i386)
Slave:  mysql  Ver 14.7 Distrib 4.1.13, for unknown-freebsd4.7 (i386) using readline 4.3

Hope this helps!

By the way, I've remedied the race condition that seems to occur (and thus cause the deadlock?) by adding a "sleep" command between the two shell-executed sql commands.