1# 2# This test verifies if concurrent transactions that invoke a 3# trigger that inserts more than one values into one or more 4# tables with an auto_increment column will make the autoinc 5# values become inconsistent on master and slave. 6# 7 8connection master; 9create table t1(a int, b int) engine=innodb; 10create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; 11eval create trigger tr1 $trigger_action on t1 for each row insert into t2(a) values(6); 12 13create table t3(a int, b int) engine=innodb; 14create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; 15create table t5(a int) engine=innodb; 16delimiter |; 17eval create trigger tr2 $trigger_action on t3 for each row begin 18 insert into t4(a) values(f1_insert_triggered()); 19 insert into t4(a) values(f1_insert_triggered()); 20 insert into t5(a) values(8); 21end | 22delimiter ;| 23 24create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; 25delimiter //; 26CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER 27BEGIN 28 INSERT INTO t6(a) values(2),(3); 29 RETURN 1; 30END// 31delimiter ;// 32 33begin; 34let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); 35insert into t1(a,b) values(1,1),(2,1); 36insert into t3(a,b) values(1,1),(2,1); 37update t1 set a = a + 5 where b = 1; 38update t3 set a = a + 5 where b = 1; 39delete from t1 where b = 1; 40delete from t3 where b = 1; 41 42connection master1; 43#The default autocommit is set to 1, so the statement is auto committed 44insert into t2(a) values(3); 45insert into t4(a) values(3); 46 47connection master; 48commit; 49insert into t1(a,b) values(4,2); 50insert into t3(a,b) values(4,2); 51update t1 set a = a + 5 where b = 2; 52update t3 set a = a + 5 where b = 2; 53delete from t1 where b = 2; 54delete from t3 where b = 2; 55--echo # To verify if insert/update in an autoinc column causes statement to be logged in row format 56source include/show_binlog_events.inc; 57commit; 58 59connection master; 60sync_slave_with_master; 61--echo #Test if the results are consistent on master and slave 62--echo #for 'INVOKES A TRIGGER with $trigger_action action' 63let $diff_tables= master:t2, slave:t2; 64source include/diff_tables.inc; 65let $diff_tables= master:t4, slave:t4; 66source include/diff_tables.inc; 67let $diff_tables= master:t6, slave:t6; 68source include/diff_tables.inc; 69 70connection master; 71DROP TABLE t1; 72DROP TABLE t2; 73DROP TABLE t3; 74DROP TABLE t4; 75DROP TABLE t5; 76DROP TABLE t6; 77DROP FUNCTION f1_insert_triggered; 78sync_slave_with_master; 79 80