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