From: Dao-Gang.Qu Date: September 8 2009 5:49am Subject: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677 List-Archive: http://lists.mysql.com/commits/82623 X-Bug: 45677 Message-Id: <200909080550.n885ojDw010531@daogangqu-desktop> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0612449860==" --===============0612449860== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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-08 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-08 05:49:21 +0000 @@ -0,0 +1,730 @@ +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,t35,t36,t37; +drop view if exists v15; +drop view if exists v16; +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),(16); +INSERT INTO v15(c2) VALUES (17),(18); +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +#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 +4 16 +5 19 +6 20 +7 23 +8 24 +select * from t34; +i1 c2 +1 13 +2 14 +3 17 +4 18 +5 21 +6 22 +7 25 +8 26 +#Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES on slave +select * from t33; +i1 c1 +1 11 +2 12 +3 15 +4 16 +5 19 +6 20 +7 23 +8 24 +select * from t34; +i1 c2 +1 13 +2 14 +3 17 +4 18 +5 21 +6 22 +7 25 +8 26 +# Test case16: Test if the the autoinc values are consistent on master and slave, +# when INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS +CREATE TABLE t35(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t36(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t37(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb; +create trigger tr16 after insert on t35 for each row insert into t37(f1) values(new.c1); +create trigger tr17 after insert on t36 for each row insert into t37(f1) values(new.c2); +INSERT INTO t35(c1) VALUES (11), (12); +INSERT INTO t36(c2) VALUES (13), (14); +CREATE VIEW v16 AS SELECT c1, c2 FROM t35, t36; +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +#Test result for INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS on master +select * from t37; +i1 f1 +1 11 +2 12 +3 13 +4 14 +5 15 +6 16 +7 17 +8 18 +9 19 +10 20 +11 21 +12 22 +13 23 +14 24 +15 25 +16 26 +#Test result for INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS on slave +select * from t37; +i1 f1 +1 11 +2 12 +3 13 +4 14 +5 15 +6 16 +7 17 +8 18 +9 19 +10 20 +11 21 +12 22 +13 23 +14 24 +15 25 +16 26 +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 TABLE t35; +DROP TABLE t36; +DROP TABLE t37; +DROP VIEW v15; +DROP VIEW v16; +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-08 05:49:21 +0000 @@ -0,0 +1,634 @@ +# +# 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 +# Test case16: Test if the the autoinc values are consistent on master and slave, +# when INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS +# + +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,t35,t36,t37; +drop view if exists v15; +drop view if exists v16; +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),(16); +INSERT INTO v15(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); + +connection master; +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +--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; + + +--echo # Test case16: Test if the the autoinc values are consistent on master and slave, +--echo # when INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS +connection master; +CREATE TABLE t35(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t36(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t37(i1 int not null auto_increment, f1 int, primary key(i1)) engine=innodb; +create trigger tr16 after insert on t35 for each row insert into t37(f1) values(new.c1); +create trigger tr17 after insert on t36 for each row insert into t37(f1) values(new.c2); +INSERT INTO t35(c1) VALUES (11), (12); +INSERT INTO t36(c2) VALUES (13), (14); + +CREATE VIEW v16 AS SELECT c1, c2 FROM t35, t36; + +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); + +connection master; +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +--echo #Test result for INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS on master +select * from t37; +sync_slave_with_master; +connection slave; +--echo #Test result for INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS on slave +select * from t37; + + +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 TABLE t35; +DROP TABLE t36; +DROP TABLE t37; +DROP VIEW v15; +DROP VIEW v16; +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-08 05:49:21 +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; } --===============0612449860== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dao-gang.qu@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dao-gang.qu@stripped # target_branch: file:///home/daogangqu/mysql/bzrwork/bug45677/mysql-\ # 5.1-bugteam/ # testament_sha1: dfc464af24272061200d498f90e15c3cb5c4a0d9 # timestamp: 2009-09-08 13:49:45 +0800 # base_revision_id: dao-gang.qu@stripped\ # h2ikk473x5njqq29 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQmvMYEAHgjfgFfwWf///3/v /+q////0YCH/A71ZT5okz7NLs1BIABPuPvZsZe7A84wH0BoD61UqKKpXN3IqwBFEjbEvYzTIEkJS qTmxSSlK6NHWkMbSRQ8JJSnoZNR5GUNDTaRppp6mJoAyA0AAAAAShTFPaoRpoA0ADQAAAAAAAAA0 ymhKGhoAGjQD1AAAAAAANAASn6qKag0FTeqHkaGiANGAAAgAGgNANAiSITICnpoDU00anokwaNU/ VPU9J5T1DynqPKeo0B6amNQKkhAgIBMBTTJoankk8QjKYEek9TaaT1PKPRqPSeM/7O/hJJ4E9E8j zaLvVkmSZLKZLqWeqzNdmu+h7WbNuNEzXaNGw1bNmFmRSKZNGqkzNWTZZmw0XaM2jVmutlVZGuVr WthqaMM2jZq1YXbLqKGyjVNRm1ZMMmllTVddMjJs2YbJhhNl9bWlLVswNDYpFNUuE3SR7olqe3+X sXBZiQTUSLBxYt6XIysVe6eggfypgGYIszWPkdnmXa8fXfd6rM7f8aCJ4vl7e/n+b7N9HnXeursD Izy3QoqS0QQGYirQ5PLWgZ2nNCEJyRrlCdnCBcq9drau5uAAbdrbTk206hlnQ7rWWO+qvA6kbKKU KSho9Wrc3Pa0LtGZoYKUYUsuosUphFllkpMyyVW7ckkluWcWrVZ20uKXEkuSxmyYXTIpM1JSzJd1 NGaZphMlzZkwwlmi7ZmWNlM11mpssw9JsUbJlXfNnADch0RkC8kRDfEF1QDjJJLxZFJFZ1Q6Ai64 bZthfERaRdCAHZnXG9eJu4qtTDDC0wy0y0y5LNwvBqF4yB1yQExGA0HNIUnOIGs47Hh0dPDXjfEs dGYCeY0oe2LIsiEiTdQlSbqHKF70c73sXhexQ1AS0QksQKhbjU6uq3dv8ftDkNAAAcrvt7N3db2O Bo5XFvZvyTewADjfczz2sEUtSbOVIXlwUN3q/4+bn6AXPZr7ZdHdJL/+YaY3DX/1tRMYKIb0Hh0e oAbK3GED+HMo96SdlINv7urm9x1lFFEUUUccP4dYR7feySO0/E/h21VROH8U+mm3w36bvs9vYTDb dvIYYG4Mg9iQ1AhltOK2mTItlIOCDsWVpu0KTRmqYzOHmyo5IYAOwEgcevtBfuPyhCEaUD4ETgbT 8Afhuics/XZet3h94Pl4C3bhJRlzHSf9Z+0Uk/zWUpSlKWkeg1mojA0HkLZsEYGrqCPSLqF2gpi4 hGBtaaCMCgpoIwOgWy9GXrYb1hRCn3kmt4nOI5EnP+lR+IyPToJ1oTgpUkkkqlll2XMpJgEkwL02 GqCmqILQQoKIQaKGC4r5huMIECBDPqBT6QB+6t7yDgLTQRg5g2JMJKclKfvSWJ8/5VHveL6Hax5P y8QRsiPhAYZgp4i5nuuCPON+OOVy3UbX2Nzi5vzuh/+Rz6fwWcbHkUOt4Y6a6etCfNS9iVekkmZJ 4ohbP8EfaRqRuIe6e2qpSilFKaElw+8s8qVmB3dvbLWtPY9j8iynius19n5/xe8nEoklFD2ZL/pO 5pdPQq19ukJpVXqZS1GYeCyKKeKcAB6AgsCKkgwisjJHUCobortw/WyMqj4uem4FMPF22zEE9EBX OcDPMxOE9DREj0fEop6rPaouUoxBLLJRTCyzCxYppkcyhSilNy9uFXdvAurhPoJGsMsgMTOnyRPY yeb+qlKKj7zwYJxB2ISqUH8+DkS/KJelnPv6bsOSbnyLLrvJ8VM2GSYUWYXUwumEwoswuphRdgYU WXXUwuwYWLCmF1LLlyiyKFLrqWXLsMy7DDIuXWXUyXYMKSyzBkXZrsKZsM0woswupZcuupTDCYSl GFFjcpdkYLt67Cmqz/0pcustiyrTVhhkpddZlStCzOlSljRddTNRdgYUWXXUwuwmFJZhdSy5dxS4 H6qc+Fnalcl6rc34kJz6LYxwJl3BOm+e5cn7L6vyd+W9Eyx7yyJx55LqfM0ZYbfc7gYXIUDRR8iC /KAiZcOoP0+MV9S/AsXH16/MeXeEIMCBISEgwIFunHaYqck9t4bA5DcMt8x781jC9hU0uhiO3UDl yXVdQjhVlmYZhmRhERGEERFERERGERGERTya0BAJIQAAAIQhAJmzgmz827aAiqRiVsNBJBFJAlkC k8AGaQDD5FFFFhaioUShYooLJapVSMqxYlkrN3m3169ySSvKNAF6d6V7stS4t25eO6bokuOinFnn 9QJMwIhxF5JEpJw4k0tMpiiRZCYiJAggjQ1MjBRg20vVtbWtbY2YvZlbRkyasNF10ouuuYXXZMGR hhvC7DXcrLRpatWV9LtGSzc1aMYXXmmmXoSOW9Eqe62P9Ku6uylKUpkOKz61UkkkliLKK3WbqlYr FlKxZSsoSerFiRkFG4cxzLpOgqNRXQAYw2a7TCthKMym9VIQytG9wmRKIOM4CqIh3QcOIh5uCDRJ QIBBhBRIMAWdIkFqZqUrFSqLLWWYsZKM1MMLquusyve1L5sRSrrs1y69pZSj9P+PTIRzpIyOmCff vDn1RkUJPtoqSgWJycVOxoJMGCjkoUnQUlFLixcidKmGGGjpdjraOxss2bNmrVqyatWpdq1bNl11 12zZ4IHT9ylfXOj0PelI/D0akIrfraOBKRbkGQnpu0QBOqPoU5RwG3E4+rg9SKqhrlG4wV3a0ufO pW35AdRMknAFkk8cGHAYdjpvIbD7P7Yp+H0fWMJJ8QXCBt6NQ/7J1ynxOfWVhRDq4j0BAgeHDatQ OcZG4GNzEJJIWEogQhRe/sl/hcfQWBP3ODd1JclPex8BoyvdWyNWimTGSSlBYjSXm8rXS331a1lm Slm/Js/D8hkpI0WFklxGBy+DS1yfFQbn7k2KIsplDfZ1XzSK6Oq1yNjuXbjtciRmgcmGHFSyqUqq sdCbIZkSaEjI/Kj7zQsyM2CxKRRSqKoosbiYC5EzbNGxSPv7pywnYjNHRlMSpo3zxn1aJ2NhR1ro 5lPCtVZ0gVSY4KDkBHBTXNaaX1xlV4FISoBUEqLpN5TVpu/w5bpm3Dclk2KcTPmmkwmhof0XSiUj UpzcNJ+XDM1TUs5qJhNint58d0wzKNjr3HXvZDeU5cHmu2amt0nhqWa7WTQ0O/XPJNCm/jW+XeDE xN+2rLwj2VkTN2NnayGSllLUszt3FiRo/NPB5cXFg6WqOsnJssinBYspJ1uDepLGagZFAsoz47cO 3GpJsObc3MBk0HC9VVUUWGxYsYi4AjyQwI3LkLFvMooqUpSUF12bR/pKTmczCGhTuiOtsumRSZFO aPHx3V7Mm9qWRqU65SYYOu6ZFOycbJkUMFO9TRgsMGTlwYGbGZdMynTKTpb7E3U1UL6nPsYTJj+l xblWfsYZNnNVSc8Wqra2cV2GBZbFVhvUysydL1fVPWec9329SOCUpR3OK65LqFEuoXUkpSCyj4cr Hjj1Z14QQ8fhat3FatSSW7duleSyvKqSlUqlYUTDC7J5Tn43HS5TTOYdCgYuVxsNzDCx1QWZFDoQ XE6qFotjYTCyy9Imi9/YsqtMM7rODRSymOe5vaty/pTtf6Pg9TwE3m0oaCDAgu23UBvMCiEFZ2qa cdtGDTpqrLLLqWdgUYLO0obFjAwOPRmYpo3jT5yNJ7fsNCxl72ZiZPY+RsuWDt3Xxqq5Q1ly5Lwq 8ldx3cNH07u9rtDYQm5uUshbwTDCylJZTj0uedsyzmyvVqVKhKoqqU/E8TdiYC4mg0UQhCEIWLJc 70NGF1MnT2zvdua32b3H29Hue10Tcxmcqf8/PjxR8G+cPJq5Y5MZ8suh3Xq19pydDiqmKaMOtddS jD6X1NJubnq7GWn6ZXknmnBSUcFgnK9RYQUuFW1478A8iigIQMQnCSELr2XWo8yyilFlmGFpn1dT 69Oxwv0r/bk8F3a3O6ZTvyjlflzve9JS66rLXqvc8FaOLg6Wr8935PX09PI4vJLJZSVu4YLxv7Oz LDxZdV7Ws0UQhRKJRKk6BsNFjNlvq7fk1vEyNXM6TrbnhPCWvVo5WaOS8mXb4I9fcjxnxleZ4uDg se4g9Vw28uw7r+mqDiaMZKEoopaKL4iW7FXzxapR5tLHqzs71pwJwxd6N879mOK1qUsspZ+EzeR9 06mhm61yi8X4On0xbr7F8+KigoKCiDC7gMIK1aTznLUJc+PF6OJqPqWZuycGS6zNdvaLsnjl9HBa 1uhmxjmydiZdEiRIrER0eDvF3eTxdoxVlFJI8GRQjGhB0JKhMmJRMVWgqzoZoW+7OnGcq8iw8qKq iiikDu4o0rpgaGhtLS2ZimcajVsstQBcGbb7AaGgABpod3u94HJFc6zi/sLLLHU3t7i4uTqaup0t +5ubm5ubm5ubm5o2bLNmzZs2YYYYYbNlLtBwnGmdO9Xas6t6tquS5ypEzJM2AAAAVXREREUUUURR EX0YzcrTMNVMzwzrucuviVRwbnFvZOLRUuVq0ZuLJRqbHUqKmS56s1a1amYlSSUtaq1M7borGKTC 1llKc+eTizb2fW06+XDeat210z2egkS3M4/ZQH5xwAf7IA+bx26g0KrX7mFi1EIkuZUIhcNx9HMP g89satNNVXw9ch7fCI4gv2ixVIqLET8xf5Ar9/5on3fliH+Av4ARYJBYEFzW5dH95vOZGSFkSAQ/ NUXV32/0Bgv6CwHEUwC4I2pCgKF2KBBbiqfhWxBwRN4iULAWB+oAMBaFMbB/Pis96JiBAP4B0C2X IDoX9VEsiQRJFwFEzH+YAfyQGDrWDZbLQ0EAgQguWxA4im8C7+CXHXEf8sjuiKInGTAqJDIiLDN6 cyeSSoj+KTzSdKRuHKIjeNidrAdilKUgpCRsROh3DeTqZagF1AA7wiJBYcTiL1mgtAriK9fNrJjR 2E3ITLM3tatKWoqlquLjSIoJgmZE1F4i7IUkReSkiH8+ZHIkoQ3JwWJ1CTsJSTY1g4rFGIlCqYLQ LtRP/USAsP47UQb7lAn270W4LEUOFCgbRTDpIEUN5EemTaDeeaSRuSSN23JeVVUg6ZSSpFSDtFIm RUm8UjJRKU4GSRHS8ZKF9sTzaCHQXLJKG5M6Mssz910Zm6zLmCSQCSXFVLuRLaA5ImYI4jQLBF3g 0opqGcFiTVSR+5USlIkoCqJwVbmIbyJaQc4MyWJJhJSRuSDVIsiZEnZxYHPv+v7PVk1ftT9gfBPt VNJR7WH5KrPnEoEobzJAw16sKvgYfNE+tAhldL+7ITATQVqlNBMW0RKpPt5D3SvsOI/vRZ/e/wyf tw5L8afo/PfOk7qA7edkfvqdRC3XXKFQgHSNIZG8LmRycSuqDnhgosuDpQAlFTR5sDuhv1Ajz8fU jjhw+juR4Zgj5hN+P4u6gUw0HwTeAMLfBgiCYEiR1mMEeIikXlmIUJsesHF0F/eMBSKRA+KHTBSc QcVIhSoPHzn6umeLlb3PCe972H6/82iIb0dMe1m1e5Zm05dM6qSTtbT9O/Hb6yQeNRaSZBpqrETW n8ViRMoklaQkpQ6h0L4gjHA7cLDCTD9k07Xzb3mwR5tnGoid31Wb4830PjPp69YjxRPhxv3b2OAH nWlM9EYotm4yTAMAbbhFooWgUMWgBnpsIpSq4ACR1SgUIHvoVoEYSEEg+w6Ws7N769lubWNMq0i/ c72DRFT0+5+taKn0xI6pEOC2Muhk7pzqusnMUUh146tx3fZmjsOjf8gft9nh3qtxyYXQlV8fJ1Y/ xy+vIJYlNqJOMvcVZPd4T5mZNK456jVw3yzSknHMZlIopKFEUUlNVklkDRfkzCVfPPGaJmelMgnH 5TS50VtFZsVKmJqcENXlQPTHyKpDuvqL/5mD/qwfOQdqh27zlOpKEQzOIcMcqTYtYDYCy2QO8R5X 5ESO7zAxLGgRvJDo0ic6VuO7wq8yWym9L2WO/3urX4drPrabic0T6jiEXSToNuoiPKhqApCKSfTf oQHl6RM+oRDVxQIoMRHNinS3qy68EEYEgjFgzZ2UAFgiqa1WhM8yKKnqICQPAf033Oi6o2OOg2AR LNBYp0Nprm5J/WAjRF6ugbpIaEt85SNjzSTHVI0k4xI120J/ZR30E9/ifJ731Pj8fuIfN0CPmFYC EBFoRgI6bCw3LhBgQYUlFGQokfvInxZH2OTXpnFz+LofjGZNzJhkfY6z6eZb+M7frndMk9fh6rkk cwro+GioApvU1UBs9GOryiSCwIe0sjyb5q+34SE02kJq4ouiGFdtT+TTb6keHfPi9OmInyrIIk4s Qa9YFV+j/9zLr1nvKfkgI0SlKArx1fr/VEhlSlRF8uqWA12iZqEfESPuZcuz8sRjv9PbE8o3vYfy 5E5UIPMNER8fFzF5Jp4piClHAQ+oTX6hNi/t5Ir8rYDiqvuHERDL1dl/p7bYna+QZ/8jZXOq7JUl kllJZn0ypLIwyjK1SSySrUs1WXKyKyWUg9fni87DfUEVD5eGAyqJyp5+qO/Ug8RPnRxICvnOZ4sh EsIWUAgtT85t7/Ro6w7HETWf3PF8k2ed49DcTw2taW0RTtHPyeAnyR4Aj+ge6Q63kbmc+nBTFIbM AA/KCkafx/E5T3iFYqZKkvJgIolEy/QjOwhx1ambgUzGgqa9ocHg97iih755i/WGaAL1GtIW9mvH eYKg5b01ApFBPH6KWdFAoKSeCaGkdWyEffNfhvd884QshSTfwuj3cJIjsfmzjg5cTXnj9HHzzfJo jhCatSoVEMy5aKoEqkBlFAsha1bFZn7WgxjzmW1Lor64+xA09gllQNYFoiaIumo/dVkQ8iQ+RvUk UuNCzKoIhjZ4cH2BYLooRRDNRDUCnV6Mu017tSqDmCOwQoFIgsFYilESgvYkhaVUNiRiXj2Z7v/H 2gfaIqeAP7qwxF22VXsVd7l43F4qIYmFwuivynkZGAClCI+bhIkQE++gVpBQUiUb6hYfjnbMBlL7 UFYcgAQij2kosNC7YQxofjuzeCnTucjQQpGLMU9IvKglY31DRipspprQFKVCRG8EIrcYBhhh22Kt rVTXrYZ9zo6/XxBz/i+9HWnx9/wIuwiBAAICMIQRgxgemqUYRHnN/eDSBBYCBce63JyEG5gCwGMb KXjvRIwjW0kk35o+a3OgOT9HdlicqbZaaQ2qDoCnMDbuyg9NrPy4uXqAU1ae4TAdoLuD6/CqqVIY njwAFNsVOEF+at1wUuliHxm365b0sAWswaJH1UgZjJM5QceZroAIsssuvxWs3JExmXTpl08vCeCV 4CYnW5KjHtAE2dOaqDcuagTIiSvlmdhvacEbETE4zaIj7qkLwXdU77g5zZtewOybtfi1aze5inZO jhQAPm9dKAXi6TXsF1/HKvmCOSgJjfIbiUbLTiZCB/SSYymkH5unte8I4O0m56ZhMM2CMgMBMdJJ CmSTmTqaXJHWSKkJmhopQGLZ2aUTFlX22vpMVAtUCZ10V0sOaQ/uUKURShSkilCpZiVSViWSWUlY lYlYlmJWJWYlSViWZJYlkvitWWpYtWpKzUskqkqTLFFIWoUogEIAQ5UJRBYZot4jK9G/iw3S3zTN zRiRpfZIOMVP5vsuwRuNHgBCEIQUSiCMddJQGdd4PKqm3dnYQgImSMBH1qUlKoY3E3Ho4kpqtZLe mpIzlSRgdP9VhJ5VAe3x5u7DyiI+NknWSjBJCrIg6heXRJ7BKLDIGQlEUibRdXe9mSriHWtDHT9v mR7Nh5dYeuGAxVR/HjNrwt9TmmjxBTv7gbiAaEVsB0gdgfZJKkkki8kogn5YnhNY+XDVv2lK6+bx wkQ78iHRKgSfhiQNcBU2VVYOBfnQdIlz58RLBFCCu/xe3kf04v3jBn7EhGmDEIOwUah1EBTFU6XZ 1Kt+TPTDEZv5vH6G3eEE7mAX1WnfAUrWdhVnLIFKvD3OJRo9z5kDf2J2VbTjjs6GtTSgLLzj9Jcg KqqJ/i7kinChIBNeYwI= --===============0612449860==--