| 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: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.1, 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.