Ensure that we reset auto-increment cache if we have to do an UPDATE becasue of REPLACE

This fixes bug #11080: Multi-row REPLACE fails on a duplicate key error


mysql-test/r/auto_increment.result:
  New tests for auto-increment and replace
mysql-test/r/innodb.result:
  New tests for auto-increment and replace
mysql-test/t/auto_increment.test:
  New tests for auto-increment and replace
mysql-test/t/innodb.test:
  New tests for auto-increment and replace
mysys/my_alloc.c:
  More comments

6 files changed, 172 insertions, 0 deletions

diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 4587c675150..61a42b004a9 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -355,3 +355,42 @@ CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
+insert into t1 (b) values (1);
+replace into t1 (b) values (2), (1), (3);
+select * from t1;
+a	b
+3	1
+2	2
+4	3
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+replace into t1 (b) values (3);
+select * from t1;
+a	b
+3	1
+2	2
+4	3
+drop table t1;
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val));
+replace into t1 (val) values ('1'),('2');
+replace into t1 (val) values ('1'),('2');
+insert into t1 (val) values ('1'),('2');
+ERROR 23000: Duplicate entry '1' for key 2
+select * from t1;
+rowid	val
+3	1
+4	2
+drop table t1;
+create table t1 (a int not null auto_increment primary key, val int);
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+insert into t1 (val) values (1);
+select * from t1;
+a	val
+2	1
+3	1
+drop table t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 91b01f8e8e6..53dbf2a39e1 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -2410,3 +2410,42 @@ select min(b) from t1 where a='8';
 min(b)
 6
 drop table t1;
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2), (1), (3);
+ERROR 23000: Duplicate entry '3' for key 1
+select * from t1;
+a	b
+1	1
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+replace into t1 (b) values (3);
+ERROR 23000: Duplicate entry '3' for key 1
+select * from t1;
+a	b
+3	1
+2	2
+drop table t1;
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val)) engine=innodb;
+replace into t1 (val) values ('1'),('2');
+replace into t1 (val) values ('1'),('2');
+ERROR 23000: Duplicate entry '3' for key 1
+insert into t1 (val) values ('1'),('2');
+ERROR 23000: Duplicate entry '1' for key 2
+select * from t1;
+rowid	val
+1	1
+2	2
+drop table t1;
+create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+insert into t1 (val) values (1);
+ERROR 23000: Duplicate entry '2' for key 1
+select * from t1;
+a	val
+2	1
+drop table t1;
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index ef344df5fb6..afc4c722051 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -218,3 +218,39 @@ CHECK TABLE t1;
 INSERT INTO t1 (b) VALUES ('bbbb');
 CHECK TABLE t1;
 DROP TABLE IF EXISTS t1;
+
+#
+# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
+# 
+
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
+insert into t1 (b) values (1);
+replace into t1 (b) values (2), (1), (3);
+select * from t1;
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+replace into t1 (b) values (3);
+select * from t1;
+drop table t1;
+
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val));
+replace into t1 (val) values ('1'),('2');
+replace into t1 (val) values ('1'),('2');
+--error 1062
+insert into t1 (val) values ('1'),('2');
+select * from t1;
+drop table t1;
+
+#
+# Test that update changes internal auto-increment value
+#
+
+create table t1 (a int not null auto_increment primary key, val int);
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+insert into t1 (val) values (1);
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 6c685796b2c..e3edcdfa5fc 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1329,3 +1329,48 @@ insert into t1 values ('8', '6'), ('4', '7');
 select min(a) from t1;
 select min(b) from t1 where a='8';
 drop table t1;
+
+#
+# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
+#
+
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
+insert into t1 (b) values (1);
+# We shouldn't get the following error
+--error 1062
+replace into t1 (b) values (2), (1), (3);
+select * from t1;
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+# We shouldn't get the following error
+--error 1062
+replace into t1 (b) values (3);
+select * from t1;
+drop table t1;
+
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val)) engine=innodb;
+replace into t1 (val) values ('1'),('2');
+# We shouldn't get the following error
+--error 1062
+replace into t1 (val) values ('1'),('2');
+--error 1062
+insert into t1 (val) values ('1'),('2');
+select * from t1;
+drop table t1;
+
+
+#
+# Test that update changes internal auto-increment value
+#
+
+create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+# We shouldn't get the following error
+--error 1062
+insert into t1 (val) values (1);
+select * from t1;
+drop table t1;
diff --git a/mysys/my_alloc.c b/mysys/my_alloc.c
index 072fc09cd12..4aa31829c59 100644
--- a/mysys/my_alloc.c
+++ b/mysys/my_alloc.c
@@ -262,6 +262,7 @@ static inline void mark_blocks_free(MEM_ROOT* root)
   NOTES
     One can call this function either with root block initialised with
     init_alloc_root() or with a bzero()-ed block.
+    It's also safe to call this multiple times with the same mem_root.
 */
 
 void free_root(MEM_ROOT *root, myf MyFlags)
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index dbecfe1489e..6fb871cbc9d 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -916,6 +916,12 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
         if (res == VIEW_CHECK_ERROR)
           goto before_trg_err;
 
+        if (thd->clear_next_insert_id)
+        {
+          /* Reset auto-increment cacheing if we do an update */
+          thd->clear_next_insert_id= 0;
+          thd->next_insert_id= 0;
+        }
         if ((error=table->file->update_row(table->record[1],table->record[0])))
 	{
 	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
@@ -949,6 +955,12 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
               table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                 TRG_ACTION_BEFORE, TRUE))
             goto before_trg_err;
+          if (thd->clear_next_insert_id)
+          {
+            /* Reset auto-increment cacheing if we do an update */
+            thd->clear_next_insert_id= 0;
+            thd->next_insert_id= 0;
+          }
           if ((error=table->file->update_row(table->record[1],
 					     table->record[0])))
             goto err;