MySQL Bugs: #53194: REPLACE INTO breaks replication

Bug #53194	REPLACE INTO breaks replication
Submitted:	27 Apr 2010 11:06	Modified:	28 May 2010 11:27
Reporter:	Davide Ferrari	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.32	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	replace into, replication

Description:
I've got a "replace into" which executed correctly on the master but then broke all my slaves with the same erro: 'Duplicate entry '43375468' for key 'PRIMARY''

This is quite strange cause as subject says we were using REPLACE INTO to update already existing primary keys.

How to repeat:
I haven't tried to repeat it right now cause I can't break replication again, it's a production system

Many thanks for writing a bug report.

I am not able to repeat this.

Are you using row based or statement based replication?

I'm using statement based replication. The master is a very old 5.0.32 installation (we are planning to update it).
Replication broke on 4 different slaves (3 x86_64 Linux with 5.1.44 and 1 Solaris Sparc64 with 5.1.40) which are read-only

This is not a bug.

To be more correct ... we have had a bug in statement based replication about auto_increment handling ... this bug is fixed in MySQL 5.1.

More informations about the problem you run into you will find here:

http://dev.mysql.com/doc/refman/5.0/en/replication-features-auto-increment.html

Susanne, I'm not questioning your resolution as "not a bug" but I don't understand where http://dev.mysql.com/doc/refman/5.0/en/replication-features-auto-increment.html explains what happened in my case. Can you tell me more? It's not an alter, it's not a routine/stored procedure, it's not an insert dealayed and I'm not copying any table.

Sorry, I'm opening it again cause I'm not convinced by your explication and we are still running in this replace into problems. Moreover the same sentence executed manually in the slave executed flawlessly.

Yesterday we updated the master to 5.0.44 and this bug seems to be gone. Thanks and sorry.

Hello Davide,

we have had lots of problems in 5.0 with replication of auto_increment.

I thought the bug about filling it via stored procedures or triggers that the real source of this is that we had a problem filling fixed/given values into auto_inc columns.

Anyway, I just thought about how I could tell you that updating the master will help and then you already figured it out by yourself.