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.
9 create table t1(a int, b int) engine=innodb;
10 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
11 eval create trigger tr1 $trigger_action on t1 for each row insert into t2(a) values(6);
13 create table t3(a int, b int) engine=innodb;
14 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
15 create table t5(a int) engine=innodb;
17 eval 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);
24 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
26 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
28 INSERT INTO t6(a) values(2),(3);
34 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
35 insert into t1(a,b) values(1,1),(2,1);
36 insert into t3(a,b) values(1,1),(2,1);
37 update t1 set a = a + 5 where b = 1;
38 update t3 set a = a + 5 where b = 1;
39 delete from t1 where b = 1;
40 delete from t3 where b = 1;
43 #The default autocommit is set to 1, so the statement is auto committed
44 insert into t2(a) values(3);
45 insert into t4(a) values(3);
49 insert into t1(a,b) values(4,2);
50 insert into t3(a,b) values(4,2);
51 update t1 set a = a + 5 where b = 2;
52 update t3 set a = a + 5 where b = 2;
53 delete from t1 where b = 2;
54 delete from t3 where b = 2;
55 --echo # To verify if insert/update in an autoinc column causes statement to be logged in row format
56 source include/show_binlog_events.inc;
60 sync_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'
63 let $diff_tables= master:t2, slave:t2;
64 source include/diff_tables.inc;
65 let $diff_tables= master:t4, slave:t4;
66 source include/diff_tables.inc;
67 let $diff_tables= master:t6, slave:t6;
68 source include/diff_tables.inc;
77 DROP FUNCTION f1_insert_triggered;
78 sync_slave_with_master;