#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
