MySQL Bugs: #1824: replication fails for duplicate in primary key, which isn't true on master

Bug #1824	replication fails for duplicate in primary key, which isn't true on master
Submitted:	12 Nov 2003 20:59	Modified:	10 Dec 2003 9:11
Reporter:	Ken Menken	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.13 (-log)	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any

Description:
We successfully set up master-slave replication from machine A to machine B, and then wanted to create A->B->C when we discovered this error. We later repeated it using B->C but we only find this error on machine C.

What happens is that replication fails because of ERROR: 1062  Duplicate entry '4312612' for key 1

This field is described as follows:
Field        | Type          | Null | Key | Default | Extra
id           | int(11)       |      | PRI | NULL    | auto_increment

What is bizarre about this error is that the id is not even set in the query supposedly causing the error. The query sets all 17 other columns but not the id.

We were able to circumvent the problem for now and avoid the issue by changing 'id' to an integer, not auto_increment -- and, of course, the query turns out not to set the value at all but simply leaves it as 0, the default.

Note that other queries which add new records to other tables with id fields do not have this problem!

How to repeat:
In this case, simply start replication -- soon enough reading in from the other machine will cause replication to err and stop.

Would it be possible for you to upgrade to the latest MySQL 4.0 release and see if the issue arises again?  I am unsure if this applies to your specific situation, but there have been replication-related bug fixes in subsequent releases, one such being a correction to an issue introduced in version 4.0.13 that involved replicating auto_increment columns.

Thank you

We upgraded the slave to 4.0.16 and it seems like it took longer to crash -- but crash it did, with the same problem. Do we need to upgrade the master as well? It's an active site, so we're more leery about upgrades to the master.

Thanks!

If the particular bug mentioned above is causing the problem then yes, as the bug is actually on the master and not on the slave.

We are having the same problem on our windows 2000 installation. Upgrading to 4.0.16 on the slave has resulted in fewer "duplicate key" slave thread crashes, but now our slave is now routinely out of sync. We have reviewed the binlog on the master and there do not seem to be any problems. It appears that certain insert statements are being ignored by the slave.

Chris,

Yours may be a different issue (were you careful to take a new snapshot before restarting replication post-upgrade?). For us, the change to 4.0.16 did fix the problem -- replication no longer stops and, at least to the best of our knowledge, the tables are in sync.

As far as I'm concerned this bug report can be closed (I forgot to do that, sorry) -- but you may want to open a new one for your Windows replication issue.

Yours,

Ken

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Ken, I am marking the bug closed as you said 4.0.16 fixed the problem.
Chris, feel free to enter your problem as a separate bug, if needed.