MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Narayanan V Date:September 16 2008 1:08pm
Subject:bzr commit into mysql-5.1 branch (v.narayanan:2739) Bug#38338
View as plain text  
#At file:///export/home/log/Narayanan/mysql_checkouts_bazaar/5.1_main_repository/mysql-5.1-bugteam-38338/

 2739 Narayanan V	2008-09-16
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                        
      Fix the write_record function to record auto increment
      values in a consistent way.
modified:
  mysql-test/r/auto_increment.result
  mysql-test/t/auto_increment.test
  sql/sql_insert.cc

per-file messages:
  mysql-test/r/auto_increment.result
    Updated the test result file with the output of the
    new test case added to verify this bug.
  mysql-test/t/auto_increment.test
    Added a new test case to verify this bug.
  sql/sql_insert.cc
    The algorithm for the write_record function
    in sql_insert.cc is (more emphasis given to
    the parts that deal with the autogenerated values)
    
    1) If a write fails
    
       1.1) save the autogenerated value to avoid 
            thd->insert_id_for_cur_row to become 0.
    
       1.2) <logic to handle INSERT ON DUPLICATE KEY
            UPDATE and REPLACE>
    
    2) record the first successful insert id.
    
    explanation of the failure
    --------------------------
    
    As long as 1.1) was executed 2) worked fine.
    
    1.1) was always executed when REPLACE worked 
         with the last row update optimization, but
         in cases where 1.1) was not executed 2)
         would fail and would result in the autogenerated
         value not being saved.
    
    solution
    --------
    
    repeat a check for thd->insert_id_for_cur_row 
    being zero similar to 1.1) before 2) and ensure
    that the correct value is saved.
=== modified file 'mysql-test/r/auto_increment.result'
--- a/mysql-test/r/auto_increment.result	2008-01-11 01:06:08 +0000
+++ b/mysql-test/r/auto_increment.result	2008-09-16 13:07:59 +0000
@@ -454,3 +454,11 @@ select last_insert_id();
 last_insert_id()
 3
 drop table t1;
+create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;

=== modified file 'mysql-test/t/auto_increment.test'
--- a/mysql-test/t/auto_increment.test	2008-01-11 01:06:08 +0000
+++ b/mysql-test/t/auto_increment.test	2008-09-16 13:07:59 +0000
@@ -314,5 +314,15 @@ insert into t1 values(null,0,0,null);
 # this will delete two rows
 replace into t1 values(null,1,0,null);
 select last_insert_id();
+drop table t1;
 
+# Test of REPLACE when it does a INSERT+DELETE for all the conflicting rows
+# (i.e.) when there are three rows conflicting in unique key columns with
+# a row that is being inserted, all the three rows will be deleted and then
+# the new rows will be inserted.
+create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
 drop table t1;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2008-09-11 11:23:12 +0000
+++ b/sql/sql_insert.cc	2008-09-16 13:07:59 +0000
@@ -1545,6 +1545,17 @@ int write_record(THD *thd, TABLE *table,
         }
       }
     }
+    
+    /*
+        If more than one iteration of the above while loop is done, from the second 
+        one the row being inserted will have an explicit value in the autoinc field, 
+        which was set at the first call of handler::update_auto_increment(). This 
+        value is saved to avoid thd->insert_id_for_cur_row becoming 0. Use this saved
+        autoinc value.
+     */
+    if (table->file->insert_id_for_cur_row == 0)
+      table->file->insert_id_for_cur_row= insert_id_for_cur_row;
+      
     thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row);
     /*
       Restore column maps if they where replaced during an duplicate key

Thread
bzr commit into mysql-5.1 branch (v.narayanan:2739) Bug#38338Narayanan V16 Sep