MySQL Bugs: #24432: INSERT... ON DUPLICATE KEY UPDATE skips auto

Bug #24432	INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values
Submitted:	19 Nov 2006 19:24	Modified:	19 Mar 2007 19:11
Reporter:	Adam Kozubowicz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.27-standard, 5.1 BK	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	auto_increment, ON DUPLICATE KEY UPDATE

Description:
I'm updating one table based on changes made on another. So sometimes there is no changes, sometimes only updates and sometimes inserts. When I was working on  MySQL 4.1.20 everything was OK, but after upgrade to 5.0.27 there is serious problem with auto_increment field. Updating records even if there is no changes, increasing auto_increment field value. This occurs only when updating procedures are made "bulk" - from file. When I simulate updating proces "by hand" - query by query, everything works fine. It's problem because it exhausting auto_increment values... I'm updating about 100.000 records by hour, and every update when there is a new record increasing auto_increment values of 100.000... 

How to repeat:
DROP TABLE IF EXISTS Test1;
DROP TABLE IF EXISTS Test2;

CREATE TABLE Test1 (
  id bigint(20) unsigned NOT NULL auto_increment,
  field_1 int(10) unsigned NOT NULL,
  field_2 varchar(255) NOT NULL,
  field_3 varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY field_1 (field_1, field_2)
) ENGINE=MyISAM;

CREATE TABLE Test2 (
  field_a int(10) unsigned NOT NULL,
  field_b varchar(255) NOT NULL,
  field_c varchar(255) NOT NULL
) ENGINE=MyISAM;

INSERT INTO Test2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
INSERT INTO Test2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
INSERT INTO Test2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
INSERT INTO Test2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
INSERT INTO Test2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');

# Updating table Test1 based on values from table Test2
INSERT INTO Test1 (field_1, field_2, field_3)
SELECT Test2.field_a, Test2.field_b, Test2.field_c
FROM Test2
ON DUPLICATE KEY UPDATE
Test1.field_3 = Test2.field_c;

# Inserting new record into Test2
INSERT INTO Test2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');

# Updating Test1 again
INSERT INTO Test1 (field_1, field_2, field_3)
SELECT Test2.field_a, Test2.field_b, Test2.field_c
FROM Test2
ON DUPLICATE KEY UPDATE
Test1.field_3 = Test2.field_c;

# END OF FILE

And there is incorrect value id:

mysql> SELECT * FROM Test1;
+----+---------+---------+---------+
| id | field_1 | field_2 | field_3 |
+----+---------+---------+---------+
|  1 |       1 | a       | 1a      | 
|  2 |       2 | b       | 2b      | 
|  3 |       3 | c       | 3c      | 
|  4 |       4 | d       | 4d      | 
|  5 |       5 | e       | 5e      | 
| 11 |       6 | f       | 6f      | 
+----+---------+---------+---------+
6 rows in set (0.00 sec)

Id value for last record should be 6. And on MySQL 4.1.20 in fact is 6. All above procedures made on 4.1.20 produce:

mysql> select * from Test1;
+----+---------+---------+---------+
| id | field_1 | field_2 | field_3 |
+----+---------+---------+---------+
|  1 |       1 | a       | 1a      |
|  2 |       2 | b       | 2b      |
|  3 |       3 | c       | 3c      |
|  4 |       4 | d       | 4d      |
|  5 |       5 | e       | 5e      |
|  6 |       6 | f       | 6f      |
+----+---------+---------+---------+
6 rows in set (0.00 sec)

Suggested fix:
Switching back to 4.1.20 :)

Thank you for the report.

Verified as described on Linux using last 5.0 and 5.1 BK sources.

Simplier test case (does not occur with INSERT IGNORE):
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, UNIQUE(b));
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
SELECT a FROM t1;
a
1
3

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18709

ChangeSet@1.2392, 2007-01-24 16:43:51+01:00, guilhem@gbichot3.local +9 -0
  Fix for BUG#24432
  "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values".
  When in an INSERT ON DUPLICATE KEY UPDATE, using
  an autoincrement column, we inserted some autogenerated values and
  also updated some rows, some autogenerated values were not used
  (for example, even if 10 was the largest autoinc value in the table
  at the start of the statement, 12 could be the first autogenerated
  value inserted by the statement, instead of 11). One autogenerated
  value was lost per updated row. Led to exhausting the range of the
  autoincrement column faster.
  Bug introduced by fix of BUG#20188; present since 5.0.24 and 5.1.12.
  This bug breaks replication from a pre-5.0.24 master.
  But the present bugfix, as it makes INSERT ON DUP KEY UPDATE
  behave like pre-5.0.24, breaks replication from a [5.0.24,5.0.34]
  master to a fixed (5.0.36) slave! To warn users against this when
  they upgrade their slave, as agreed with the support team, we add
  code for a fixed slave to detect that it is connected to a buggy
  master in a situation (INSERT ON DUP KEY UPDATE into autoinc column)
  likely to break replication, in which case it cannot replicate so
  stops and prints a message to the slave's error log and to SHOW SLAVE
  STATUS.
  For 5.0.36->[5.0.24,5.0.34] replication we cannot warn as master
  does not know the slave's version (but we always recommended to users
  to have slave at least as new as master).
  As agreed with support, I'll also ask for an alert to be put into
  the MySQL Network Monitoring and Advisory Service.

Guilhem, nice job, particularly that excellent error message!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19517

ChangeSet@1.2392, 2007-02-07 23:30:11+01:00, guilhem@gbichot3.local +10 -0
  Fix for BUG#24432
  "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values".
  When in an INSERT ON DUPLICATE KEY UPDATE, using
  an autoincrement column, we inserted some autogenerated values and
  also updated some rows, some autogenerated values were not used
  (for example, even if 10 was the largest autoinc value in the table
  at the start of the statement, 12 could be the first autogenerated
  value inserted by the statement, instead of 11). One autogenerated
  value was lost per updated row. Led to exhausting the range of the
  autoincrement column faster.
  Bug introduced by fix of BUG#20188; present since 5.0.24 and 5.1.12.
  This bug breaks replication from a pre-5.0.24 master.
  But the present bugfix, as it makes INSERT ON DUP KEY UPDATE
  behave like pre-5.0.24, breaks replication from a [5.0.24,5.0.34]
  master to a fixed (5.0.36) slave! To warn users against this when
  they upgrade their slave, as agreed with the support team, we add
  code for a fixed slave to detect that it is connected to a buggy
  master in a situation (INSERT ON DUP KEY UPDATE into autoinc column)
  likely to break replication, in which case it cannot replicate so
  stops and prints a message to the slave's error log and to SHOW SLAVE
  STATUS.
  For 5.0.36->[5.0.24,5.0.34] replication we cannot warn as master
  does not know the slave's version (but we always recommended to users
  to have slave at least as new as master).
  As agreed with support, I'll also ask for an alert to be put into
  the MySQL Network Monitoring and Advisory Service.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19547

ChangeSet@1.2392, 2007-02-08 11:50:15+01:00, guilhem@gbichot3.local +10 -0
  Fix for BUG#24432
  "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values".
  When in an INSERT ON DUPLICATE KEY UPDATE, using
  an autoincrement column, we inserted some autogenerated values and
  also updated some rows, some autogenerated values were not used
  (for example, even if 10 was the largest autoinc value in the table
  at the start of the statement, 12 could be the first autogenerated
  value inserted by the statement, instead of 11). One autogenerated
  value was lost per updated row. Led to exhausting the range of the
  autoincrement column faster.
  Bug introduced by fix of BUG#20188; present since 5.0.24 and 5.1.12.
  This bug breaks replication from a pre-5.0.24 master.
  But the present bugfix, as it makes INSERT ON DUP KEY UPDATE
  behave like pre-5.0.24, breaks replication from a [5.0.24,5.0.34]
  master to a fixed (5.0.36) slave! To warn users against this when
  they upgrade their slave, as agreed with the support team, we add
  code for a fixed slave to detect that it is connected to a buggy
  master in a situation (INSERT ON DUP KEY UPDATE into autoinc column)
  likely to break replication, in which case it cannot replicate so
  stops and prints a message to the slave's error log and to SHOW SLAVE
  STATUS.
  For 5.0.36->[5.0.24,5.0.34] replication we cannot warn as master
  does not know the slave's version (but we always recommended to users
  to have slave at least as new as master).
  As agreed with support, I'll also ask for an alert to be put into
  the MySQL Network Monitoring and Advisory Service.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19561

ChangeSet@1.2392, 2007-02-08 15:53:14+01:00, guilhem@gbichot3.local +10 -0
  Fix for BUG#24432
  "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values".
  When in an INSERT ON DUPLICATE KEY UPDATE, using
  an autoincrement column, we inserted some autogenerated values and
  also updated some rows, some autogenerated values were not used
  (for example, even if 10 was the largest autoinc value in the table
  at the start of the statement, 12 could be the first autogenerated
  value inserted by the statement, instead of 11). One autogenerated
  value was lost per updated row. Led to exhausting the range of the
  autoincrement column faster.
  Bug introduced by fix of BUG#20188; present since 5.0.24 and 5.1.12.
  This bug breaks replication from a pre-5.0.24 master.
  But the present bugfix, as it makes INSERT ON DUP KEY UPDATE
  behave like pre-5.0.24, breaks replication from a [5.0.24,5.0.34]
  master to a fixed (5.0.36) slave! To warn users against this when
  they upgrade their slave, as agreed with the support team, we add
  code for a fixed slave to detect that it is connected to a buggy
  master in a situation (INSERT ON DUP KEY UPDATE into autoinc column)
  likely to break replication, in which case it cannot replicate so
  stops and prints a message to the slave's error log and to SHOW SLAVE
  STATUS.
  For 5.0.36->[5.0.24,5.0.34] replication we cannot warn as master
  does not know the slave's version (but we always recommended to users
  to have slave at least as new as master).
  As agreed with support, I'll also ask for an alert to be put into
  the MySQL Network Monitoring and Advisory Service.

Mats approved via email, so ticking his box. Pushed into the 5.0-rpl team tree.

queued to 5.0-rpl, 5.1-rpl

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20456

ChangeSet@1.2396, 2007-02-23 15:32:51+01:00, gbichot@dl145h.mysql.com +2 -0
  the fix for BUG#24432
    "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
  didn't make it into 5.0.36 and 5.1.16,
  so we need to adjust the bug-detection-based-on-version-number code.
  Because the rpl tree has a too old version, rpl_insert_id cannot pass,
  so I disable it (like is already the case in 5.1-rpl for the same reason),
  and the repl team will re-enable it when they merge 5.0 and 5.1 into
  their trees (thus getting the right version number).

I wonder whether I fully understand the consequences of different combinations of master and slave. My understanding is:

          master  (-inf, 5.0.23)        [5.0.24, 5.0.34]      [5.0.36, 5.1)
slave
(-inf, 5.0.23)    BUG#20188             both bugs, no warning BUG#20188
[5.0.24, 5.0.34]  this bug, no warning  no bug                this bug, no warning
[5.0.36, 5.1)     no bug                this bug, warning     no bug

Anything wrong?

Hello Alan Tam,

I'm limiting to 5.0 versions (to get the chart for 5.1, replace 5.0.23 by 5.1.11, 5.0.24 by 5.1.12, 5.0.34 by 5.1.17).
In your chart, "this bug" is a bit fuzzy. It must be "different execution of INSERT ON DUPLICATE KEY UPDATE on master and slave with regard to how autoincrement values are skipped". BUG#24432 is a case of this. The fix of BUG#24432 is the other case of this.
That is the correct chart:

          master  (-inf, 5.0.23)        [5.0.24, 5.0.34]      [5.0.36,+inf)
slave
(-inf, 5.0.23)    BUG#20188             both bugs, no error   BUG#20188
[5.0.24, 5.0.34]  this bug, no error    no bug                this bug, no error
[5.0.36, +inf)    no bug                this bug, error       no bug

Last: the fix for BUG#24432 missed 5.0.36, it will only be in 5.0.38 and newer, and 5.1.17 and newer.

Guilhem Bichot,

Thanks for your clarification. Indeed I am only interested in the "no bug" entry on the bottom left corner. It enables me to first upgrade slave to a patched version to give a working master-slave relationship, then promote the slave as master, and finally upgrade the old master and make it a slave. This is very important since I don't dare upgrading a production server without first testing the new version using a backup of production data, but we cannot make a backup copy of the production data if no slave is sync, and presence of these 2 bugs breaks replication.

pushed to 5.0.38,5.1.17-beta

Noted in 5.0.38, 5.1.17 changelogs.

For INSERT ... ON DUPLICATE KEY UPDATE statements where some
AUTO_INCREMENT values were generated automatically for inserts and
some rows were updated, one auto-generated value was lost per updated
row, leading to faster exhaustion of the range of the AUTO_INCREMENT
column.

Because the original problem can affect replication (different values
on master and slave), it is recommended that the master and its
slaves be upgraded to the current version.

Guilhem - thanks a lot for this table. I'm shooting for the middle 'no bug' case (master 5.0.24) and going to downgrade our new slave to 5.0.34 before we migrate to this new machine. I also didn't fancy the prod upgrade option!

Thought I might add - although it says Server: MyISAM this bug affects us using InnoDB too.

I think this should be listed as an incompatible change on the release notes, because it breaks replication with this error:

070425 11:55:32 [ERROR] Slave: According to the master's version ('5.0.26-log'),
it is probable that master suffers from this bug: http://bugs.mysql.com/bug.php
?id=24432 and thus replicating the current binary log event may make the slave's
data become different from the master's data. To take no risk, slave refuses to
replicate this event and stops. We recommend that all updates be stopped on the
master and slave, that the data of both be manually synchronized, that master's
binary logs be deleted, that master be upgraded to a version at least equal to
'5.0.38'. Then replication can be restarted. Error_code: 1105
070425 11:55:32 [ERROR] Slave: Error 'master may suffer from http://bugs.mysql.c
om/bug.php?id=24432 so slave stops; check error log on slave for more info' on q
uery. Default database: 'rkmain'. Query:....

The current release notes (http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es-5-0-38.html) just says

"For INSERT ... ON DUPLICATE KEY UPDATE statements where some AUTO_INCREMENT values were generated automatically for inserts and some rows were updated, one auto-generated value was lost per updated row, leading to faster exhaustion of the range of the AUTO_INCREMENT column. (Bug#24432)"

That sounds pretty minor, but it's not! I think it's important to know that you can't upgrade your slave without being forced to upgrade your master. As it is, if you upgrade a slave, it will simply stop replication.

This bug still seems to be affecting InnoDB from what I can see, though the earlier simple test case works, the following does not:

create table t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, c INT, UNIQUE(b)) ENGINE=InnoDB;
insert into t1(b, c) values (1, 5) ON DUPLICATE KEY UPDATE t1.c = values(c);
insert into t1(b, c) values (1, 10) ON DUPLICATE KEY UPDATE t1.c = values(c);
insert into t1(b, c) values (1, 15) ON DUPLICATE KEY UPDATE t1.c = values(c);
insert into t1(b, c) values (1, 20) ON DUPLICATE KEY UPDATE t1.c = values(c);
insert into t1(b, c) values (2, 5) ON DUPLICATE KEY UPDATE t1.c = values(c);
select * from t1;

Yields :
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |   20 |
| 5 |    2 |    5 |
+---+------+------+

Each insert incremented the auto_inc value, but only the first and last lines inserted a row.

I tested this on Windows using 5.0.45 and on Gentoo Linux using 5.0.44.

Could you please open a new bug report containing your testcase? And also test if this is InnoDB-specific (does it happen with MyISAM?) ? Thanks!

Hi, I also encountered the issue mentioned by William (sorry I'm new here and have tried 40 mins but still can't find if there is a new bug being reported). I found this issue indeed only happens on tables using InnoDB. I'm using v5.1.21 on RHEL 5.

Here is my test script:

----- test for InnoDB -----

create table test (id int unsigned not null AUTO_INCREMENT, data int not null, count int not null default 0,  primary key (id), unique key uk_data (data) ) ENGINE=InnoDB;

mysql> insert into test (data, count) values (1,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (data, count) values (1,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test (data, count) values (3,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+-------+
| id | data | count |
+----+------+-------+
|  1 |    1 |     2 | 
|  3 |    2 |     4 | 
|  7 |    3 |     1 | 
+----+------+-------+
3 rows in set (0.00 sec)

----- test2 for MyISAM -----

create table test2 (id int unsigned not null AUTO_INCREMENT, data int not null, count int not null default 0,  primary key (id), unique key uk_data (data) ) ENGINE=MyISAM;

mysql> insert into test2 (data, count) values (1,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 (data, count) values (1,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test2 (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test2 (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test2 (data, count) values (2,1) on duplicate key update count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test2 (data, count) values (3,1) on duplicate key update count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+------+-------+
| id | data | count |
+----+------+-------+
|  1 |    1 |     2 | 
|  2 |    2 |     4 | 
|  3 |    3 |     1 | 
+----+------+-------+
3 rows in set (0.00 sec)

Problem with InnoDB is bug #28781