From 9595c788f9857d0e712f6659d3a0d85300aa0f7b Mon Sep 17 00:00:00 2001
From: unknown <monty@mysql.com>
Date: Mon, 6 Jun 2005 20:41:52 +0300
Subject: 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
---
 mysql-test/t/auto_increment.test | 36 ++++++++++++++++++++++++++++++++
 mysql-test/t/innodb.test         | 45 ++++++++++++++++++++++++++++++++++++++++
 2 files changed, 81 insertions(+)

(limited to 'mysql-test/t')

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;
-- 
cgit v1.2.1