List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:August 23 2009 2:39pm
Subject:bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677
View as plain text  
#At file:///home/daogangq/mysql/bzrwork/bug45677/mysql-5.1-bugteam/ based on revid:joro@stripped

 3072 Dao-Gang.Qu@stripped	2009-08-23
      Bug #45677  	Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      Concurrent transactions that cause a trigger to insert 2 or more rows 
      into a table with an auto_increment column will generate wrong auto_increment 
      values in statement-based replication, because we just write one specific 
      'SET INSERT_ID=n' sentence to binlog for the first insert sentence, 
      the second insert sentence will generate its auto_increment value 
      base on the current auto_increment value of the table instead of the value  
      of the last INSERT_ID. So the duplicate entry error will be caused for the 
      key PRIMARY. In this case, the duplicate entry error can't be avoided in 
      statement-based replication base on current architecture.
      
      In mixed mode, the problem has been resolved by seting the insert sentence to
      unsafe sentence when using trigger.
     @ mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result
        Added test result for bug#45677.
     @ mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test
        Added test to verify if Concurrent transactions that insert rows into a table 
        with an auto_increment column can cause mixed based replication to break with 
        slave reporting error 1062 (Error 'Duplicate entry 'x' for key 'PRIMARY'').

    added:
      mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result
      mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test
    modified:
      sql/sql_insert.cc
=== added file 'mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result'
--- a/mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result	2009-08-23 14:38:51 +0000
@@ -0,0 +1,34 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+use test;
+create table t1(f1 int) engine=innodb;
+create table t2(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr1 after insert on t1 for each row insert into t2(f1) values(new.f1);
+set autocommit=0;
+insert into t1(f1) values(1),(2);
+insert into t2(f1) values(3),(4);
+commit;
+insert into t1(f1) values(5);
+commit;
+#on master
+select * from t2;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+#on slave
+select * from t2;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+DROP TABLE t1;
+DROP TABLE t2;

=== added file 'mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test'
--- a/mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test	2009-08-23 14:38:51 +0000
@@ -0,0 +1,42 @@
+#
+# Bug45677
+# This test verifies if Concurrent transactions that insert rows into a table 
+# with an auto_increment column can cause mixed based replication to break with 
+# slave reporting error 1062 (Error 'Duplicate entry 'x' for key 'PRIMARY'').
+#
+
+source include/have_binlog_format_mixed.inc;
+source include/have_innodb.inc;
+source include/master-slave.inc;
+
+connection master;
+use test;
+create table t1(f1 int) engine=innodb;
+create table t2(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr1 after insert on t1 for each row insert into t2(f1) values(new.f1);
+
+set autocommit=0;
+insert into t1(f1) values(1),(2);
+
+connection master1;
+insert into t2(f1) values(3),(4);
+
+connection master;
+commit;
+insert into t1(f1) values(5);
+commit;
+
+connection master;
+--echo #on master
+select * from t2;
+
+sync_slave_with_master;
+connection slave;
+--echo #on slave
+select * from t2;
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
+sync_slave_with_master;
+

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2009-08-13 02:48:57 +0000
+++ b/sql/sql_insert.cc	2009-08-23 14:38:51 +0000
@@ -732,6 +732,19 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 
   prepare_triggers_for_insert_stmt(table);
 
+  /*
+    BUG#45677 
+    Concurrent transactions that cause a trigger to insert 2 or more rows into 
+    a table with an auto_increment column will generate wrong auto_increment 
+    values in statement-based replication, so in mixed mode we go to row-based
+    when using trigger.
+  */  
+  if ((thd->lex->trg_chistics.event == TRG_EVENT_INSERT) &&
+      (thd->lex->trg_chistics.action_time == TRG_ACTION_AFTER))
+  {
+    thd->lex->set_stmt_unsafe();
+    thd->set_current_stmt_binlog_row_based_if_mixed();
+  }
 
   if (table_list->prepare_where(thd, 0, TRUE) ||
       table_list->prepare_check_option(thd))


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090823143851-v6a3zhnwabloz2ll.bundle
Thread
bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677Dao-Gang.Qu23 Aug
  • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677He Zhenxing24 Aug
    • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677He Zhenxing24 Aug
      • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677Daogang Qu26 Aug