List:Commits« Previous MessageNext Message »
From:Daogang Qu Date:September 27 2009 7:07am
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677
View as plain text  
Hi Sven,
Thanks for the good comments, Please review the newest patch base on 
these comments. Thanks!

Best Regards,

Daogang

Sven Sandberg wrote:
> Hi Dao-Gang!
>
> Nice work with the source patch, and ambitious test case! I have a few 
> comments, but it's mostly formalities, you did a good job.
>
> STATUS: rejected
>
> SUGGESTIONS (see comments inline):
>
> (S1) Too long filename.
>
> (S2) No need to clean up before test.
>
> (S3) Simplify code.
>
> (S4) Use diff_tables.inc instead of SELECT on master and slave.
>
> (S5) Use show_binlog_events.inc instead of SHOW BINLOG EVENTS.
>
> (S6) Improve source code comments.
>
> (S7) Improve test case comments.
>
> (S8) Please add a test showing that row mode is actually used. I think 
> it can just insert something in a table that has an autoinc column, 
> and then source include/show_binlog_events.inc.
>
> /Sven
>
>
> Dao-Gang.Qu@stripped wrote:
>> #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-14
>>       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
>>         Test result for bug#45677
>>      @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
>>         Added test to verify if 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.
>>      @ 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/extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test
> 
>>
>>       mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test
>>       mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test
>>       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/extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test' 
>>
>> --- 
>>
> a/mysql-test/extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test    
>> 1970-01-01 00:00:00 +0000
>> +++ 
>>
> b/mysql-test/extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test    
>> 2009-09-14 10:19:16 +0000
>
> (S1) This file name is currently the longest filename in the tree. 
> There have been problems in the past both because some tar program on 
> some platform didn't support long names and because paths used by 
> pushbuild became too long when the tree was unpacked deep down in some 
> directory. I don't know if these problems still exist and I don't know 
> the limit, but I'd suggest to shorten it just to be safe. Maybe 
> something like rpl_autoinc_func_invokes_trigger.test ?
Good.
>
>> @@ -0,0 +1,60 @@
>> +#
>> +# This test verifies if concurrent transactions that call a +# 
>> function which invokes a 'after/before insert action' trigger +# that 
>> inserts more than one values into a table with autoinc +# column will 
>> make the autoinc values become inconsistent on +# master and slave.
>> +#
>> +
>> +--disable_warnings
>> +DROP FUNCTION IF EXISTS f1_two_inserts_trigger;
>> +--enable_warnings
>
> (S2) There is no need to clean up before the test, we can always 
> assume the database is clean. So I think you can remove these 3 lines.
>
Right.
>> +
>> +connection master;
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +delimiter //;
>> +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
>> +BEGIN
>> +   INSERT INTO t2(a) values(2),(3);
>> +   INSERT INTO t2(a) values(2),(3);
>> +   RETURN 1;
>> +END//
>> +delimiter ;//
>> +delimiter |;
>
> (S3) Why change delimiters here? I think it would be easier to just 
> use | for both the function and the trigger.
Yes. Right.
>
>> +eval create trigger tr11 $insert_action on t2 for each row begin
>> +    insert into t3(a) values(new.a);
>> +    insert into t3(a) values(new.a);
>> +end |
>> +delimiter ;|
>> +begin;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +
>> +connection master1;
>> +#The default autocommit is set to 1, so the statement is auto committed
>> +insert into t2(a) values(4),(5);
>> +
>> +connection master;
>> +commit;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +commit;
>> +
>> +connection master;
>> +--echo #Test result for 'CALLS A FUNCTION which INVOKES A TRIGGER 
>> with $insert_action action' on master
>> +select * from t2 ORDER BY i1;
>> +select * from t3 ORDER BY i1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for 'CALLS A FUNCTION which INVOKES A TRIGGER 
>> with $insert_action action' on slave
>> +select * from t2 ORDER BY i1;
>> +select * from t3 ORDER BY i1;
>
> (S4) Instead of the above, please use the include file 
> include/diff_tables.inc for each table. That makes the result file 
> easier to read, and we don't have to manually check that the output of 
> both selects are identical. It's used like this:
>
> set $diff_tables_1= master:test.t2;
> set $diff_tables_2= master:test.t2;
> source include/diff_tables.inc;
Good.
>
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +drop table t3;
>> +drop function f1_two_inserts_trigger;
>> +sync_slave_with_master;
>> +
>>
>> === added file 
>> 'mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test'
>> --- 
>> a/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test    
>> 1970-01-01 00:00:00 +0000
>> +++ 
>> b/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test    
>> 2009-09-14 10:19:16 +0000
>> @@ -0,0 +1,49 @@
>> +#
>> +# This test verifies if inserting data into view that invokes a +# 
>> trigger will make the autoinc values become inconsistent on +# master 
>> and slave.
>> +#
>> +
>> +connection master;
>> +--disable_warnings
>> +drop view if exists v16;
>> +--enable_warnings
>
> (S2) No need to clean up before the test, I think you can remove these 
> 4 lines.
Right.
>
>> +
>> +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +eval create trigger tr16 $insert_action on t1 for each row insert 
>> into t3(a) values(new.c1);
>> +eval create trigger tr17 $insert_action on t2 for each row insert 
>> into t3(a) values(new.c2);
>> +begin;
>> +INSERT INTO t1(c1) VALUES (11), (12);
>> +INSERT INTO t2(c2) VALUES (13), (14);
>> +
>> +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
>> +
>> +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 t3 ORDER BY i1;
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS 
>> on slave
>> +select * from t3 ORDER BY i1;
>
> (S4) Please use diff_tables.inc instead (see above).
Yes. Right.
>
>> +
>> +connection master;
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP VIEW  v16;
>> +sync_slave_with_master;
>> +
>> +
>> +
>>
>> === added file 
>> 'mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test'
>> --- 
>> a/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test    
>> 1970-01-01 00:00:00 +0000
>> +++ 
>> b/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test    
>> 2009-09-14 10:19:16 +0000
>> @@ -0,0 +1,78 @@
>> +#
>> +# This test verifies if concurrent transactions that invoke a +# 
>> trigger that inserts more than one values into one or more +# tables 
>> with an auto_increment column will make the autoinc +# values become 
>> inconsistent on master and slave.
>> +#
>> +
>> +connection master;
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +eval create trigger tr1 $trigger_action on t1 for each row insert 
>> into t2(a) values(6);
>> +
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +delimiter |;
>> +eval create trigger tr2 $trigger_action on t3 for each row begin
>> +    insert into t4(a) values(f1_insert_triggered());
>> +    insert into t4(a) values(f1_insert_triggered());
>> +    insert into t5(a) values(8);
>> +end |
>> +delimiter ;|
>> +
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +delimiter //;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +   INSERT INTO t6(a) values(2),(3);
>> +   RETURN 1;
>> +END//
>> +delimiter ;//
>> +
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +
>> +connection master1;
>> +#The default autocommit is set to 1, so the statement is auto committed
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +
>> +connection master;
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +
>> +connection master;
>> +--echo #Test result for 'INVOKES A TRIGGER with $trigger_action 
>> action' on master
>> +select * from t2 ORDER BY i1;
>> +select * from t4 ORDER BY i1;
>> +select * from t6 ORDER BY i1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for 'INVOKES A TRIGGER with $trigger_action 
>> action' on slave
>> +select * from t2 ORDER BY i1;
>> +select * from t4 ORDER BY i1;
>> +select * from t6 ORDER BY i1;
>
> Please use diff_tables.inc instead (see above).
Yes. Right.
>
>> +
>> +connection master;
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +sync_slave_with_master;
>> +
>>
>> === 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-14 10:19:16 +0000
>> @@ -0,0 +1,1144 @@
>> +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 function if exists f1_two_inserts;
>> +drop function if exists f1_two_updates;
>> +drop view if exists v15;
>> +# Test case1: INVOKES A TRIGGER with after insert action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 after insert on t1 for each row insert into t2(a) 
>> values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 after insert on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with after insert action' on master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with after insert action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case2: INVOKES A TRIGGER with before insert action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 before insert on t1 for each row insert into 
>> t2(a) values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 before insert on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with before insert action' on 
>> master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with before insert action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case3: INVOKES A TRIGGER with after update action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 after update on t1 for each row insert into t2(a) 
>> values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 after update on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with after update action' on master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with after update action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case4: INVOKES A TRIGGER with before update action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 before update on t1 for each row insert into 
>> t2(a) values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 before update on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with before update action' on 
>> master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with before update action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case5: INVOKES A TRIGGER with after delete action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 after delete on t1 for each row insert into t2(a) 
>> values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 after delete on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with after delete action' on master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with after delete action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case6: INVOKES A TRIGGER with before delete action
>> +create table t1(a int, b int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr1 before delete on t1 for each row insert into 
>> t2(a) values(6);
>> +create table t3(a int, b int) engine=innodb;
>> +create table t4(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t5(a int) engine=innodb;
>> +create trigger tr2 before delete on t3 for each row begin
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t4(a) values(f1_insert_triggered());
>> +insert into t5(a) values(8);
>> +end |
>> +create table t6(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t6(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,1);
>> +insert into t3(a,b) values(1,1),(2,1);
>> +update t1 set a = a + 5 where b = 1;
>> +update t3 set a = a + 5 where b = 1;
>> +delete from t1 where b = 1;
>> +delete from t3 where b = 1;
>> +insert into t2(a) values(3);
>> +insert into t4(a) values(3);
>> +commit;
>> +insert into t1(a,b) values(4,2);
>> +insert into t3(a,b) values(4,2);
>> +update t1 set a = a + 5 where b = 2;
>> +update t3 set a = a + 5 where b = 2;
>> +delete from t1 where b = 2;
>> +delete from t3 where b = 2;
>> +commit;
>> +#Test result for 'INVOKES A TRIGGER with before delete action' on 
>> master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +#Test result for 'INVOKES A TRIGGER with before delete action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    6
>> +2    6
>> +3    3
>> +4    6
>> +select * from t4 ORDER BY i1;
>> +i1    a
>> +1    1
>> +2    1
>> +3    1
>> +4    1
>> +5    3
>> +6    1
>> +7    1
>> +select * from t6 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    2
>> +8    3
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +16    2
>> +17    3
>> +DROP TABLE t1;
>> +DROP TABLE t2;
>> +DROP TABLE t3;
>> +DROP TABLE t4;
>> +DROP TABLE t5;
>> +DROP TABLE t6;
>> +DROP FUNCTION f1_insert_triggered;
>> +# Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after 
>> insert action
>> +DROP FUNCTION IF EXISTS f1_two_inserts_trigger;
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t2(a) values(2),(3);
>> +INSERT INTO t2(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +create trigger tr11 after insert on t2 for each row begin
>> +insert into t3(a) values(new.a);
>> +insert into t3(a) values(new.a);
>> +end |
>> +begin;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +insert into t2(a) values(4),(5);
>> +commit;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +commit;
>> +#Test result for 'CALLS A FUNCTION which INVOKES A TRIGGER with 
>> after insert action' on master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    4
>> +8    5
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +select * from t3 ORDER BY i1;
>> +i1    a
>> +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 with 
>> after insert action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    4
>> +8    5
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +select * from t3 ORDER BY i1;
>> +i1    a
>> +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
>> +drop table t1;
>> +drop table t2;
>> +drop table t3;
>> +drop function f1_two_inserts_trigger;
>> +# Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before 
>> insert action
>> +DROP FUNCTION IF EXISTS f1_two_inserts_trigger;
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create table t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t2(a) values(2),(3);
>> +INSERT INTO t2(a) values(2),(3);
>> +RETURN 1;
>> +END//
>> +create trigger tr11 before insert on t2 for each row begin
>> +insert into t3(a) values(new.a);
>> +insert into t3(a) values(new.a);
>> +end |
>> +begin;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +insert into t2(a) values(4),(5);
>> +commit;
>> +insert into t1(a) values(f1_two_inserts_trigger());
>> +commit;
>> +#Test result for 'CALLS A FUNCTION which INVOKES A TRIGGER with 
>> before insert action' on master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    4
>> +8    5
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +select * from t3 ORDER BY i1;
>> +i1    a
>> +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 with 
>> before insert action' on slave
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    3
>> +4    2
>> +5    3
>> +7    4
>> +8    5
>> +10    2
>> +11    3
>> +13    2
>> +14    3
>> +select * from t3 ORDER BY i1;
>> +i1    a
>> +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
>> +drop table t1;
>> +drop table t2;
>> +drop table t3;
>> +drop function f1_two_inserts_trigger;
>> +# Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with 
>> after insert action
>> +drop view if exists v16;
>> +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr16 after insert on t1 for each row insert into 
>> t3(a) values(new.c1);
>> +create trigger tr17 after insert on t2 for each row insert into 
>> t3(a) values(new.c2);
>> +begin;
>> +INSERT INTO t1(c1) VALUES (11), (12);
>> +INSERT INTO t2(c2) VALUES (13), (14);
>> +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
>> +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 t3 ORDER BY i1;
>> +i1    a
>> +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 t3 ORDER BY i1;
>> +i1    a
>> +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 VIEW  v16;
>> +# Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with 
>> before insert action
>> +drop view if exists v16;
>> +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t3(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +create trigger tr16 before insert on t1 for each row insert into 
>> t3(a) values(new.c1);
>> +create trigger tr17 before insert on t2 for each row insert into 
>> t3(a) values(new.c2);
>> +begin;
>> +INSERT INTO t1(c1) VALUES (11), (12);
>> +INSERT INTO t2(c2) VALUES (13), (14);
>> +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
>> +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 t3 ORDER BY i1;
>> +i1    a
>> +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 t3 ORDER BY i1;
>> +i1    a
>> +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 VIEW  v16;
>> +# Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO 
>> A TABLE WITH AUTOINC COLUMN
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
>> +BEGIN
>> +INSERT INTO t2(a) values(2);
>> +INSERT INTO t2(a) values(2);
>> +RETURN 1;
>> +END//
>> +begin;
>> +insert into t1(a) values(f1_two_inserts());
>> +insert into t2(a) values(4),(5);
>> +commit;
>> +insert into t1(a) values(f1_two_inserts());
>> +commit;
>> +#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on 
>> master
>> +select * from t2 ORDER BY i1;
>> +i1    a
>> +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 t2 ORDER BY i1;
>> +i1    a
>> +1    2
>> +2    2
>> +3    4
>> +4    5
>> +5    2
>> +6    2
>> +drop table t1;
>> +drop table t2;
>> +drop function f1_two_inserts;
>> +# Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A 
>> TABLE WITH AUTOINC COLUMN
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +CREATE FUNCTION f1_two_updates() RETURNS INTEGER
>> +BEGIN
>> +update t2 set a = a + 5 where b = 1;
>> +update t2 set a = a + 5 where b = 2;
>> +update t2 set a = a + 5 where b = 3;
>> +update t2 set a = a + 5 where b = 4;
>> +RETURN 1;
>> +END//
>> +insert into t2(a,b) values(1,1);
>> +insert into t2(a,b) values(2,2);
>> +insert into t2(a,b) values(3,3);
>> +insert into t2(a,b) values(4,4);
>> +insert into t1(a) values(f1_two_updates());
>> +begin;
>> +insert into t1(a) values(f1_two_updates());
>> +commit;
>> +#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on 
>> master
>> +select * from t2 ORDER BY i1;
>> +i1    a    b
>> +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 t2 ORDER BY i1;
>> +i1    a    b
>> +1    11    1
>> +2    12    2
>> +3    13    3
>> +4    14    4
>> +drop table t1;
>> +drop table t2;
>> +drop function f1_two_updates;
>> +# Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
>> +create table t1(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +begin;
>> +insert into t1(a,b) values(1,1),(2,2);
>> +insert into t2(a,b) values(1,1),(2,2);
>> +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
>> +insert into t1(a,b) values(3,3);
>> +insert into t2(a,b) values(3,3);
>> +commit;
>> +# To verify if it works fine when these statements are not be marked 
>> as unsafe
>> +SHOW BINLOG EVENTS FROM 50427;
>> +Log_name    Pos    Event_type    Server_id    End_log_pos    Info
>> +master-bin.000001    50427    Query    1    50495    BEGIN
>> +master-bin.000001    50495    Intvar    1    50523    INSERT_ID=1
>> +master-bin.000001    50523    Query    1    50623    use `test`; 
>> insert into t1(a,b) values(1,1),(2,2)
>> +master-bin.000001    50623    Intvar    1    50651    INSERT_ID=1
>> +master-bin.000001    50651    Query    1    50751    use `test`; 
>> insert into t2(a,b) values(1,1),(2,2)
>> +master-bin.000001    50751    Query    1    50880    use `test`; 
>> update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b
>> +master-bin.000001    50880    Intvar    1    50908    INSERT_ID=3
>> +master-bin.000001    50908    Query    1    51002    use `test`; 
>> insert into t1(a,b) values(3,3)
>> +master-bin.000001    51002    Intvar    1    51030    INSERT_ID=3
>> +master-bin.000001    51030    Query    1    51124    use `test`; 
>> insert into t2(a,b) values(3,3)
>> +master-bin.000001    51124    Xid    1    51151    COMMIT /* xid=642 */
>> +#Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on master
>> +select * from t1 ORDER BY i1;
>> +i1    a    b
>> +1    6    1
>> +2    7    2
>> +3    3    3
>> +select * from t2 ORDER BY i1;
>> +i1    a    b
>> +1    6    1
>> +2    7    2
>> +3    3    3
>> +#Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT on slave
>> +select * from t1 ORDER BY i1;
>> +i1    a    b
>> +1    6    1
>> +2    7    2
>> +3    3    3
>> +select * from t2 ORDER BY i1;
>> +i1    a    b
>> +1    6    1
>> +2    7    2
>> +3    3    3
>> +drop table t1;
>> +drop table t2;
>> +# Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE 
>> TABLES
>> +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary 
>> key(i1)) engine=innodb;
>> +begin;
>> +INSERT INTO t1(c1) VALUES (11), (12);
>> +INSERT INTO t2(c2) VALUES (13), (14);
>> +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
>> +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;
>> +# To verify if it works fine when these statements are not be marked 
>> as unsafe
>> +SHOW BINLOG EVENTS FROM 51601;
>> +Log_name    Pos    Event_type    Server_id    End_log_pos    Info
>> +master-bin.000001    51601    Query    1    51669    BEGIN
>> +master-bin.000001    51669    Intvar    1    51697    INSERT_ID=1
>> +master-bin.000001    51697    Query    1    51796    use `test`; 
>> INSERT INTO t1(c1) VALUES (11), (12)
>> +master-bin.000001    51796    Intvar    1    51824    INSERT_ID=1
>> +master-bin.000001    51824    Query    1    51923    use `test`; 
>> INSERT INTO t2(c2) VALUES (13), (14)
>> +master-bin.000001    51923    Xid    1    51950    COMMIT /* xid=661 */
>> +master-bin.000001    51950    Query    1    52127    use `test`; 
>> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY 
>> DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2
>> +master-bin.000001    52127    Query    1    52195    BEGIN
>> +master-bin.000001    52195    Intvar    1    52223    INSERT_ID=3
>> +master-bin.000001    52223    Query    1    52322    use `test`; 
>> INSERT INTO v15(c1) VALUES (15),(16)
>> +master-bin.000001    52322    Xid    1    52349    COMMIT /* xid=664 */
>> +master-bin.000001    52349    Query    1    52417    BEGIN
>> +master-bin.000001    52417    Intvar    1    52445    INSERT_ID=3
>> +master-bin.000001    52445    Query    1    52544    use `test`; 
>> INSERT INTO v15(c2) VALUES (17),(18)
>> +master-bin.000001    52544    Xid    1    52571    COMMIT /* xid=665 */
>> +master-bin.000001    52571    Query    1    52639    BEGIN
>> +master-bin.000001    52639    Intvar    1    52667    INSERT_ID=5
>> +master-bin.000001    52667    Query    1    52766    use `test`; 
>> INSERT INTO v15(c1) VALUES (19),(20)
>> +master-bin.000001    52766    Xid    1    52793    COMMIT /* xid=666 */
>> +master-bin.000001    52793    Query    1    52861    BEGIN
>> +master-bin.000001    52861    Intvar    1    52889    INSERT_ID=5
>> +master-bin.000001    52889    Query    1    52988    use `test`; 
>> INSERT INTO v15(c2) VALUES (21),(22)
>> +master-bin.000001    52988    Xid    1    53015    COMMIT /* xid=667 */
>> +master-bin.000001    53015    Query    1    53083    BEGIN
>> +master-bin.000001    53083    Intvar    1    53111    INSERT_ID=7
>> +master-bin.000001    53111    Query    1    53211    use `test`; 
>> INSERT INTO v15(c1) VALUES (23), (24)
>> +master-bin.000001    53211    Xid    1    53238    COMMIT /* xid=668 */
>> +master-bin.000001    53238    Query    1    53306    BEGIN
>> +master-bin.000001    53306    Intvar    1    53334    INSERT_ID=7
>> +master-bin.000001    53334    Query    1    53434    use `test`; 
>> INSERT INTO v15(c2) VALUES (25), (26)
>> +master-bin.000001    53434    Xid    1    53461    COMMIT /* xid=669 */
>> +#Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE 
>> TABLES on master
>> +select * from t1 ORDER BY i1;
>> +i1    c1
>> +1    11
>> +2    12
>> +3    15
>> +4    16
>> +5    19
>> +6    20
>> +7    23
>> +8    24
>> +select * from t2 ORDER BY i1;
>> +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 t1 ORDER BY i1;
>> +i1    c1
>> +1    11
>> +2    12
>> +3    15
>> +4    16
>> +5    19
>> +6    20
>> +7    23
>> +8    24
>> +select * from t2 ORDER BY i1;
>> +i1    c2
>> +1    13
>> +2    14
>> +3    17
>> +4    18
>> +5    21
>> +6    22
>> +7    25
>> +8    26
>> +drop table t1;
>> +drop table t2;
>> +drop view  v15;
>>
>> === 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-14 10:19:16 +0000
>> @@ -0,0 +1,210 @@
>> +#
>> +# Bug45677
>> +# This test verifies if 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. 
>
> (S7) I think this comment can be improved. When writing a test case, I 
> think it is important to state (1) what property of the server should 
> be tested; (2) how is the property tested.
>
> In this case, i think there are at least two properties to test:
>
>  (P1) insert/update in an autoinc column causes statement to be logged 
> in row format if binlog_format=mixed.
>
>  (P2) BUG#45677: if binlog_format=mixed, and a trigger or function 
> contains two or more inserts/updates in a table that has an autoinc 
> column, then the slave should not go out of sync, even if there are 
> concurrent transactions. (This property follows from (P1) but should 
> be tested separately.)
>
> Property (P1) is tested by executing an insert and an update on a 
> table that has an autoinc column, and verifying that these statements 
> result in row events in the binlog.
>
> Property (P2) is tested by setting up the test scenario and verifying 
> that the tables are identical on master and slave.
>
>
>> +#
>> +
>> +source include/have_binlog_format_mixed.inc;
>> +source include/have_innodb.inc;
>> +source include/master-slave.inc;
>> +--disable_warnings
>> +drop function if exists f1_two_inserts;
>> +drop function if exists f1_two_updates;
>> +drop view if exists v15;
>> +--enable_warnings
>
> (S2) No need to clean up before the test, I think you can remove these 
> 5 lines.
Yes. Right.
>
>> +
>> +--echo # Test case1: INVOKES A TRIGGER with after insert action
>> +let $trigger_action = after insert;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case2: INVOKES A TRIGGER with before insert action
>> +let $trigger_action = before insert;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case3: INVOKES A TRIGGER with after update action
>> +let $trigger_action = after update;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case4: INVOKES A TRIGGER with before update action
>> +let $trigger_action = before update;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case5: INVOKES A TRIGGER with after delete action
>> +let $trigger_action = after delete;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case6: INVOKES A TRIGGER with before delete action
>> +let $trigger_action = before delete;
>> +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
>> +
>> +--echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with 
>> after insert action
>> +let $insert_action = after insert;
>> +source 
>> extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test;
>> +
>> +--echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with 
>> before insert action
>> +let $insert_action = before insert;
>> +source 
>> extra/rpl_tests/rpl_auto_increment_call_func_which_invoke_trigger.test;
>> +
>> +--echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS 
>> with after insert action
>> +let $insert_action = after insert;
>> +source extra/rpl_tests/rpl_auto_increment_insert_view.test;
>> +
>> +--echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS 
>> with before insert action
>> +let $insert_action = before insert;
>> +source extra/rpl_tests/rpl_auto_increment_insert_view.test;
>> +
>> +--echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE 
>> VALUES INTO A TABLE WITH AUTOINC COLUMN
>> +connection master;
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, primary 
>> key(i1)) engine=innodb;
>> +delimiter //;
>> +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
>> +BEGIN
>> +   INSERT INTO t2(a) values(2);
>> +   INSERT INTO t2(a) values(2);
>> +   RETURN 1;
>> +END//
>> +delimiter ;//
>> +begin;
>> +insert into t1(a) values(f1_two_inserts());
>> +
>> +connection master1;
>> +#The default autocommit is set to 1, so the statement is auto committed
>> +insert into t2(a) values(4),(5);
>> +
>> +connection master;
>> +commit;
>> +insert into t1(a) values(f1_two_inserts());
>> +commit;
>> +
>> +connection master;
>> +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE 
>> VALUES on master
>> +select * from t2 ORDER BY i1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE 
>> VALUES on slave
>> +select * from t2 ORDER BY i1;
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +drop function f1_two_inserts;
>> +sync_slave_with_master;
>> +
>> +--echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE 
>> VALUES OF A TABLE WITH AUTOINC COLUMN
>> +connection master;
>> +create table t1(a int) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +delimiter //;
>> +CREATE FUNCTION f1_two_updates() RETURNS INTEGER
>> +BEGIN
>> +   update t2 set a = a + 5 where b = 1;
>> +   update t2 set a = a + 5 where b = 2;
>> +   update t2 set a = a + 5 where b = 3;
>> +   update t2 set a = a + 5 where b = 4;
>> +   RETURN 1;
>> +END//
>> +delimiter ;//
>> +
>> +connection master1;
>> +#The default autocommit is set to 1, so the statement is auto committed
>> +insert into t2(a,b) values(1,1);
>> +insert into t2(a,b) values(2,2);
>> +insert into t2(a,b) values(3,3);
>> +insert into t2(a,b) values(4,4);
>> +insert into t1(a) values(f1_two_updates());
>> +
>> +connection master;
>> +begin;
>> +insert into t1(a) values(f1_two_updates());
>> +commit;
>> +
>> +connection master;
>> +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE 
>> VALUES on master
>> +select * from t2 ORDER BY i1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE 
>> VALUES on slave
>> +select * from t2 ORDER BY i1;
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +drop function f1_two_updates;
>> +sync_slave_with_master;
>> +
>> +--echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
>> +connection master;
>> +create table t1(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +create table t2(i1 int not null auto_increment, a int, b int, 
>> primary key(i1)) engine=innodb;
>> +begin;
>> +let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1);
>> +insert into t1(a,b) values(1,1),(2,2);
>> +insert into t2(a,b) values(1,1),(2,2);
>> +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
>> +insert into t1(a,b) values(3,3);
>> +insert into t2(a,b) values(3,3);
>> +commit;
>> +--echo # To verify if it works fine when these statements are not be 
>> marked as unsafe
>> +eval SHOW BINLOG EVENTS FROM $pos0_master;
>
> (S5) SHOW BINLOG EVENTS outputs too much information (e.g., binlog 
> positions that may vary between versions). Please do 'source 
> include/show_binlog_events.inc' instead, it normalizes the output.
Good.
>
>> +--echo #Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT 
>> on master
>> +select * from t1 ORDER BY i1;
>> +select * from t2 ORDER BY i1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT 
>> on slave
>> +select * from t1 ORDER BY i1;
>> +select * from t2 ORDER BY i1;
>
> (S4) Please use diff_tables.inc instead (see above).
Right.
>
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +sync_slave_with_master;
>> +
>> +--echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN 
>> ONE TABLES
>> +connection master;
>> +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary 
>> key(i1)) engine=innodb;
>> +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary 
>> key(i1)) engine=innodb;
>> +let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1);
>> +begin;
>> +INSERT INTO t1(c1) VALUES (11), (12);
>> +INSERT INTO t2(c2) VALUES (13), (14);
>> +
>> +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
>> +
>> +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 # To verify if it works fine when these statements are not be 
>> marked as unsafe
>> +eval SHOW BINLOG EVENTS FROM $pos1_master;
>
> (S5) Please use show_binlog_events.inc instead (see above).
Right.
>
>> +
>> +--echo #Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE 
>> THAN ONE TABLES on master
>> +select * from t1 ORDER BY i1;
>> +select * from t2 ORDER BY i1;
>> +sync_slave_with_master;
>> +connection slave;
>> +--echo #Test result for INSERT DATA INTO VIEW WHICH INVOLVED MORE 
>> THAN ONE TABLES on slave
>> +select * from t1 ORDER BY i1;
>> +select * from t2 ORDER BY i1;
>
> (S4) Please use diff_tables.inc instead (see above).
Right.
>
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +drop view  v15;
>> +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-14 10:19:16 +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,
>> @@ -5277,15 +5277,18 @@ int lock_tables(THD *thd, TABLE_LIST *ta
>>        thd->in_lock_tables=1;
>>        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))
>> +        One query that invokes a trigger or calls a function that 
>> updated
>> +        autoinc fields can make the autoinc values become 
>> inconsistent +        on master and slave.
>
> (S6) I don't think we need a trigger or function call to make the 
> values inconsistent on master and slave. This comment will be a bit 
> confusing when read outside the context of BUG#45677. I would suggest 
> to just say something like "A query that modifies an autoinc column 
> can make the master and slave inconsistent, e.g., if the 
> inserted/updated rows are ordered differently on master and slave."
Yes. It's better.
>
>> +        We can solve these problems in mixed mode by switching to 
>> row-based +        binlogging:
>
> (S6) To be more clear, you could say "... switching to binlogging if 
> at least one updated table has an autoinc column".
It's better.
>
>
>> +      */
>> +      /* 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->first_not_own_table()))
>>        {
>>          thd->lex->set_stmt_unsafe();
>> -        thd->set_current_stmt_binlog_row_based_if_mixed();
>>        }
>>      }
>>  
>> @@ -8815,47 +8818,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
>
> (S6) Please use doxygen-style comments. Also, good to clarify the 
> return values a bit, like:
>
> @param[in] tables Table list
> @retval 0 if at least one write tables has an auto_increment column
> @retval 1 otherwise
Yes. It's better.
>
>>  
>>    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;
>>  }
>>  
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>
>

Thread
bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677Dao-Gang.Qu14 Sep
  • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677Sven Sandberg22 Sep
    • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677Daogang Qu27 Sep