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
