This fix+comments was originally made by Alexey Kopytov

LP bug #1035225 / MySQL bug #66301: INSERT ... ON DUPLICATE KEY UPDATE +
innodb_autoinc_lock_mode=1 is broken

The problem was that when certain INSERT ... ON DUPLICATE KEY UPDATE
were executed concurrently on a table containing an AUTO_INCREMENT
column as a primary key, InnoDB would correctly reserve non-overlapping
AUTO_INCREMENT intervals for each statement, but when the server
encountered the first duplicate key error on the secondary key in one of
the statements and performed an UPDATE, it also updated the internal
AUTO_INCREMENT value to the one from the existing row that caused a
duplicate key error, even though the AUTO_INCREMENT value was not
specified explicitly in the UPDATE clause. It would then proceed with
using AUTO_INCREMENT values the range reserved previously by another
statement, causing duplicate key errors on the AUTO_INCREMENT column.

Fixed by changing write_record() to ensure that in case of a duplicate
key error the internal AUTO_INCREMENT counter is only updated when the
AUTO_INCREMENT value was explicitly updated by the UPDATE
clause. Otherwise it is restored to what it was before the duplicate key
error, as that value is unused and can be reused for subsequent
successfully inserted rows.

sql/sql_insert.cc:
  Don't update next_insert_id to the value of a row found during ON DUPLICATE KEY UPDATE.
sql/sql_parse.cc:
  Added DBUG_SYNC
sql/table.h:
  Added next_number_field_updated flag to detect changing of auto increment fields.
  Moved fields a bit to get bool fields after each other (better alignment)

2 files changed, 84 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/auto_increment_dup.result b/mysql-test/suite/innodb/r/auto_increment_dup.result
new file mode 100644
index 00000000000..5bf901cb212
--- /dev/null
+++ b/mysql-test/suite/innodb/r/auto_increment_dup.result
@@ -0,0 +1,33 @@
+drop table if exists t1;
+CREATE TABLE t1(
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+k INT,
+c CHAR(1),
+UNIQUE KEY(k)) ENGINE=InnoDB;
+#
+# Connection 1
+#
+SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1';
+affected rows: 0
+INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
+#
+# Connection 2
+#
+SET DEBUG_SYNC='start_ha_write_row WAIT_FOR continue2';
+affected rows: 0
+SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1';
+affected rows: 0
+INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
+affected rows: 3
+info: Records: 3  Duplicates: 0  Warnings: 0
+affected rows: 4
+info: Records: 3  Duplicates: 1  Warnings: 0
+SET DEBUG_SYNC='RESET';
+SELECT * FROM t1 ORDER BY k;
+id	k	c
+1	1	NULL
+4	2	1
+2	3	NULL
+5	4	NULL
+6	5	NULL
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/auto_increment_dup.test b/mysql-test/suite/innodb/t/auto_increment_dup.test
new file mode 100644
index 00000000000..ad439024f65
--- /dev/null
+++ b/mysql-test/suite/innodb/t/auto_increment_dup.test
@@ -0,0 +1,51 @@
+##########################################################################
+# LP bug #1035225 / MySQL bug #66301: INSERT ... ON DUPLICATE KEY UPDATE +
+# innodb_autoinc_lock_mode=1 is broken
+##########################################################################
+
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+CREATE TABLE t1(
+       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+       k INT,
+       c CHAR(1),
+       UNIQUE KEY(k)) ENGINE=InnoDB;
+
+--enable_info
+
+--connect(con1, localhost, root)
+--connect(con2, localhost, root)
+
+--connection con1
+
+--echo #
+--echo # Connection 1
+--echo #
+SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1';
+--send INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'
+
+--connection con2
+--echo #
+--echo # Connection 2
+--echo #
+SET DEBUG_SYNC='start_ha_write_row WAIT_FOR continue2';
+SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1';
+INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
+
+--connection con1
+--reap
+--disable_info
+SET DEBUG_SYNC='RESET';
+SELECT * FROM t1 ORDER BY k;
+
+--disconnect con1
+--disconnect con2
+
+--connection default
+
+DROP TABLE t1;