bk commit into 5.0 tree (monty:1.1923) BUG#11080

[monty]

Below is the list of changes that have just been committed into a local
5.0 repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1923 05/06/06 20:41:52 monty@stripped +6 -0
  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

  sql/sql_insert.cc
    1.158 05/06/06 20:41:49 monty@stripped +12 -0
    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

  mysys/my_alloc.c
    1.28 05/06/06 20:41:49 monty@stripped +1 -0
    More comments

  mysql-test/t/innodb.test
    1.90 05/06/06 20:41:49 monty@stripped +45 -0
    New tests for auto-increment and replace

  mysql-test/t/auto_increment.test
    1.23 05/06/06 20:41:49 monty@stripped +36 -0
    New tests for auto-increment and replace

  mysql-test/r/innodb.result
    1.113 05/06/06 20:41:49 monty@stripped +39 -0
    New tests for auto-increment and replace

  mysql-test/r/auto_increment.result
    1.34 05/06/06 20:41:49 monty@stripped +39 -0
    New tests for auto-increment and replace

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	monty
# Host:	narttu.mysql.com
# Root:	/home/my/mysql-5.0

--- 1.27/mysys/my_alloc.c	2005-06-05 17:01:07 +03:00
+++ 1.28/mysys/my_alloc.c	2005-06-06 20:41:49 +03:00
@@ -262,6 +262,7 @@
   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)

--- 1.157/sql/sql_insert.cc	2005-06-05 17:08:45 +03:00
+++ 1.158/sql/sql_insert.cc	2005-06-06 20:41:49 +03:00
@@ -916,6 +916,12 @@
         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 @@
               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;

--- 1.33/mysql-test/r/auto_increment.result	2005-05-14 18:19:30 +03:00
+++ 1.34/mysql-test/r/auto_increment.result	2005-06-06 20:41:49 +03:00
@@ -355,3 +355,42 @@
 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;

--- 1.112/mysql-test/r/innodb.result	2005-06-06 14:03:28 +03:00
+++ 1.113/mysql-test/r/innodb.result	2005-06-06 20:41:49 +03:00
@@ -2410,3 +2410,42 @@
 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;

--- 1.22/mysql-test/t/auto_increment.test	2005-05-13 23:34:05 +03:00
+++ 1.23/mysql-test/t/auto_increment.test	2005-06-06 20:41:49 +03:00
@@ -218,3 +218,39 @@
 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;

--- 1.89/mysql-test/t/innodb.test	2005-06-06 14:03:28 +03:00
+++ 1.90/mysql-test/t/innodb.test	2005-06-06 20:41:49 +03:00
@@ -1329,3 +1329,48 @@
 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;