Bug #66301 INSERT ... ON DUPLICATE KEY UPDATE + innodb_autoinc_lock_mode=1 is broken
Submitted: 10 Aug 2012 8:46 Modified: 22 Apr 2013 20:28
Reporter: Alexey Kopytov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[10 Aug 2012 8:46] Alexey Kopytov
Description:
Concurrent INSERT ... ON DUPLICATE KEY UPDATE statements on a table with
an AUTO_INCREMENT column may result in spurious duplicate key errors
(and, as a result, lost data due to some rows being updated rather than
inserted) with the default value of innodb_autoinc_lock_mode=1.

The problem only appears with concurrently executing INSERT ... ON
DUPLICATE KEY UPDATE statements and is time-sensitive. Consider the
following table:

CREATE TABLE t(
       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       k INT,
       c CHAR(1),
       UNIQUE KEY(k)) ENGINE=InnoDB;

and the following two statements:

INSERT INTO t(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; /* (1) */
INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; /* (2) */

When statements (1) and (2) are executed sequentially, table 't'
contains the following rows as a result:

id	k	c
1	1	NULL
2	2	2
3	3	NULL
4	4	NULL
5	5	NULL

(the non-NULL value of the 'c' column depends on the order of execution)

However, if both statements are executed concurrently, and statement (2)
starts and finishes while statement (1) execution is in progress, the
result of both statements may become as follows:

id	k	c
1	1	NULL
4	2	1
5	4	1
6	5	NULL

The attached .test file uses DEBUG_SYNC points to demonstrate that.

The workaround is to use innodb_autoinc_lock_mode=0, i.e. serialize such
statements with the InnoDB AUTO-INC lock. For example, try running the test
case with --mysqld=--innodb_autoinc_lock_mode=0

Naturally, this bug also breaks statement-based replication and results
in inconsistent slaves.

The root cause is that when the statements are executed concurrently in
such a way, InnoDB will correctly reserve non-overlapping AUTO_INCREMENT
intervals for each statement, but when the server encounters the first
duplicate key error on the secondary key in statement (1) and performs
an UPDATE, it also updates the internal AUTO_INCREMENT value to the one
corresponding to the row inserted by statement (2), even though it is
not specified explicitly in the UPDATE part. It will then proceed with
using AUTO_INCREMENT values for statement (1) from the range reserved
previously by statement (2), causing duplicate key errors on the
AUTO_INCREMENT column.

How to repeat:
Use the attached MTR testcase.
[10 Aug 2012 8:47] Alexey Kopytov
mtr testcase

Attachment: bug66301.test (application/octet-stream, text), 1.24 KiB.

[10 Aug 2012 15:00] Valeriy Kravchuk
Thank you for the bug report. Verified with 5.5.28 on Mac OS X:

macbook-pro:mysql-test openxs$ ./mtr bug66301
Logging: ./mtr  bug66301
120810 17:58:15 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/jQXdra8V3y/ is case insensitive
120810 17:58:15 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.28
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 63050

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug66301                            [ fail ]
        Test ended at 2012-08-10 17:58:30

CURRENT_TEST: main.bug66301
--- /Users/openxs/dbs/5.5/mysql-test/r/bug66301.result	2012-08-10 17:58:06.000000000 +0300
+++ /Users/openxs/dbs/5.5/mysql-test/r/bug66301.reject	2012-08-10 17:58:30.000000000 +0300
@@ -0,0 +1,63 @@
+CREATE TABLE t(
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+k INT,
+c CHAR(1),
+UNIQUE KEY(k)) ENGINE=InnoDB;
+#
+# Sequential execution
+#
+INSERT INTO t(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
+affected rows: 3
+info: Records: 3  Duplicates: 0  Warnings: 0
+#
+# 1 duplicate
+#
+INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
+affected rows: 4
+info: Records: 3  Duplicates: 1  Warnings: 0
+#
+# 5 rows, consecutive auto_inc values
+#
+SELECT * FROM t;
+id	k	c
+1	1	NULL
+2	2	2
+3	3	NULL
+4	4	NULL
+5	5	NULL
+affected rows: 5
+TRUNCATE t;
+affected rows: 0
+#
+# Parallel execution
+#
+#
+# Connection 1
+#
+SET DEBUG_SYNC=IF(@@innodb_autoinc_lock_mode > 0, 'ha_write_row_end WAIT_FOR continue', 'RESET');
+affected rows: 0
+INSERT INTO t(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
+#
+# Connection 2
+#
+SET DEBUG_SYNC=IF(@@innodb_autoinc_lock_mode > 0, 'execute_command_after_close_tables SIGNAL continue', 'RESET');
+affected rows: 0
+INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
+affected rows: 3
+info: Records: 3  Duplicates: 0  Warnings: 0
+#
+# 2 duplicates
+#
+affected rows: 5
+info: Records: 3  Duplicates: 2  Warnings: 0
+#
+# 4 rows, gap in auto_inc values
+#
+SELECT * FROM t;
+id	k	c
+1	1	NULL
+4	2	1
+5	4	1
+6	5	NULL
+affected rows: 4
+DROP TABLE t;
...
[15 Apr 2013 13:33] Laurynas Biveinis
5.6.10 seems to affected too.
[22 Apr 2013 20:28] Bugs System
Added changelog entry to 5.6.12, 5.7.2:

"A multi-row "INSERT ... ON DUPLICATE KEY UPDATE" insert failure, caused by
a duplicate key error, would result in duplicate auto-increment values."
[6 Jun 2013 15:44] Laurynas Biveinis
The commit message is missing the public bug db number, making it harder to find.

5.6$ bzr log -r 5014
------------------------------------------------------------
revno: 5014
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.6
timestamp: Tue 2013-04-16 14:22:38 +0530
message:
  Bug #14483484 INSERT ... ON DUPLICATE KEY UPDATE + INNODB_AUTOINC_LOCK_MODE=1
  IS BROKEN
  
  Problem:
  
  When a multi-row INSERT ... ON DUPLICATE KEY UPDATE statement begins
  execution it reserves the required number of auto increment values.
  When one of the insert fails because of duplicate key error, then it
  will become an update operation.  The auto increment value of this
  update operation will determine the auto increment values
  used for subsequent inserts.  This can result in duplicate auto increment
  values.
  
  Solution:
  
  When an insert operation of a multi-row INSERT ... ON DUPLICATE KEY 
  UPDATE statement fails, check if the update clause explicitly updates
  the auto increment value. If an explicit value is given for an 
  auto increment field, then check if it will conflict with the values
  that we have reserved.  If it might conflict then return an error.  
  Also, the update clause will not bump the auto increment value for
  the rows inserted in the multi-row INSERT ... ON DUPLICATE KEY.
  
  rb#2219 approved by Guilhem and Dmitry Shulga.