MySQL Bugs: #29728: INSERT ... ON DUPLICATE KEY UPDATE syntax fails on replication slaves

Bug #29728	INSERT ... ON DUPLICATE KEY UPDATE syntax fails on replication slaves
Submitted:	11 Jul 2007 17:54	Modified:	13 Aug 2007 20:21
Reporter:	Justin Sheckler	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.36sp1-enterprise-gpl-log	OS:	Solaris (i386)
Assigned to:		CPU Architecture:	Any
Tags:	insert, ON DUPLICATE KEY, replication, slaves, UPDATE

Description:
INSERT ... ON DUPLICATE KEY UPDATE is not replication-safe.  This doesn't appear to be mentioned in the manual page for INSERT.  UPDATES using this syntax aren't propagated to the slaves (although INSERTS are.)

Workaround is to use REPLACE instead of INSERT ... UPDATE.

How to repeat:
Assuming that these commands are run on a master server which has one or more slaves:

create table foo (id int(10) not null primary key auto_increment, fnord varchar(255));

insert into foo (id, fnord) values (1, "one") on duplicate key update fnord = values(fnord);

Master will have (1, "one"); slave will have (1, "one"); (Row was inserted.)

insert into foo (id, fnord) values (1, "two") on duplicate key update fnord = values(fnord);

Master will have (1, "two"); slave will have (1, "one"); (Row was updated.)

replace into foo (id, fnord) values (1, "three");

Master will have (1, "three"); slave will have (1, "three"); (Row was deleted, then inserted.)

Suggested fix:
Either make this command replication-safe, or post a prominent warning on the INSERT manual page.

Thank you for a problem report. As you are a customer, I suggest opening a new support issue for this problem. Do you agree?

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

Thank you for the report.

This bug has been fixed in version 5.0.38 by fix of Bug #24432.