MySQL Bugs: #33025: Replication breaks on "on duplicate key update": ids on master & slave differ

Bug #33025	Replication breaks on "on duplicate key update": ids on master & slave differ
Submitted:	5 Dec 2007 21:54	Modified:	7 Dec 2007 18:08
Reporter:	Arno Schäfer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	innodb, ON DUPLICATE KEY UPDATE, replication

Description:
I am trying to replicate between two servers, both running MySQL 5.0.45. Replication stops after a few queries with the error message

"Cannot add or update a child row: a foreign key constraint fails"

The queries in question are such (simplified):

INSERT INTO foo (a, b, c) VALUES (1, 2, 3), (4, 5, 6), ... ON DUPLICATE KEY UPDATE a=values(a), b=values(b), c=values(c);
INSERT INTO bar (foo_id, d, e) VALUES (<insert_id1>, 7, 8), (<insert_id2>, 7, 8)...

where the table definitions are:

foo (
foo_id bigint unsigned not null primary key auto_increment,
a int,
b int,
c int,
unique key a
) engine=innodb

and

bar (
d int,
e int,
foo_id bigint unsigned not null,
FOREIGN KEY (foo_id) REFERENCES foo (foo_id) ON DELETE CASCADE ON UPDATE CASCADE) engine=innodb

(hope I got this about right, I can supply the original queries if that is of interest).

Replication breaks on the second query, but the error occurs on the first, as these are the ids of the inserted/updated rows on the master vs. the slave:

SET INSERT_ID=10464954/*!*/;
	
Master	Slave
1024199	1024199
3564090	3564090
3564091	3564091
3564092	3564092
3564093	3564093
2940871	2940871
10464960	10464954
10464961	10464955
3564094	3564094
2903848	2903848
3564095	3564095
3316273	3316273
2761562	2761562
3564096	3564096
3564097	3564097
10464962	10464956
10464963	10464957
3564099	3564099
3564100	3564100
3564102	3564102
10464964	10464958
10464965	10464959
10464966	10464960

The IDs over 10000000 are inserts, obviously, while the others are updates.

It appears that the slave (probably correctly) starts inserting the new entries starting with the INSERT_ID 10464954, while the master also counts the updates BEFORE the first insert and thus starts at 10464960. Interestingly, after the first two inserts, this behavior stops, as the next insert ID on the master after a block of updates is 10464962.

This behavior breaks the replication on the next query, as the table "foo" then differs on master and slave and the foreign key constraint no longer works.

How to repeat:
see description

Thank you for reporting a bug.
This behaviour can happens, when the Slave lost the connection to the Master.
For example: when you make a kill -9 on the mysqld of the slave.

Was your connection aborted during the replication?

No, the connection to the slave was not lost. What I did was load the DB dump (made using mysqldump --opt --master-data) and start the slave. After a few seconds, the error occurs as described.

I do not think this is a replication bug per se, but a bug in the creation of insert IDs in the "insert...on duplicate key update" statement on the master (or any standalone mysqld, for that matter). The IDs of the actually inserted rows should follow consecutively from the last insert ID, but they do not, a gap is left (in the size of the number of updates before the first insert). In my example, there were six updates before the first actual insert, and the insert id of the first inserted row on the master is 10464960, it should be 10464954.

The bug only manifests itself in the replication, as the slave for some reason handles the insert IDs correctly, unlike the master, and so replication breaks.

During an "insert ... on duplicate key update" the auto_increment set the value to the next step. It doesn't matter if it's an update at the end or not. The keyword for that behaviour is the "insert".

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

It is a bug, because it break replication. Either the behavior on the master is wrong, or on the client. Either way, it is a bug.

You miss understood the behaviour of auto_increment.

You never can be sure, which value auto_increment will insert into the column, when you don't tell the insert the values.

When you make an "insert ... on duplicate key update" auto_increment adds 1 to every row, that just was updated. Means, when you have 50 inserts (from 1-50) and you then 4 updates happens, the next value, auto_increment will insert is 55.

Because the slave table has an auto_increment field too and you don't tell the slave what's the highest number on the master, it counts on his own.

Consider, the master will only transfer those statements to the slave, which made a change on the master. When you update the column with the same values that are already in the columns of the row, the statement won't be transfered.

Thats an expected behaviour.

Newer versions of MySQL don't have this problem with auto_increment.
This is a duplicate of bug #28781

So basically you are saying that "on duplicate key update" is not replication safe? Sorry, but this is NOT in the official documentation. You wrote:

> When you make an "insert ... on duplicate key update" auto_increment adds
> 1 to every row, that just was updated. Means, when you have 50 inserts
> (from 1-50) and you then 4 updates happens, the next value, auto_increment
> will insert is 55.

That is NOT what happens. Please take a look at my original report and the insert IDs generated. The updates don't generally increase the autoincrement value. For example, on the master, after insert ID 10464961 there are seven updates, but the next insert ID is still 10464962. The ONLY case where the insert id is increased with an update is the first six updates on the master.

> Because the slave table has an auto_increment field too and you don't
> tell the slave what's the highest number on the master, it counts on
> his own.

That is not true. The binlog specifially contains:

SET INSERT_ID=10464954/*!*/;

So the slave most certainly knows the highest autoincrement ID on the master.

> Consider, the master will only transfer those statements to the
> slave, which made a change on the master. When you update the column
> with the same values that are already in the columns of the row,
> the statement won't be transfered.

Yes, but what difference does that make? The INSERT statement I was talking about most certainly DOES make changes, so it is in the binlog. Everything would work without problems if the updates did not increase the autoincrement value, and I am most certain that this is the expected behavior.

> Thats an expected behaviour.

I cannot believe that replication is supposed to break on "on duplicate key update". Is that the official position of MySQL?

Sorry, but you mixed the tasks here.
What do you want to know? Why the replication breaks or why the next inserted ID on the master sometimes is hire?
I didn't understand your problem in the right way.
Please, look to bug #28781. When I understand you in the right way, this is a duplicate of the bug and this is fixed in newer MySQL versions.
For the understanding of auto_increment please read also bug #19243.
When you agree with me about the duplicate, your problem shouldn't occur in MySQL 5.0.51.

Many thanks for choosing MySQL.