MySQL Bugs: #17684: Replication Breaks due to Duplicate Key of ID 0

Bug #17684	Replication Breaks due to Duplicate Key of ID 0
Submitted:	24 Feb 2006 1:05	Modified:	6 May 2006 16:19
Reporter:	Eric Filson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (Linux RHE 4)
Assigned to:		CPU Architecture:	Any

Description:
Single Master, Single Slave setup INSERTs, UPDATEs, and DELETEs only allowed on Master.  Auto_Incremented table with a single index.  Replication breaks on:

060223 17:37:00 [ERROR] Slave: Error 'Duplicate entry 'XXXXXXX' for key 1' on query. Default database: 'any_database'. Query: ' INSERT INTO table_name (table_name_id) VALUES (0)', Error_code: 1062

This seems to be an impossibility.  This query should only create a blank row in the table.  This happens at least once a day in multiple tables (both high and low concurrency).  

Tables are MyISAM and I have run check table on both master and slave for each table producing the error which produced "ok" status for each.

I have also run repair table for each just in case but errors are still being produced for those tables.

Please help, I'm loosing all my hair over this one.  I run a network of websites with a high amount of traffic and my entire user base is about to strangle me :P

How to repeat:
Setup replication, single master, single server.

Create table with single auto_incremented index. Perform:

INSERT INTO table_name (table_name_id) VALUES (0)

UPDATE table_name SET a=1, b=2, c=3

Might want to try INSERTs and UPDATEs in mixed sets (e.g. INSERT, INSERT, UPDATE, INSERT, UPDATE, UPDATE) through software...

Suggested fix:
Unsure

Thank you for a problem report. Are both your master and slave running on 5.0.18? Are you sure your tables are identical, and has the same data in them initially?

To put it short: Can you provide a set of statements (starting with CREATE TABLE) that demonstrates this behaviour each and every time?

Yes, both Master and Slave are running 5.0.18 and the Master and Slave have identical initial data.  They are also running identical OSs and configurations for all libraries, scripts, and software.  This problem does not happen everytime the query is run, it's very inconsistant.  I have changed the query to read:

INSERT INTO table_name (table_name_id) VALUES (0) ON DUPLICATE KEY UPDATE table_name_id=0;

This issue has not repeated itself since the command was changed around 20 hours ago.  I will continue to monitor and post results here in 48 hours.  I will also mysqldump and compare both data sets and report any inconsistancies for the same report.  

Hope all this helps.

Thank you for the additional information. Please, inform about your further findings.

With the statement changed to add:

INSERT INTO table_name (table_name_id) VALUES (0) ON DUPLICATE KEY UPDATE
table_name_id=0;

One of the errors disappears.  I realized that I was receiving a seperate error for high concurrency tables as opposed to low concurrency tables.  High concurrency tables were returning the error:

060223 17:37:00 [ERROR] Slave: Error 'Duplicate entry '0' for key 1' on
query. Default database: 'any_database'. Query: ' INSERT INTO table_name
(table_name_id) VALUES (0)', Error_code: 1062

The low concurrency tables were returning the error:

060223 17:37:00 [ERROR] Slave: Error 'Duplicate entry 'XXXXXXX' for key 1' on
query. Default database: 'any_database'. Query: ' INSERT INTO table_name
(table_name_id) VALUES (0)', Error_code: 1062

After adding in the additional: ON DUPLICATE... The error for low concurrency tables which always includes an actual value for the key disappears.  The only error remaining is the high concurrency table with the id field value returning a 0.  Which is even more mysterious :P

If you would like I can provide the binary log files.  I would recommend creating a work around for this problem by adding something like:

ON DUPLICATE KEY DO NOT UPDATE

This would at least allow the replication NOT to break.  This would be of HUGE help.  Right now I have had to shut off tracking on my visitor table...  Please let me know what else I can provide you with in order to help you trouble shoot this issue.

Sorry for a long delay with porcessing this bug report. Please, try to repeat with a newer version of master and slave, 5.0.19. If the problem is still repeatable, I will need either a repeatable test case (right mix of INSERTs and UPDATEs) or a binary log that is not properly replicated, from your master.

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".