List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:September 22 2009 9:52am
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677
View as plain text  
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 ?

> @@ -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.

> +
> +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.

> +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;

> +
> +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.

> +
> +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).

> +
> +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).

> +
> +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.

> +
> +--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.

> +--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).

> +
> +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).

> +
> +--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).

> +
> +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."

> +        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".

> +      */
> +      /* 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

>  
>    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;
>  }
>  
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 


-- 
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
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