List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:September 6 2009 2:54pm
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-06
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query. If more than one autoinc values are used in the query, the 
      autoinc values after the first one can be inserted wrongly on slave.
      So these autoinc values can become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ 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-06 14:53:01 +0000
@@ -0,0 +1,643 @@
+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;
+drop table if exists t26,t27,t28,t29,t30,t31,t32,t33,t34;
+drop view if exists v15;
+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 CALLS A FUNCTION which INVOKES 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 CALLS A FUNCTION which INVOKES 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 CALLS A FUNCTION which INVOKES 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 INVOKES A TRIGGER which CALLS 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 INVOKES A TRIGGER which CALLS 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 INVOKES A TRIGGER which CALLS 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
+# Test case13: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES
+create table t27(f1 int) engine=innodb;
+create table t28(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t29(f1 int) engine=innodb;
+create table t30(f1 int) engine=innodb;
+create trigger tr13 before insert on t27 for each row begin
+insert into t28(f1) values(new.f1);
+insert into t29(f1) values(new.f1);
+insert into t30(f1) values(new.f1);
+end |
+insert into t27(f1) values(1),(2);
+insert into t27(f1) values(3);
+insert into t28(f1) values(4),(5);
+insert into t29(f1) values(4),(5);
+insert into t30(f1) values(4),(5);
+commit;
+insert into t27(f1) values(6);
+commit;
+#Test result for INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES on master
+select * from t28;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+#Test result for INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES on slave
+select * from t28;
+i1	f1
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+# Test case14: Test if the the autoinc values are consistent on master and slave,
+#              when UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
+create table t31(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+create table t32(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+insert into t31(f1,f2) values(1,1),(2,2);
+insert into t32(f1,f2) values(1,1),(2,2);
+update t31,t32 set t31.f1=t31.f1+5, t32.f1=t32.f1+5 where t31.f2=t32.f2;
+insert into t31(f1,f2) values(3,3);
+insert into t32(f1,f2) values(3,3);
+commit;
+#Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on master
+select * from t31;
+i1	f1	f2
+1	6	1
+2	7	2
+3	3	3
+select * from t32;
+i1	f1	f2
+1	6	1
+2	7	2
+3	3	3
+#Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on slave
+select * from t31;
+i1	f1	f2
+1	6	1
+2	7	2
+3	3	3
+select * from t32;
+i1	f1	f2
+1	6	1
+2	7	2
+3	3	3
+# Test case15: Test if the the autoinc values are consistent on master and slave, 
+#              when INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
+CREATE TABLE t33(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
+CREATE TABLE t34(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
+INSERT INTO t33(c1) VALUES (11), (12);
+INSERT INTO t34(c2) VALUES (13), (14);
+CREATE VIEW v15 AS SELECT c1, c2 FROM t33, t34;
+INSERT INTO v15(c1) VALUES (15);
+INSERT INTO v15(c2) VALUES (16);
+#Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES on master
+select * from t33;
+i1	c1
+1	11
+2	12
+3	15
+select * from t34;
+i1	c2
+1	13
+2	14
+3	16
+#Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES on slave
+select * from t33;
+i1	c1
+1	11
+2	12
+select * from t34;
+i1	c2
+1	13
+2	14
+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 TABLE t27;
+DROP TABLE t28;
+DROP TABLE t29;
+DROP TABLE t30;
+DROP TABLE t31;
+DROP TABLE t32;
+DROP TABLE t33;
+DROP TABLE t34;
+DROP VIEW v15;
+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-06 14:53:01 +0000
@@ -0,0 +1,588 @@
+#
+# 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 CALLS A FUNCTION which INVOKES A TRIGGER
+# Test case12: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A TRIGGER which CALLS A FUNCTION
+# Test case13: Test if the the autoinc values are consistent on master and slave, 
+#              when INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES
+# Test case14: Test if the the autoinc values are consistent on master and slave,
+#              when UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
+# Test case15: Test if the the autoinc values are consistent on master and slave, 
+#              when INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
+#
+
+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;
+drop table if exists t26,t27,t28,t29,t30,t31,t32,t33,t34;
+drop view if exists v15;
+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 CALLS A FUNCTION which INVOKES 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 CALLS A FUNCTION which INVOKES A TRIGGER on master
+select * from t22;
+select * from t23;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for CALLS A FUNCTION which INVOKES 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 INVOKES A TRIGGER which CALLS 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 INVOKES A TRIGGER which CALLS A FUNCTION on master
+select * from t25;
+select * from t26;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A TRIGGER which CALLS A FUNCTION on slave
+select * from t25;
+select * from t26;
+
+--echo # Test case13: Test if the the autoinc values are consistent on master and slave, 
+--echo #              when INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES
+connection master;
+create table t27(f1 int) engine=innodb;
+create table t28(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb;
+create table t29(f1 int) engine=innodb;
+create table t30(f1 int) engine=innodb;
+delimiter |;
+create trigger tr13 before insert on t27 for each row begin
+    insert into t28(f1) values(new.f1);
+    insert into t29(f1) values(new.f1);
+    insert into t30(f1) values(new.f1);
+end |
+delimiter ;|
+insert into t27(f1) values(1),(2);
+insert into t27(f1) values(3);
+
+connection master1;
+insert into t28(f1) values(4),(5);
+insert into t29(f1) values(4),(5);
+insert into t30(f1) values(4),(5);
+
+connection master;
+commit;
+insert into t27(f1) values(6);
+commit;
+
+connection master;
+--echo #Test result for INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES on master
+select * from t28;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INVOKES A TRIGGER which INVOLVES MORE THAN ONE TABLES on slave
+select * from t28;
+
+
+--echo # Test case14: Test if the the autoinc values are consistent on master and slave,
+--echo #              when UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
+connection master;
+create table t31(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+create table t32(i1 int not null auto_increment, f1 int, f2 int, primary key(i1)) engine=innodb;
+insert into t31(f1,f2) values(1,1),(2,2);
+insert into t32(f1,f2) values(1,1),(2,2);
+update t31,t32 set t31.f1=t31.f1+5, t32.f1=t32.f1+5 where t31.f2=t32.f2;
+insert into t31(f1,f2) values(3,3);
+insert into t32(f1,f2) values(3,3);
+commit;
+--echo #Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on master
+select * from t31;
+select * from t32;
+
+sync_slave_with_master;
+connection slave;
+--echo #Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on slave
+select * from t31;
+select * from t32;
+
+
+--echo # Test case15: Test if the the autoinc values are consistent on master and slave, 
+--echo #              when INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
+connection master;
+CREATE TABLE t33(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
+CREATE TABLE t34(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
+
+INSERT INTO t33(c1) VALUES (11), (12);
+INSERT INTO t34(c2) VALUES (13), (14);
+
+CREATE VIEW v15 AS SELECT c1, c2 FROM t33, t34;
+
+INSERT INTO v15(c1) VALUES (15);
+INSERT INTO v15(c2) VALUES (16);
+
+connection master;
+--echo #Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES on master
+select * from t33;
+select * from t34;
+sync_slave_with_master;
+connection slave;
+--echo #Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES on slave
+select * from t33;
+select * from t34;
+
+
+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 TABLE t27;
+DROP TABLE t28;
+DROP TABLE t29;
+DROP TABLE t30;
+DROP TABLE t31;
+DROP TABLE t32;
+DROP TABLE t33;
+DROP TABLE t34;
+DROP VIEW v15;
+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-06 14:53:01 +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:
-      */
-      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
-          has_two_write_locked_tables_with_auto_increment(tables))
+        statement-based binlogging won't work.
+        One query that invokes a trigger or calls a function that updated
+        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 && tables && 
+          has_write_table_with_auto_increment(*thd->lex->query_tables_own_last))
       {
         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-20090906145301-9a0lc5jnx169hycu.bundle
Thread
bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677Dao-Gang.Qu6 Sep