List:Commits« Previous MessageNext Message »
From:Daogang Qu Date:September 28 2009 1:36pm
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3093) Bug#45677
View as plain text  
Hi Zhenxing,
Thanks a lot for the good comments. Please review the newest patch. Thanks!

Best Regards,

Doagang

He Zhenxing wrote:
> Hi Daogang,
>
> Nice work, Patch approved after fixing the messages.
>
> 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-27
>>       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 
>>     
>
> The problem is that when binlogging ...
>
>   
Good.
>>       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 the following two properties:
>>         P1) insert/update in an autoinc column causes statement to 
>>         be logged in row format if binlog_format=mixed
>>         P2) 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.
>>      @ 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_insert_view.test
>>       mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test
>>       mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_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_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-27
> 07:05:58 +0000
>> @@ -0,0 +1,44 @@
>> +#
>> +# 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; 
>> +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;
>> +sync_slave_with_master;
>> +--echo #Test if the results are consistent on master and slave
>> +--echo #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
>> +let $diff_table_1=master:test.t3;
>> +let $diff_table_2=slave:test.t3;
>> +source include/diff_tables.inc;
>> +
>> +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-27 07:05:58
> +0000
>> @@ -0,0 +1,82 @@
>> +#
>> +# 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;
>> +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
>> +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;
>> +--echo # To verify if insert/update in an autoinc column causes statement to be
> logged in row format
>> +source include/show_binlog_events.inc;
>> +commit;
>> +
>> +connection master;
>> +sync_slave_with_master;
>> +--echo #Test if the results are consistent on master and slave
>> +--echo #for 'INVOKES A TRIGGER with $trigger_action action'
>> +let $diff_table_1=master:test.t2;
>> +let $diff_table_2=slave:test.t2;
>> +source include/diff_tables.inc;
>> +let $diff_table_1=master:test.t4;
>> +let $diff_table_2=slave:test.t4;
>> +source include/diff_tables.inc;
>> +let $diff_table_1=master:test.t6;
>> +let $diff_table_2=slave:test.t6;
>> +source include/diff_tables.inc;
>> +
>> +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/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test'
>> --- a/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test	1970-01-01
> 00:00:00 +0000
>> +++ b/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test	2009-09-27
> 07:05:58 +0000
>> @@ -0,0 +1,57 @@
>> +#
>> +# 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.
>> +#
>> +
>> +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 |
>> +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;
>> +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
>> +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());
>> +--echo # To verify if insert/update in an autoinc column causes statement to be
> logged in row format
>> +source include/show_binlog_events.inc;
>> +commit;
>> +
>> +connection master;
>> +sync_slave_with_master;
>> +--echo #Test if the results are consistent on master and slave
>> +--echo #for 'CALLS A FUNCTION which INVOKES A TRIGGER with $insert_action
> action'
>> +let $diff_table_1=master:test.t2;
>> +let $diff_table_2=slave:test.t2;
>> +source include/diff_tables.inc;
>> +let $diff_table_1=master:test.t3;
>> +let $diff_table_2=slave:test.t3;
>> +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/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-27
> 07:05:58 +0000
>> @@ -0,0 +1,1041 @@
>> +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;
>> +# 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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 1
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with after insert action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 1
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with before insert action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 1
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with after update action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 1
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t1 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; delete from t3 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with before update action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 1
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with after delete action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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;
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t4(a) values(3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(1,1),(2,1)
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 1
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 1
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t3(a,b) values(4,2)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t1 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Query	#	#	use `test`; update t3 set a = a + 5 where b = 2
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t5)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t6)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Delete_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'INVOKES A TRIGGER with before delete action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t4 and slave:test.t4
>> +Comparing tables master:test.t6 and slave:test.t6
>> +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
>> +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());
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t3 and slave:test.t3
>> +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
>> +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());
>> +# To verify if insert/update in an autoinc column causes statement to be logged
> in row format
>> +show binlog events from <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
>> +master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: #
>> +master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +commit;
>> +#Test if the results are consistent on master and slave
>> +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action'
>> +Comparing tables master:test.t2 and slave:test.t2
>> +Comparing tables master:test.t3 and slave:test.t3
>> +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
>> +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 if the results are consistent on master and slave
>> +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
>> +Comparing tables master:test.t3 and slave:test.t3
>> +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
>> +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 if the results are consistent on master and slave
>> +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
>> +Comparing tables master:test.t3 and slave:test.t3
>> +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 <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=1
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(1,1),(2,2)
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=1
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a,b) values(1,1),(2,2)
>> +master-bin.000001	#	Query	#	#	use `test`; update t1,t2 set t1.a=t1.a+5,
> t2.a=t2.a+5 where t1.b=t2.b
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t1(a,b) values(3,3)
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; insert into t2(a,b) values(3,3)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +#Test if the results are consistent on master and slave
>> +#for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
>> +Comparing tables master:test.t1 and slave:test.t1
>> +Comparing tables master:test.t2 and slave:test.t2
>> +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 <binlog_start>;
>> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=1
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1(c1) VALUES (11), (12)
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=1
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2(c2) VALUES (13), (14)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	use `test`; CREATE ALGORITHM=UNDEFINED
> DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c1) VALUES (15),(16)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=3
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c2) VALUES (17),(18)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c1) VALUES (19),(20)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=5
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c2) VALUES (21),(22)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=7
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c1) VALUES (23), (24)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +master-bin.000001	#	Query	#	#	BEGIN
>> +master-bin.000001	#	Intvar	#	#	INSERT_ID=7
>> +master-bin.000001	#	Query	#	#	use `test`; INSERT INTO v15(c2) VALUES (25), (26)
>> +master-bin.000001	#	Xid	#	#	COMMIT /* XID */
>> +#Test if the results are consistent on master and slave
>> +#for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
>> +Comparing tables master:test.t1 and slave:test.t1
>> +Comparing tables master:test.t2 and slave:test.t2
>> +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-27
> 07:05:58 +0000
>> @@ -0,0 +1,214 @@
>> +#
>> +# Bug45677
>> +# This test verifies the following two properties:
>> +# P1) insert/update in an autoinc column causes statement to 
>> +#     be logged in row format if binlog_format=mixed.
>> +# P2) 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.
>> +# 
>> +# 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;
>> +
>> +--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_autoinc_func_invokes_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_autoinc_func_invokes_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 $binlog_start= 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
>> +source include/show_binlog_events.inc;
>> +
>> +sync_slave_with_master;
>> +--echo #Test if the results are consistent on master and slave
>> +--echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
>> +let $diff_table_1=master:test.t1;
>> +let $diff_table_2=slave:test.t1;
>> +source include/diff_tables.inc;
>> +let $diff_table_1=master:test.t2;
>> +let $diff_table_2=slave:test.t2;
>> +source include/diff_tables.inc;
>> +
>> +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 $binlog_start= 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
>> +source include/show_binlog_events.inc;
>> +
>> +sync_slave_with_master;
>> +--echo #Test if the results are consistent on master and slave
>> +--echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
>> +let $diff_table_1=master:test.t1;
>> +let $diff_table_2=slave:test.t1;
>> +source include/diff_tables.inc;
>> +let $diff_table_1=master:test.t2;
>> +let $diff_table_2=slave:test.t2;
>> +source include/diff_tables.inc;
>> +
>> +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-27 07:05:58 +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,17 @@ 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))
>> +        A query that modifies an autoinc column can make the master and slave 
>>     
>
> A query that modifies autoinc column in sub-statement can ...
>   
Good.
>   
>> +        inconsistent.
>> +        We can solve these problems in mixed mode by switching to binlogging 
>> +        if at least one updated table has an autoinc column
>>     
>
> ... one updated table used by sub-statement
>   
Good.
>   
>> +      */
>> +      /* 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 +8817,31 @@ 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
>> +  @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;
>>  }
>>  
>>
>>     
>
>
>   

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