MySQL Bugs: #40872: Slave doesn't get replicated properly with ON DUPLICATE KEY UPDATE statements

Bug #40872	Slave doesn't get replicated properly with ON DUPLICATE KEY UPDATE statements
Submitted:	19 Nov 2008 23:09	Modified:	20 Dec 2008 6:51
Reporter:	Kayra Otaner	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45, 5.0.51a (ICC)	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	ON DUPLICATE KEY UPDATE, replication

Description:
I have stored procedures to update certain aggregation tables every night. These stored procedures update aggregation tables by overriding last 5 day's data using
ON DUPLICATE KEY UPDATE v1=VALUES(value), cdate=VALUES(cdate) in the insert statement.
I have 4 aggregation tables, 4 stored procedures. Unique constraint is defined on either 4 or 5 columns.
This worked fine for about 2 years with no problem. About 2 weeks ago a change in the java code caused stored procedures executed against master host with binlog disabled. So data didn't get replicated to the slave for >5 days. When I noticed it, we just enabled binlogging on the java code to write updates to the table. When the stored procedure is executed next night, it ran for last 5 days and data populated on the master properly as it was expected, but the slave had the data captured/calculated almost randomly for those dates it ran.

I've tested it multiple times, since this happened on 4 agg tables, in 5 diffferent databases (same db schema/same SP definition). On all cases data get randomly updated due to the gap in the agg table updates.

How to repeat:
I've tested following scenarios in last 10 days:
- Ran the SP directly on the slave, it captured data into agg tables and produced 100% identical data to the master.
- Extracted sql statements from master's binlog, (traced them) and ran them against the slave, this produced 100% identical output to the master.
- Enabled binlogging on the slave (log-slave updates) and ran those queries against slave, it produced 100% identical output to the master.
- Since SP's were using temp tables, and prepared statements I've customized stored procedures to copy data from temp table to a debug table to make sure it is populated correctly and it is. Also ran it without prepared statements (on the master) but result on the slave was still incorrect.
- When I checked data incorrectly populated on the slave table via replication, I've noticed that data is not randomly updated, but keys are chosen incorrectly. One simplified example (without going into too much details about db schema):
On master :
product 1 sold 100 on 2008/11/01
product 1 sold 101 on 2008/11/02

product 2 sold 200 on 2008/11/01
product 2 sold 201 on 2008/11/02

product 3 sold 300 on 2008/11/01
product 3 sold 301 on 2008/11/02

On the slave :
product 3 sold 100 on 2008/11/01
product 3 sold 101 on 2008/11/02

product 1 sold 200 on 2008/11/01
product 1 sold 201 on 2008/11/02

product 2 sold 300 on 2008/11/01
product 2 sold 301 on 2008/11/02

- One final test I did was to truncate agg table on the slave, and run sp against it on the master. This produced correct result when the sp/updates are replicated to the slave. So I noticed that if the slave agg table is different then the master table for some reason (except when its blank), updates against it produces somehow random results.

Suggested fix:
I've repopulated aggregation tables on the slave by copying it from master, now the SP & replication works fine.

Thank you for the report.

You said:

----<START QUOTE>----
About 2 weeks ago a change in the java
code caused stored procedures executed against master host with binlog disabled. So data
didn't get replicated to the slave for >5 days. When I noticed it, we just enabled
binlogging on the java code to write updates to the table. When the stored procedure is
executed next night, it ran for last 5 days and data populated on the master properly as
it was expected, but the slave had the data captured/calculated almost randomly for those
dates it ran.
----<END QUOTE>----

Also you said " One final test I did was to truncate agg table on the slave, and run sp against it on the master. This produced correct result when the sp/updates are replicated to the slave."

It looks like after having binlog disabled tables on master have new rows with unique key, but not on slave. In this case this is intended behavior, because slave can not know about not existent rows. Please check.

Also you can open support issue if you need help regarding to this problem.

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