Debian Codegraph @

mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test

raw
1 #
2 # Bug45677
3 # This test verifies the following two properties:
4 # P1) insert/update in an autoinc column causes statement to
5 # be logged in row format if binlog_format=mixed.
6 # P2) if binlog_format=mixed, and a trigger or function contains
7 # two or more inserts/updates in a table that has an autoinc
8 # column, then the slave should not go out of sync, even if
9 # there are concurrent transactions.
10 #
11 # Property (P1) is tested by executing an insert and an update on
12 # a table that has an autoinc column, and verifying that these
13 # statements result in row events in the binlog.
14 # Property (P2) is tested by setting up the test scenario and
15 # verifying that the tables are identical on master and slave.
16 #
17
18 source include/have_binlog_format_mixed.inc;
19 source include/have_innodb.inc;
20 source include/master-slave.inc;
21
22 --echo # Test case1: INVOKES A TRIGGER with after insert action
23 let $trigger_action = after insert;
24 source include/rpl_auto_increment_invoke_trigger.test;
25
26 --echo # Test case2: INVOKES A TRIGGER with before insert action
27 let $trigger_action = before insert;
28 source include/rpl_auto_increment_invoke_trigger.test;
29
30 --echo # Test case3: INVOKES A TRIGGER with after update action
31 let $trigger_action = after update;
32 source include/rpl_auto_increment_invoke_trigger.test;
33
34 --echo # Test case4: INVOKES A TRIGGER with before update action
35 let $trigger_action = before update;
36 source include/rpl_auto_increment_invoke_trigger.test;
37
38 --echo # Test case5: INVOKES A TRIGGER with after delete action
39 let $trigger_action = after delete;
40 source include/rpl_auto_increment_invoke_trigger.test;
41
42 --echo # Test case6: INVOKES A TRIGGER with before delete action
43 let $trigger_action = before delete;
44 source include/rpl_auto_increment_invoke_trigger.test;
45
46 --echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action
47 let $insert_action = after insert;
48 source include/rpl_autoinc_func_invokes_trigger.test;
49
50 --echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action
51 let $insert_action = before insert;
52 source include/rpl_autoinc_func_invokes_trigger.test;
53
54 --echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action
55 let $insert_action = after insert;
56 source include/rpl_auto_increment_insert_view.test;
57
58 --echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action
59 let $insert_action = before insert;
60 source include/rpl_auto_increment_insert_view.test;
61
62 --echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
63 connection master;
64 create table t1(a int) engine=innodb;
65 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
66 delimiter //;
67 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
68 BEGIN
69 INSERT INTO t2(a) values(2);
70 INSERT INTO t2(a) values(2);
71 RETURN 1;
72 END//
73 delimiter ;//
74 begin;
75 insert into t1(a) values(f1_two_inserts());
76
77 connection master1;
78 #The default autocommit is set to 1, so the statement is auto committed
79 insert into t2(a) values(4),(5);
80
81 connection master;
82 commit;
83 insert into t1(a) values(f1_two_inserts());
84 commit;
85
86 connection master;
87 --echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
88 select * from t2 ORDER BY i1;
89
90 sync_slave_with_master;
91 connection slave;
92 --echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
93 select * from t2 ORDER BY i1;
94
95 connection master;
96 drop table t1;
97 drop table t2;
98 drop function f1_two_inserts;
99 sync_slave_with_master;
100
101 --echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
102 connection master;
103 create table t1(a int) engine=innodb;
104 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
105 delimiter //;
106 CREATE FUNCTION f1_two_updates() RETURNS INTEGER
107 BEGIN
108 update t2 set a = a + 5 where b = 1;
109 update t2 set a = a + 5 where b = 2;
110 update t2 set a = a + 5 where b = 3;
111 update t2 set a = a + 5 where b = 4;
112 RETURN 1;
113 END//
114 delimiter ;//
115
116 connection master1;
117 #The default autocommit is set to 1, so the statement is auto committed
118 insert into t2(a,b) values(1,1);
119 insert into t2(a,b) values(2,2);
120 insert into t2(a,b) values(3,3);
121 insert into t2(a,b) values(4,4);
122 insert into t1(a) values(f1_two_updates());
123
124 connection master;
125 begin;
126 insert into t1(a) values(f1_two_updates());
127 commit;
128
129 connection master;
130 --echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
131 select * from t2 ORDER BY i1;
132
133 sync_slave_with_master;
134 connection slave;
135 --echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
136 select * from t2 ORDER BY i1;
137
138 connection master;
139 drop table t1;
140 drop table t2;
141 drop function f1_two_updates;
142 sync_slave_with_master;
143
144 --echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
145 connection master;
146 create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
147 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
148 begin;
149 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
150 insert into t1(a,b) values(1,1),(2,2);
151 insert into t2(a,b) values(1,1),(2,2);
152 update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
153 insert into t1(a,b) values(3,3);
154 insert into t2(a,b) values(3,3);
155 commit;
156 --echo # To verify if it works fine when these statements are not be marked as unsafe
157 source include/show_binlog_events.inc;
158
159 sync_slave_with_master;
160 --echo #Test if the results are consistent on master and slave
161 --echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
162 let $diff_tables= master:t1, slave:t1;
163 source include/diff_tables.inc;
164 let $diff_tables= master:t2, slave:t2;
165 source include/diff_tables.inc;
166
167 connection master;
168 drop table t1;
169 drop table t2;
170 sync_slave_with_master;
171
172 --echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
173 connection master;
174 CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
175 CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
176 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
177 begin;
178 INSERT INTO t1(c1) VALUES (11), (12);
179 INSERT INTO t2(c2) VALUES (13), (14);
180
181 CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
182
183 INSERT INTO v15(c1) VALUES (15),(16);
184 INSERT INTO v15(c2) VALUES (17),(18);
185
186 connection master1;
187 INSERT INTO v15(c1) VALUES (19),(20);
188 INSERT INTO v15(c2) VALUES (21),(22);
189
190 connection master;
191 INSERT INTO v15(c1) VALUES (23), (24);
192 INSERT INTO v15(c2) VALUES (25), (26);
193 commit;
194 --echo # To verify if it works fine when these statements are not be marked as unsafe
195 source include/show_binlog_events.inc;
196
197 sync_slave_with_master;
198 --echo #Test if the results are consistent on master and slave
199 --echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
200 let $diff_tables= master:t1, slave:t1;
201 source include/diff_tables.inc;
202 let $diff_tables= master:t2, slave:t2;
203 source include/diff_tables.inc;
204
205 connection master;
206 drop table t1;
207 drop table t2;
208 drop view v15;
209 sync_slave_with_master;
210
211 --source include/rpl_end.inc