Fixed bug #27650:

INSERT into InnoDB table may cause "ERROR 1062 (23000): Duplicate entry..."
errors or lost records after multi-row INSERT of the form:
"INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)",
where "id" is an AUTO_INCREMENT column.

It happens because InnoDB handler forgets to save next insert id after
updating of auto_increment column with new values. As result of that
last insert id stored inside InnoDB dictionary tables differs from it's
cached thd->next_insert_id value.


sql/ha_innodb.cc:
  Fixed bug #27650.
  INSERT into InnoDB table may cause "ERROR 1062 (23000): Duplicate entry..."
  errors or lost records after multi-row INSERT of the form:
  "INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)",
  where "id" is an AUTO_INCREMENT column.
  It happens because InnoDB handler forgets to save next insert id after
  updating of auto_increment column with new values. As result of that
  last insert id stored inside InnoDB dictionary tables differs from it's
  cached thd->next_insert_id value.
  ha_innobase::write_row() function has been corrected.
mysql-test/t/innodb_mysql.test:
  Added a test case for bug #27650.
mysql-test/r/innodb_mysql.result:
  Added a test case for bug #27650.

1 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index c351891d205..c2521656a9c 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -412,4 +412,51 @@ DROP TABLE t1;
 
 --source include/innodb_rollback_on_timeout.inc
 
+
+-- source include/have_innodb.inc
+
+#
+# Bug #27650: INSERT fails after multi-row INSERT of the form:
+# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
+#
+
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+truncate table t1;
+
+insert into t1 (id, c) values (NULL, 'a');
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+drop table t1;
+
 --echo End of 5.0 tests