List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:September 3 2009 3:00pm
Subject:bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677
View as plain text  
#At file:///home/daogangqu/mysql/bzrwork/bug45677/mysql-5.1-bugteam/ based on revid:dao-gang.qu@stripped

 3093 Dao-Gang.Qu@stripped	2009-09-03
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      One query that causes a trigger or calls a function to insert or 
      update > 1 autoinc fields is safe (but we do not distinguish this 
      because it is no possible), and the autoinc value is correct, the 
      problem is that there is only one autoinc value associated with 
      the query. So other autoinc values are generated base on the write 
      table instead of the last autoinc value. So These autoinc values 
      become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that cause 
      a trigger or call a function to insert or update autoinc fields 
      as unsafe, and will switch to row-format in Mixed mode.
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        suite/rpl/t/rpl_auto_increment_update_failure.test
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that causes a trigger or 
        calls a function to insert or update > 1 autoinc fields 
        will make the autoinc values are become inconsistent on 
        master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

    added:
      mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
      mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
    modified:
      sql/sql_base.cc
=== added file 'mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result'
--- a/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result	2009-09-03 14:59:51 +0000
@@ -0,0 +1,534 @@
+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;
+drop table if exists t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26;
+DROP FUNCTION IF EXISTS f1_two_inserts;
+DROP FUNCTION IF EXISTS f1_two_updates;
+DROP FUNCTION IF EXISTS f1_two_inserts_trigger;
+DROP FUNCTION IF EXISTS f1_insert_triggered;
+use test;
+set autocommit=0;
+#Test case1: Test if the the autoinc values are consistent on master and slave,
+#            when INSERT with AFTER TRIGGER
+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);
+insert into t1(f1) values(1),(2);
+insert into t1(f1) values(3);
+insert into t2(f1) values(4),(5);
+commit;
+insert into t1(f1) values(6);
+commit;
+#Test result for INSERT with AFTER TRIGGER on master 
+select * from t2;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+#Test result for INSERT with AFTER TRIGGER on slave
+select * from t2;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+# Test case2: Test if the the autoinc values are consistent on master and slave, 
+#             when UPDATE with AFTER TRIGGER
+create table t3(f1 int, f2 int) engine=innodb;
+create table t4(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr2 after update on t3 for each row insert into t4(f1) values(new.f1);
+insert into t3(f1,f2) values(1,1);
+insert into t3(f1,f2) values(2,1);
+update t3 set f1 = f1 + 5 where f2 = 1;
+insert into t4(f1) values(3);
+commit;
+insert into t3(f1,f2) values(4,2);
+update t3 set f1 = f1 + 5 where f2 = 2;
+commit;
+#Test result for UPDATE with AFTER TRIGGER on master
+select * from t4;
+i1	f1
+1	6
+2	7
+3	3
+4	9
+#Test result for UPDATE with AFTER TRIGGER on slave
+select * from t4;
+i1	f1
+1	6
+2	7
+3	3
+4	9
+# Test case3: Test if the the autoinc values are consistent on master and slave, 
+#             when DELETE with AFTER TRIGGER
+create table t5(f1 int, f2 int) engine=innodb;
+create table t6(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr3 after delete on t5 for each row insert into t6(f1) values(6);
+insert into t5(f1,f2) values(1,1);
+insert into t5(f1,f2) values(2,1);
+insert into t5(f1,f2) values(3,1);
+delete from t5 where f2 = 1;
+insert into t6(f1) values(3);
+commit;
+insert into t5(f1,f2) values(4,2);
+delete from t5 where f2 = 2;
+commit;
+#Test result for DELETE with AFTER TRIGGER on master
+select * from t6;
+i1	f1
+1	6
+2	6
+3	6
+4	3
+5	6
+#Test result for DELETE with AFTER TRIGGER on slave
+select * from t6;
+i1	f1
+1	6
+2	6
+3	6
+4	3
+5	6
+# Test case4: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
+create table t7(f1 int) engine=innodb;
+create table t8(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr4 before insert on t7 for each row begin
+insert into t8(f1) values(new.f1);
+insert into t8(f1) values(new.f1);
+end |
+insert into t7(f1) values(1),(2);
+insert into t7(f1) values(3);
+insert into t8(f1) values(4),(5);
+commit;
+insert into t7(f1) values(6);
+commit;
+#Test result for INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES on master
+select * from t8;
+i1	f1
+1	1
+2	1
+3	2
+4	2
+5	3
+6	3
+7	4
+8	5
+9	6
+10	6
+#Test result for INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES on slave
+select * from t8;
+i1	f1
+1	1
+2	1
+3	2
+4	2
+5	3
+6	3
+7	4
+8	5
+9	6
+10	6
+# Test case5: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
+create table t9(f1 int) engine=innodb;
+create table t10(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
+BEGIN
+INSERT INTO t10(f1) values(2);
+INSERT INTO t10(f1) values(2);
+RETURN 1;
+END//
+insert into t9(f1) values(f1_two_inserts());
+insert into t10(f1) values(4),(5);
+commit;
+insert into t9(f1) values(f1_two_inserts());
+commit;
+#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
+select * from t10;
+i1	f1
+1	2
+2	2
+3	4
+4	5
+5	2
+6	2
+#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
+select * from t10;
+i1	f1
+1	2
+2	2
+3	4
+4	5
+5	2
+6	2
+#Test case6: Test if the the autoinc values are consistent on master and slave,
+#            when INSERT with BEFORE TRIGGER
+create table t11(f1 int) engine=innodb;
+create table t12(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr6 before insert on t11 for each row insert into t12(f1) values(new.f1);
+insert into t11(f1) values(1),(2);
+insert into t11(f1) values(3);
+insert into t12(f1) values(4),(5);
+commit;
+insert into t11(f1) values(6);
+commit;
+#Test result for INSERT with BEFORE TRIGGER on master 
+select * from t12;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+#Test result for INSERT with BEFORE TRIGGER on slave
+select * from t12;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+# Test case7: Test if the the autoinc values are consistent on master and slave, 
+#             when UPDATE with BEFORE TRIGGER
+create table t13(f1 int, f2 int) engine=innodb;
+create table t14(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr7 before update on t13 for each row insert into t14(f1) values(new.f1);
+insert into t13(f1,f2) values(1,1);
+insert into t13(f1,f2) values(2,1);
+update t13 set f1 = f1 + 5 where f2 = 1;
+insert into t14(f1) values(3);
+commit;
+insert into t13(f1,f2) values(4,2);
+update t13 set f1 = f1 + 5 where f2 = 2;
+commit;
+#Test result for UPDATE with BEFORE TRIGGER on master
+select * from t14;
+i1	f1
+1	6
+2	7
+3	3
+4	9
+#Test result for UPDATE with BEFORE TRIGGER on slave
+select * from t14;
+i1	f1
+1	6
+2	7
+3	3
+4	9
+# Test case8: Test if the the autoinc values are consistent on master and slave, 
+#             when DELETE with BEFORE TRIGGER
+create table t15(f1 int, f2 int) engine=innodb;
+create table t16(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr8  before delete on t15 for each row insert into t16(f1) values(6);
+insert into t15(f1,f2) values(1,1);
+insert into t15(f1,f2) values(2,1);
+insert into t15(f1,f2) values(3,1);
+delete from t15 where f2 = 1;
+insert into t16(f1) values(3);
+commit;
+insert into t15(f1,f2) values(4,2);
+delete from t15 where f2 = 2;
+commit;
+#Test result for DELETE with BEFORE TRIGGER on master
+select * from t16;
+i1	f1
+1	6
+2	6
+3	6
+4	3
+5	6
+#Test result for DELETE with BEFORE TRIGGER on slave
+select * from t16;
+i1	f1
+1	6
+2	6
+3	6
+4	3
+5	6
+# Test case9: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+create table t17(f1 int) engine=innodb;
+create table t18(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+create trigger tr9 before insert on t17 for each row begin
+update t18 set f1 = f1 + 5 where f2 = new.f1;
+update t18 set f1 = f1 + 5 where f2 = new.f1 + 1;
+end |
+insert into t18(f1,f2) values(1,1);
+insert into t18(f1,f2) values(2,2);
+insert into t18(f1,f2) values(3,3);
+insert into t18(f1,f2) values(4,4);
+insert into t17(f1) values(1),(3);
+commit;
+insert into t17(f1) values(5);
+commit;
+#Test result for INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES on master
+select * from t18;
+i1	f1	f2
+1	6	1
+2	7	2
+3	8	3
+4	9	4
+#Test result for INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES on slave
+select * from t18;
+i1	f1	f2
+1	6	1
+2	7	2
+3	8	3
+4	9	4
+# Test case10: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+create table t19(f1 int) engine=innodb;
+create table t20(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+CREATE FUNCTION f1_two_updates() RETURNS INTEGER
+BEGIN
+update t20 set f1 = f1 + 5 where f2 = 1;
+update t20 set f1 = f1 + 5 where f2 = 2;
+update t20 set f1 = f1 + 5 where f2 = 3;
+update t20 set f1 = f1 + 5 where f2 = 4;
+RETURN 1;
+END//
+insert into t20(f1,f2) values(1,1);
+insert into t20(f1,f2) values(2,2);
+insert into t20(f1,f2) values(3,3);
+insert into t20(f1,f2) values(4,4);
+insert into t19(f1) values(f1_two_updates());
+commit;
+insert into t19(f1) values(f1_two_updates());
+commit;
+#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
+select * from t20;
+i1	f1	f2
+1	11	1
+2	12	2
+3	13	3
+4	14	4
+#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
+select * from t20;
+i1	f1	f2
+1	11	1
+2	12	2
+3	13	3
+4	14	4
+# Test case11: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A FUNCTION which CAUSES A TRIGGER
+create table t21(f1 int) engine=innodb;
+create table t22(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t23(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
+BEGIN
+INSERT INTO t22(f1) values(2),(3);
+INSERT INTO t22(f1) values(2),(3);
+RETURN 1;
+END//
+create trigger tr11 before insert on t22 for each row begin
+insert into t23(f1) values(new.f1);
+insert into t23(f1) values(new.f1);
+end |
+insert into t21(f1) values(f1_two_inserts_trigger());
+insert into t22(f1) values(4),(5);
+commit;
+insert into t21(f1) values(f1_two_inserts_trigger());
+commit;
+#Test result for INVOKES A FUNCTION which CAUSES A TRIGGER on master
+select * from t22;
+i1	f1
+1	2
+2	3
+4	2
+5	3
+7	4
+8	5
+10	2
+11	3
+13	2
+14	3
+select * from t23;
+i1	f1
+1	2
+2	2
+3	3
+4	3
+5	2
+6	2
+7	3
+8	3
+9	4
+10	4
+11	5
+12	5
+13	2
+14	2
+15	3
+16	3
+17	2
+18	2
+19	3
+20	3
+#Test result for when INVOKES A FUNCTION which CAUSES A TRIGGER on slave
+select * from t22;
+i1	f1
+1	2
+2	3
+4	2
+5	3
+7	4
+8	5
+10	2
+11	3
+13	2
+14	3
+select * from t23;
+i1	f1
+1	2
+2	2
+3	3
+4	3
+5	2
+6	2
+7	3
+8	3
+9	4
+10	4
+11	5
+12	5
+13	2
+14	2
+15	3
+16	3
+17	2
+18	2
+19	3
+20	3
+# Test case12: Test if the the autoinc values are consistent on master and slave, 
+#              when CAUSES A TRIGGER which INVOKES A FUNCTION
+create table t24(f1 int) engine=innodb;
+create table t25(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t26(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
+BEGIN
+INSERT INTO t26(f1) values(2),(3);
+INSERT INTO t26(f1) values(2),(3);
+RETURN 1;
+END//
+create trigger tr12 before insert on t24 for each row begin
+insert into t25(f1) values(f1_insert_triggered());
+insert into t25(f1) values(f1_insert_triggered());
+end |
+insert into t24(f1) values(1),(2);
+insert into t25(f1) values(3);
+commit;
+insert into t24(f1) values(4);
+commit;
+#Test result for CAUSES A TRIGGER which INVOKES A FUNCTION on master
+select * from t25;
+i1	f1
+1	1
+2	1
+3	1
+4	1
+5	3
+6	1
+7	1
+select * from t26;
+i1	f1
+1	2
+2	3
+4	2
+5	3
+7	2
+8	3
+10	2
+11	3
+13	2
+14	3
+16	2
+17	3
+19	2
+20	3
+22	2
+23	3
+25	2
+26	3
+28	2
+29	3
+31	2
+32	3
+34	2
+35	3
+#Test result for CAUSES A TRIGGER which INVOKES A FUNCTION on slave
+select * from t25;
+i1	f1
+1	1
+2	1
+3	1
+4	1
+5	3
+6	1
+7	1
+select * from t26;
+i1	f1
+1	2
+2	3
+4	2
+5	3
+7	2
+8	3
+10	2
+11	3
+13	2
+14	3
+16	2
+17	3
+19	2
+20	3
+22	2
+23	3
+25	2
+26	3
+28	2
+29	3
+31	2
+32	3
+34	2
+35	3
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP TABLE t4;
+DROP TABLE t5;
+DROP TABLE t6;
+DROP TABLE t7;
+DROP TABLE t8;
+DROP TABLE t9;
+DROP TABLE t10;
+DROP TABLE t11;
+DROP TABLE t12;
+DROP TABLE t13;
+DROP TABLE t14;
+DROP TABLE t15;
+DROP TABLE t16;
+DROP TABLE t17;
+DROP TABLE t18;
+DROP TABLE t19;
+DROP TABLE t20;
+DROP TABLE t21;
+DROP TABLE t22;
+DROP TABLE t23;
+DROP TABLE t24;
+DROP TABLE t25;
+DROP TABLE t26;
+DROP FUNCTION f1_two_inserts;
+DROP FUNCTION f1_two_updates;
+DROP FUNCTION f1_two_inserts_trigger;
+DROP FUNCTION f1_insert_triggered;

=== added file 'mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test'
--- a/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test	2009-09-03 14:59:51 +0000
@@ -0,0 +1,488 @@
+#
+# Bug45677
+# This test verifies if one query that causes a trigger or 
+# calls a function to insert or update > 1 autoinc fields 
+# will make the autoinc values are become inconsistent on 
+# master and slave. 
+#
+# Test case1: Test if the the autoinc values are consistent on master and slave, 
+#             when INSERT with AFTER TRIGGER
+# Test case2: Test if the the autoinc values are consistent on master and slave, 
+#             when UPDATE with AFTER TRIGGER
+# Test case3: Test if the the autoinc values are consistent on master and slave, 
+#             when DELETE with AFTER TRIGGER
+# Test case4: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
+# Test case5: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN 
+# Test case6: Test if the the autoinc values are consistent on master and slave, 
+#             when INSERT with BEFORE TRIGGER
+# Test case7: Test if the the autoinc values are consistent on master and slave, 
+#             when UPDATE with BEFORE TRIGGER
+# Test case8: Test if the the autoinc values are consistent on master and slave, 
+#             when DELETE with BEFORE TRIGGER
+# Test case9: Test if the the autoinc values are consistent on master and slave, 
+#             when INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+# Test case10: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+# Test case11: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A FUNCTION which CAUSES A TRIGGER
+# Test case12: Test if the the autoinc values are consistent on master and slave, 
+#              when CAUSES A TRIGGER which INVOKES A FUNCTION
+#
+
+source include/have_binlog_format_mixed.inc;
+source include/have_innodb.inc;
+source include/master-slave.inc;
+
+connection master;
+--disable_warnings
+drop table if exists t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26;
+DROP FUNCTION IF EXISTS f1_two_inserts;
+DROP FUNCTION IF EXISTS f1_two_updates;
+DROP FUNCTION IF EXISTS f1_two_inserts_trigger;
+DROP FUNCTION IF EXISTS f1_insert_triggered;
+--enable_warnings
+
+use test;
+#The transaction can't be committed untill the commit statement is executed 
+set autocommit=0;
+
+--echo #Test case1: Test if the the autoinc values are consistent on master and slave,
+--echo #            when INSERT with AFTER TRIGGER
+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);
+insert into t1(f1) values(1),(2);
+insert into t1(f1) values(3);
+
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t2(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t1(f1) values(6);
+commit;
+
+connection master;
+--echo #Test result for INSERT with AFTER TRIGGER on master 
+select * from t2;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INSERT with AFTER TRIGGER on slave
+select * from t2;
+
+
+--echo # Test case2: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when UPDATE with AFTER TRIGGER
+connection master;
+create table t3(f1 int, f2 int) engine=innodb;
+create table t4(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr2 after update on t3 for each row insert into t4(f1) values(new.f1);
+insert into t3(f1,f2) values(1,1);
+insert into t3(f1,f2) values(2,1);
+update t3 set f1 = f1 + 5 where f2 = 1;
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t4(f1) values(3);
+
+connection master;
+commit;
+insert into t3(f1,f2) values(4,2);
+update t3 set f1 = f1 + 5 where f2 = 2;
+commit;
+
+connection master;
+--echo #Test result for UPDATE with AFTER TRIGGER on master
+select * from t4;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for UPDATE with AFTER TRIGGER on slave
+select * from t4;
+
+
+--echo # Test case3: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when DELETE with AFTER TRIGGER
+connection master;
+create table t5(f1 int, f2 int) engine=innodb;
+create table t6(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr3 after delete on t5 for each row insert into t6(f1) values(6);
+insert into t5(f1,f2) values(1,1);
+insert into t5(f1,f2) values(2,1);
+insert into t5(f1,f2) values(3,1);
+delete from t5 where f2 = 1;
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t6(f1) values(3);
+
+connection master;
+commit;
+insert into t5(f1,f2) values(4,2);
+delete from t5 where f2 = 2;
+commit;
+
+connection master;
+--echo #Test result for DELETE with AFTER TRIGGER on master
+select * from t6;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for DELETE with AFTER TRIGGER on slave
+select * from t6;
+
+
+--echo # Test case4: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
+connection master;
+create table t7(f1 int) engine=innodb;
+create table t8(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+delimiter |;
+create trigger tr4 before insert on t7 for each row begin
+    insert into t8(f1) values(new.f1);
+    insert into t8(f1) values(new.f1);
+end |
+delimiter ;|
+insert into t7(f1) values(1),(2);
+insert into t7(f1) values(3);
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t8(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t7(f1) values(6);
+commit;
+
+connection master;
+--echo #Test result for INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES on master
+select * from t8;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A TRIGGER TO INSERT TWO OR MORE VALUES on slave
+select * from t8;
+
+
+--echo # Test case5: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
+connection master;
+create table t9(f1 int) engine=innodb;
+create table t10(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+delimiter //;
+CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
+BEGIN
+   INSERT INTO t10(f1) values(2);
+   INSERT INTO t10(f1) values(2);
+   RETURN 1;
+END//
+delimiter ;//
+insert into t9(f1) values(f1_two_inserts());
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t10(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t9(f1) values(f1_two_inserts());
+commit;
+
+connection master;
+--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
+select * from t10;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
+select * from t10;
+
+
+--echo #Test case6: Test if the the autoinc values are consistent on master and slave,
+--echo #            when INSERT with BEFORE TRIGGER
+connection master;
+create table t11(f1 int) engine=innodb;
+create table t12(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr6 before insert on t11 for each row insert into t12(f1) values(new.f1);
+insert into t11(f1) values(1),(2);
+insert into t11(f1) values(3);
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t12(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t11(f1) values(6);
+commit;
+
+connection master;
+--echo #Test result for INSERT with BEFORE TRIGGER on master 
+select * from t12;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INSERT with BEFORE TRIGGER on slave
+select * from t12;
+
+--echo # Test case7: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when UPDATE with BEFORE TRIGGER
+connection master;
+create table t13(f1 int, f2 int) engine=innodb;
+create table t14(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr7 before update on t13 for each row insert into t14(f1) values(new.f1);
+insert into t13(f1,f2) values(1,1);
+insert into t13(f1,f2) values(2,1);
+update t13 set f1 = f1 + 5 where f2 = 1;
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t14(f1) values(3);
+
+connection master;
+commit;
+insert into t13(f1,f2) values(4,2);
+update t13 set f1 = f1 + 5 where f2 = 2;
+commit;
+
+connection master;
+--echo #Test result for UPDATE with BEFORE TRIGGER on master
+select * from t14;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for UPDATE with BEFORE TRIGGER on slave
+select * from t14;
+
+
+--echo # Test case8: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when DELETE with BEFORE TRIGGER
+connection master;
+create table t15(f1 int, f2 int) engine=innodb;
+create table t16(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create trigger tr8  before delete on t15 for each row insert into t16(f1) values(6);
+insert into t15(f1,f2) values(1,1);
+insert into t15(f1,f2) values(2,1);
+insert into t15(f1,f2) values(3,1);
+delete from t15 where f2 = 1;
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t16(f1) values(3);
+
+connection master;
+commit;
+insert into t15(f1,f2) values(4,2);
+delete from t15 where f2 = 2;
+commit;
+
+connection master;
+--echo #Test result for DELETE with BEFORE TRIGGER on master
+select * from t16;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for DELETE with BEFORE TRIGGER on slave
+select * from t16;
+
+
+--echo # Test case9: Test if the the autoinc values are consistent on master and slave, 
+--echo #             when INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+connection master;
+create table t17(f1 int) engine=innodb;
+create table t18(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+delimiter |;
+create trigger tr9 before insert on t17 for each row begin
+    update t18 set f1 = f1 + 5 where f2 = new.f1;
+    update t18 set f1 = f1 + 5 where f2 = new.f1 + 1;
+end |
+delimiter ;|
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t18(f1,f2) values(1,1);
+insert into t18(f1,f2) values(2,2);
+insert into t18(f1,f2) values(3,3);
+insert into t18(f1,f2) values(4,4);
+insert into t17(f1) values(1),(3);
+
+connection master;
+commit;
+insert into t17(f1) values(5);
+commit;
+
+connection master;
+--echo #Test result for INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES on master
+select * from t18;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A TRIGGER TO UPDATE TWO OR MORE VALUES on slave
+select * from t18;
+
+
+--echo # Test case10: Test if the the autoinc values are consistent on master and slave, 
+--echo #              when INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
+connection master;
+create table t19(f1 int) engine=innodb;
+create table t20(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+delimiter //;
+CREATE FUNCTION f1_two_updates() RETURNS INTEGER
+BEGIN
+   update t20 set f1 = f1 + 5 where f2 = 1;
+   update t20 set f1 = f1 + 5 where f2 = 2;
+   update t20 set f1 = f1 + 5 where f2 = 3;
+   update t20 set f1 = f1 + 5 where f2 = 4;
+   RETURN 1;
+END//
+delimiter ;//
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t20(f1,f2) values(1,1);
+insert into t20(f1,f2) values(2,2);
+insert into t20(f1,f2) values(3,3);
+insert into t20(f1,f2) values(4,4);
+insert into t19(f1) values(f1_two_updates());
+
+connection master;
+commit;
+insert into t19(f1) values(f1_two_updates());
+commit;
+
+connection master;
+--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
+select * from t20;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
+select * from t20;
+
+
+--echo # Test case11: Test if the the autoinc values are consistent on master and slave, 
+--echo #              when INVOKES A FUNCTION which CAUSES A TRIGGER
+connection master;
+create table t21(f1 int) engine=innodb;
+create table t22(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t23(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+delimiter //;
+CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
+BEGIN
+   INSERT INTO t22(f1) values(2),(3);
+   INSERT INTO t22(f1) values(2),(3);
+   RETURN 1;
+END//
+delimiter ;//
+delimiter |;
+create trigger tr11 before insert on t22 for each row begin
+    insert into t23(f1) values(new.f1);
+    insert into t23(f1) values(new.f1);
+end |
+delimiter ;|
+
+insert into t21(f1) values(f1_two_inserts_trigger());
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t22(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t21(f1) values(f1_two_inserts_trigger());
+commit;
+
+connection master;
+--echo #Test result for INVOKES A FUNCTION which CAUSES A TRIGGER on master
+select * from t22;
+select * from t23;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for when INVOKES A FUNCTION which CAUSES A TRIGGER on slave
+select * from t22;
+select * from t23;
+
+--echo # Test case12: Test if the the autoinc values are consistent on master and slave, 
+--echo #              when CAUSES A TRIGGER which INVOKES A FUNCTION
+connection master;
+create table t24(f1 int) engine=innodb;
+create table t25(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t26(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+delimiter //;
+CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
+BEGIN
+   INSERT INTO t26(f1) values(2),(3);
+   INSERT INTO t26(f1) values(2),(3);
+   RETURN 1;
+END//
+delimiter ;//
+delimiter |;
+create trigger tr12 before insert on t24 for each row begin
+    insert into t25(f1) values(f1_insert_triggered());
+    insert into t25(f1) values(f1_insert_triggered());
+end |
+delimiter ;|
+
+insert into t24(f1) values(1),(2);
+
+connection master1;
+#The default autocommit is set to 1, so the statement is auto committed
+insert into t25(f1) values(3);
+
+connection master;
+commit;
+insert into t24(f1) values(4);
+commit;
+
+connection master;
+--echo #Test result for CAUSES A TRIGGER which INVOKES A FUNCTION on master
+select * from t25;
+select * from t26;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for CAUSES A TRIGGER which INVOKES A FUNCTION on slave
+select * from t25;
+select * from t26;
+
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP TABLE t4;
+DROP TABLE t5;
+DROP TABLE t6;
+DROP TABLE t7;
+DROP TABLE t8;
+DROP TABLE t9;
+DROP TABLE t10;
+DROP TABLE t11;
+DROP TABLE t12;
+DROP TABLE t13;
+DROP TABLE t14;
+DROP TABLE t15;
+DROP TABLE t16;
+DROP TABLE t17;
+DROP TABLE t18;
+DROP TABLE t19;
+DROP TABLE t20;
+DROP TABLE t21;
+DROP TABLE t22;
+DROP TABLE t23;
+DROP TABLE t24;
+DROP TABLE t25;
+DROP TABLE t26;
+DROP FUNCTION f1_two_inserts;
+DROP FUNCTION f1_two_updates;
+DROP FUNCTION f1_two_inserts_trigger;
+DROP FUNCTION f1_insert_triggered;
+sync_slave_with_master;
+

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-08-28 16:21:54 +0000
+++ b/sql/sql_base.cc	2009-09-03 14:59:51 +0000
@@ -106,7 +106,7 @@ static bool open_new_frm(THD *thd, TABLE
 static void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
                                  bool send_refresh);
 static bool
-has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables);
+has_write_table_with_auto_increment(TABLE_LIST *tables);
 
 
 extern "C" uchar *table_cache_key(const uchar *record, size_t *length,
@@ -5278,14 +5278,19 @@ int lock_tables(THD *thd, TABLE_LIST *ta
       thd->options|= OPTION_TABLE_LOCK;
       /*
         If we have >= 2 different tables to update with auto_inc columns,
-        statement-based binlogging won't work. We can solve this problem in
-        mixed mode by switching to row-based binlogging:
+        statement-based binlogging won't work.
+        One query that causes a trigger or calls a function to insert or 
+        update > 1 autoinc fields will make the autoinc values become
+        inconsistent on master and slave.
+        We can solve these problems in mixed mode by switching to row-based 
+        binlogging:
       */
+      /* The BINLOG_FORMAT_MIXED judgement is saved for suppressing 
+         warnings, but it will be removed by fixing bug#45827 */
       if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
-          has_two_write_locked_tables_with_auto_increment(tables))
+          has_write_table_with_auto_increment(tables->next_global))
       {
         thd->lex->set_stmt_unsafe();
-        thd->set_current_stmt_binlog_row_based_if_mixed();
       }
     }
 
@@ -8815,47 +8820,33 @@ void mysql_wait_completed_table(ALTER_PA
 
 
 /*
-  Tells if two (or more) tables have auto_increment columns and we want to
-  lock those tables with a write lock.
+  Check if one (or more) write tables have auto_increment columns.
 
-  SYNOPSIS
-    has_two_write_locked_tables_with_auto_increment
-      tables        Table list
+  PARAMETERS
+      tables[in]  Table list
+
+  RETURN
+      0           No
+      1           Yes
 
   NOTES:
     Call this function only when you have established the list of all tables
     which you'll want to update (including stored functions, triggers, views
     inside your statement).
-
-  RETURN
-    0  No
-    1  Yes
 */
 
 static bool
-has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables)
+has_write_table_with_auto_increment(TABLE_LIST *tables)
 {
-  char *first_table_name= NULL, *first_db;
-  LINT_INIT(first_db);
-
   for (TABLE_LIST *table= tables; table; table= table->next_global)
   {
     /* we must do preliminary checks as table->table may be NULL */
     if (!table->placeholder() &&
         table->table->found_next_number_field &&
         (table->lock_type >= TL_WRITE_ALLOW_WRITE))
-    {
-      if (first_table_name == NULL)
-      {
-        first_table_name= table->table_name;
-        first_db= table->db;
-        DBUG_ASSERT(first_db);
-      }
-      else if (strcmp(first_db, table->db) ||
-               strcmp(first_table_name, table->table_name))
-        return 1;
-    }
+      return 1;
   }
+
   return 0;
 }
 


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090903145951-sx4eonq1epise012.bundle
Thread
bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677Dao-Gang.Qu3 Sep