bk commit into 5.0 tree (guilhem:1.2174) BUG#20188

[guilhem]

Below is the list of changes that have just been committed into a local
5.0 repository of guilhem. When guilhem 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.2174 06/06/07 16:24:30 guilhem@stripped +3 -0
  Fix for BUG#20188 "REPLACE in auto_increment column gives different rows on slave":
  if slave's table had a higher auto_increment counter than master's (even though all rows
  of the two tables were identical), then in some cases, REPLACE failed to replicate
  statement-based (it inserted different values on slave from on master).
  Fix is to move a too early restore_auto_increment() down to when we really know we can
  restore the value.

  sql/sql_insert.cc
    1.191 06/06/07 16:24:25 guilhem@stripped +1 -13
    restore_auto_increment() means "I know I won't use this autogenerated autoincrement
value,
    you are free to reuse it for next row". But we were calling restore_auto_increment()
in the
    case of REPLACE: if REPLACE fails inserting the row, we don't know we won't use the
value,
    as we are going to try again by doing internally an UPDATE of the existing row, or a
DELETE
    of the existing row and then an INSERT. So I move restore_auto_increment() further
down,
    when we know for sure we failed all possibilities for the row.
    This too early restore_auto_increment led us, in the case of internal UPDATE, to reset
next_insert_id
    for the next row, precisely because the UPDATE may use the value we wrongly abandoned;
    if UPDATE succeeded then indeed the value we abandoned should not be used for next
row,
    which we ensured by resetting next_insert_id. Now that we don't abandon a value which
we'll use,
    we don't need to reset next_insert_id anymore.

  mysql-test/t/rpl_insert_id.test
    1.17 06/06/07 16:24:25 guilhem@stripped +21 -0
    test for the bugfix

  mysql-test/r/rpl_insert_id.result
    1.15 06/06/07 16:24:25 guilhem@stripped +21 -0
    result update, without the bugfix the last line was "4 350"

# 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:	guilhem
# Host:	gbichot3.local
# Root:	/home/mysql_src/mysql-5.0

--- 1.190/sql/sql_insert.cc	2006-05-26 10:51:16 +02:00
+++ 1.191/sql/sql_insert.cc	2006-06-07 16:24:25 +02:00
@@ -955,7 +955,6 @@
       uint key_nr;
       if (error != HA_WRITE_SKIP)
 	goto err;
-      table->file->restore_auto_increment();
       if ((int) (key_nr = table->file->get_dup_key(error)) < 0)
       {
 	error=HA_WRITE_SKIP;			/* Database can't find key */
@@ -1028,12 +1027,6 @@
         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)
@@ -1067,12 +1060,6 @@
               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;
@@ -1135,6 +1122,7 @@
   DBUG_RETURN(trg_error);
 
 err:
+  table->file->restore_auto_increment();
   info->last_errno= error;
   /* current_select is NULL if this is a delayed insert */
   if (thd->lex->current_select)

--- 1.14/mysql-test/r/rpl_insert_id.result	2006-04-21 16:54:56 +02:00
+++ 1.15/mysql-test/r/rpl_insert_id.result	2006-06-07 16:24:25 +02:00
@@ -132,3 +132,24 @@
 drop function bug15728;
 drop function bug15728_insert;
 drop table t1, t2;
+create table t1 (n int primary key auto_increment not null,
+b int, unique(b));
+insert into t1 values(null,100);
+select * from t1;
+n	b
+1	100
+insert into t1 values(null,200),(null,300);
+delete from t1 where b <> 100;
+select * from t1;
+n	b
+1	100
+replace into t1 values(null,100),(null,350);
+select * from t1;
+n	b
+2	100
+3	350
+select * from t1;
+n	b
+2	100
+3	350
+drop table t1;

--- 1.16/mysql-test/t/rpl_insert_id.test	2006-05-29 12:45:15 +02:00
+++ 1.17/mysql-test/t/rpl_insert_id.test	2006-06-07 16:24:25 +02:00
@@ -147,6 +147,27 @@
 drop function bug15728_insert;
 drop table t1, t2;
 
+# test of BUG#20188 REPLACE in auto_increment gives different rows on slave
+
+create table t1 (n int primary key auto_increment not null,
+b int, unique(b));
+insert into t1 values(null,100);
+select * from t1;
+sync_slave_with_master;
+# make slave's table autoinc counter bigger
+insert into t1 values(null,200),(null,300);
+delete from t1 where b <> 100;
+# check that slave's table content is identical to master
+select * from t1;
+# only the auto_inc counter differs.
+connection master;
+replace into t1 values(null,100),(null,350);
+select * from t1;
+sync_slave_with_master;
+select * from t1;
+connection master;
+drop table t1;
+
 # End of 5.0 tests
 
 sync_slave_with_master;