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