List:Commits« Previous MessageNext Message »
From:guilhem Date:June 16 2006 1:51pm
Subject:bk commit into 5.0 tree (guilhem:1.2174) BUG#20188
View as plain text  
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/16 15:51:48 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/16 15:51:44 guilhem@stripped +8 -8
    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.
    Additionally, in case of REPLACE, we don't need to reset next_insert_id:
    the value of next_insert_id will be suitable for the next row.
    Comment explaining why we can't do the same for ON DUPLICATE KEY UPDATE.

  mysql-test/t/rpl_insert_id.test
    1.17 06/06/16 15:51:44 guilhem@stripped +31 -0
    test for the bugfix.
    The test of ON DUPLICATE KEY UDPDATE is to verify something which I had
    got wrong in the previous changeset for the same bugfix.

  mysql-test/r/rpl_insert_id.result
    1.15 06/06/16 15:51:44 guilhem@stripped +34 -0
    result update, without the bugfix slave's "3 350" 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-16 15:51:44 +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 */
@@ -1030,7 +1029,12 @@
 
         if (thd->clear_next_insert_id)
         {
-          /* Reset auto-increment cacheing if we do an update */
+          /*
+            This update may put in the auto_increment column a value larger
+            than next_insert_id, thus making next_insert_id unusable (that
+            could even provoke a duplicate key error), so we force a call to
+            the engine for next row, to get a new next_insert_id:
+          */
           thd->clear_next_insert_id= 0;
           thd->next_insert_id= 0;
         }
@@ -1067,12 +1071,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 +1133,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)
@@ -1142,6 +1141,7 @@
   table->file->print_error(error,MYF(0));
 
 before_trg_err:
+  table->file->restore_auto_increment();
   if (key)
     my_safe_afree(key, table->s->max_unique_length, MAX_KEY_LENGTH);
   DBUG_RETURN(1);

--- 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-16 15:51:44 +02:00
@@ -132,3 +132,37 @@
 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 order by n;
+n	b
+1	100
+insert into t1 values(null,200),(null,300);
+delete from t1 where b <> 100;
+select * from t1 order by n;
+n	b
+1	100
+replace into t1 values(null,100),(null,350);
+select * from t1 order by n;
+n	b
+2	100
+3	350
+select * from t1 order by n;
+n	b
+2	100
+3	350
+insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
+select * from t1 order by n;
+n	b
+2	100
+4	400
+1000	350
+1001	600
+select * from t1 order by n;
+n	b
+2	100
+4	400
+1000	350
+1001	600
+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-16 15:51:44 +02:00
@@ -147,6 +147,37 @@
 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 order by n;
+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 order by n;
+# only the auto_inc counter differs.
+connection master;
+
+replace into t1 values(null,100),(null,350);
+select * from t1 order by n;
+sync_slave_with_master;
+select * from t1 order by n;
+
+# also check that if we update a row, the counter for next row
+# is bigger than the after-value of the updated row:
+connection master;
+insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
+select * from t1 order by n;
+sync_slave_with_master;
+select * from t1 order by n;
+
+connection master;
+drop table t1;
+
 # End of 5.0 tests
 
 sync_slave_with_master;
Thread
bk commit into 5.0 tree (guilhem:1.2174) BUG#20188guilhem16 Jun