Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_begin_commit_rollback.test

raw
1 # ==== Purpose ====
2 #
3 # Test that replication of transaction boundary statements (BEGIN,
4 # COMMIT, and ROLLBACK) works with filters.
5 #
6 # The following properties are tested:
7 #
8 # 1. Transaction boundary statements should never be skipped, even if
9 # filtered out by the slave thread. If they were skipped, a
10 # transaction where the BEGIN is filtered out and the DML is not
11 # filtered out could be executed as a sequence of autocommitted
12 # statements.
13 #
14 # ==== Implementation ====
15 #
16 # ==== References ====
17 #
18 # BUG#43263 BEGIN skipped in some replicate-do-db cases
19 # BUG#50407 mysqlbinlog --database=X produces bad output for SAVEPOINTs
20 # BUG#55798 Slave SQL retry on transaction inserts extra data into non-transaction table
21 source include/force_myisam_default.inc;
22 source include/have_myisam.inc;
23 source include/have_binlog_format_statement.inc;
24 --source include/set_privilege_checks_user_as_system_user.inc
25 source include/master-slave.inc;
26
27 connection slave;
28 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
29 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
30
31 connection master;
32 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
33
34 SET @@session.binlog_direct_non_transactional_updates= FALSE;
35
36 CREATE DATABASE replicate_do_db;
37 CREATE DATABASE binlog_ignore_db;
38
39 USE replicate_do_db;
40
41 CREATE TABLE replicate_do_db.t1 (a INT) ENGINE=InnoDB;
42 CREATE TABLE replicate_do_db.t2 (s CHAR(255)) ENGINE=MyISAM;
43
44 --source include/sync_slave_sql_with_master.inc
45 source include/stop_slave.inc;
46 --source include/rpl_connection_master.inc
47
48 DELIMITER //;
49 CREATE PROCEDURE replicate_do_db.p1 ()
50 BEGIN
51 INSERT INTO t1 VALUES (1);
52 INSERT INTO t1 VALUES (2);
53 INSERT INTO t1 VALUES (3);
54 INSERT INTO t1 VALUES (4);
55 INSERT INTO t1 VALUES (5);
56 END//
57
58 CREATE PROCEDURE replicate_do_db.p2 ()
59 BEGIN
60 INSERT INTO t1 VALUES (6);
61 INSERT INTO t1 VALUES (7);
62 INSERT INTO t1 VALUES (8);
63 INSERT INTO t1 VALUES (9);
64 INSERT INTO t1 VALUES (10);
65 INSERT INTO t2 VALUES ('executed replicate_do_db.p2()');
66 END//
67 DELIMITER ;//
68
69 INSERT INTO replicate_do_db.t2 VALUES ('before call replicate_do_db.p1()');
70
71 # Note: the master_log_pos is set to be the position of the BEGIN + 1,
72 # so before fix of BUG#43263 if the BEGIN is ignored, then all the
73 # INSERTS in p1 will be replicated in AUTOCOMMIT=1 mode and the slave
74 # SQL thread will stop right before the first INSERT. After fix of
75 # BUG#43263, BEGIN will not be ignored by the replication db rules,
76 # and then the whole transaction will be executed before slave SQL
77 # stop.
78 --let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
79
80 USE test;
81 BEGIN;
82 CALL replicate_do_db.p1();
83 COMMIT;
84
85 # $master_pos is the position after the Gtid/Anonymous event, just
86 # before query_log_event(BEGIN).
87 --let $master_pos= query_get_value(SHOW BINLOG EVENTS FROM $master_pos LIMIT 1, End_log_pos, 1)
88 # $master_pos is one byte into the BEGIN event.
89 # This means START SLAVE UNTIL should skip the entire transaction.
90 --inc $master_pos
91
92 # The position where the following START SLAVE UNTIL should stop at
93 let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
94
95 INSERT INTO replicate_do_db.t2 VALUES ('after call replicate_do_db.p1()');
96 SELECT * FROM replicate_do_db.t1;
97 SELECT * FROM replicate_do_db.t2;
98
99 --source include/rpl_connection_slave.inc
100
101 replace_result $master_pos MASTER_POS;
102 eval START SLAVE UNTIL SOURCE_LOG_FILE='master-bin.000001', SOURCE_LOG_POS=$master_pos;
103 --source include/wait_for_slave_sql_to_stop.inc
104
105 # Test BUG#43263
106 --let $assert_cond= [SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1] = $master_end_trans_pos
107 --let $assert_text= Slave should have stopped after executing the stored procedure transaction
108 --source include/assert.inc
109
110 SELECT * from replicate_do_db.t1;
111 SELECT * from replicate_do_db.t2;
112
113 --source include/rpl_connection_master.inc
114
115 INSERT INTO replicate_do_db.t2 VALUES ('before call replicate_do_db.p2()');
116
117 # See comments above.
118 --let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
119
120 BEGIN;
121 CALL replicate_do_db.p2();
122 disable_warnings;
123 ROLLBACK;
124 enable_warnings;
125
126 --let $master_pos= query_get_value(SHOW BINLOG EVENTS FROM $master_pos LIMIT 1, End_log_pos, 1)
127 --inc $master_pos
128
129 let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
130
131 INSERT INTO replicate_do_db.t2 VALUES ('after call replicate_do_db.p2()');
132 SELECT * FROM replicate_do_db.t1;
133 SELECT * FROM replicate_do_db.t2;
134
135 --source include/rpl_connection_slave.inc
136
137 --replace_result $master_pos MASTER_POS
138 eval START SLAVE UNTIL SOURCE_LOG_FILE='master-bin.000001', SOURCE_LOG_POS=$master_pos;
139 source include/wait_for_slave_sql_to_stop.inc;
140
141 # Test BUG#43263
142 --let $assert_cond= [SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1] = $master_end_trans_pos
143 --let $assert_text= Slave should have stopped after executing the stored procedure transaction
144 --source include/assert.inc
145
146 SELECT * from replicate_do_db.t1;
147 SELECT * from replicate_do_db.t2;
148
149 START SLAVE;
150 source include/wait_for_slave_sql_to_start.inc;
151
152 --echo #
153 --echo # SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263
154 --echo # This was reported by BUG#50407
155 --source include/rpl_connection_master.inc
156 let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1);
157
158 BEGIN;
159 INSERT INTO replicate_do_db.t1 VALUES(20);
160
161 --echo #
162 --echo # Verify whether this statement is binlogged correctly
163 /*comment*/ SAVEPOINT has_comment;
164 USE replicate_do_db;
165 INSERT INTO replicate_do_db.t1 VALUES(30);
166 INSERT INTO replicate_do_db.t2 VALUES("in savepoint has_comment");
167 USE binlog_ignore_db;
168 SavePoint mixed_cases;
169 USE replicate_do_db;
170 INSERT INTO replicate_do_db.t2 VALUES("in savepoint mixed_cases");
171 INSERT INTO replicate_do_db.t1 VALUES(40);
172 USE binlog_ignore_db;
173 ROLLBACK TO mixed_cases;
174 ROLLBACK TO has_comment;
175 USE replicate_do_db;
176 INSERT INTO replicate_do_db.t2 VALUES("after rollback to");
177 INSERT INTO replicate_do_db.t1 VALUES(50);
178 USE binlog_ignore_db;
179 COMMIT;
180 source include/show_binlog_events.inc;
181 --source include/sync_slave_sql_with_master.inc
182
183 --echo [on slave]
184 --echo #
185 --echo # Verify INSERT statements in savepoints are executed, for MyISAM table
186 --echo # is not effected by ROLLBACK TO
187 SELECT * FROM replicate_do_db.t2 WHERE s LIKE '% savepoint %';
188 --echo #
189 --echo # Verify INSERT statements on the Innodb table are rolled back;
190 SELECT * FROM replicate_do_db.t1 WHERE a IN (30, 40);
191
192 --echo
193 --echo # BUG#55798 Slave SQL retry on transaction inserts extra data into
194 --echo # non-transaction table
195 --echo # ----------------------------------------------------------------
196 --echo # To verify that SQL thread does not retry a transaction which can
197 --echo # not be rolled back safely, even though only a temporary error is
198 --echo # encountered.
199 --echo
200
201 --source include/rpl_connection_master.inc
202
203 USE replicate_do_db;
204 DROP TABLE t1, t2;
205 CREATE TABLE t1(c1 INT KEY, c2 CHAR(100)) ENGINE=InnoDB;
206 CREATE TABLE t2(c1 INT) ENGINE=MyISAM;
207 CREATE TABLE t3(c1 INT) ENGINE=InnoDB;
208 INSERT INTO t1 VALUES(1, "master");
209
210 SET @@session.binlog_direct_non_transactional_updates= FALSE;
211
212 --source include/sync_slave_sql_with_master.inc
213
214 --echo # [ on slave ]
215 USE replicate_do_db;
216
217 SET @timeout_old= @@GLOBAL.innodb_lock_wait_timeout;
218 SET GLOBAL innodb_lock_wait_timeout= 1;
219
220 STOP SLAVE SQL_THREAD;
221 source include/wait_for_slave_sql_to_stop.inc;
222 START SLAVE SQL_THREAD;
223 source include/wait_for_slave_sql_to_start.inc;
224
225 --echo # Verify the SQL thread doesn't retry the transaction when one of
226 --echo # its statements has modified a non-transactional table.
227 --echo # ----------------------------------------------------------------
228 --echo
229 --echo # INSERT statement inserts a row to a non-transactional table.
230 let $statement= INSERT INTO t2 VALUES(1);
231 source extra/rpl_tests/rpl_temp_error.test;
232
233 --echo
234 --echo # INSERT ... SELECT statement inserts a row to a non-transactional table.
235 let $statement= INSERT INTO t2 SELECT 2;
236 source extra/rpl_tests/rpl_temp_error.test;
237
238 --echo
239 --echo # UPDATE statement updates a row to a non-transactional table.
240 connection master;
241 let $statement= UPDATE t2 SET c1= 3;
242 source extra/rpl_tests/rpl_temp_error.test;
243
244 --echo
245 --echo # MULTI-UPDATE statement updates a row to a non-transactional table.
246 connection master;
247 let $statement= UPDATE t2, t3 SET t2.c1=4, t3.c1= 4;
248 source extra/rpl_tests/rpl_temp_error.test;
249
250 --echo
251 --echo # DELETE statement deletes a row from a non-transactional table.
252 connection master;
253 let $statement= DELETE FROM t2;
254 source extra/rpl_tests/rpl_temp_error.test;
255
256 --echo
257 --echo # CREATE TEMPORARY TABLE statement in the transaction
258 let $statement= CREATE TEMPORARY TABLE IF NOT EXISTS temp_t(c1 INT);
259 source extra/rpl_tests/rpl_temp_error.test;
260
261 --echo
262 --echo # DROP TEMPORARY TABLE statement in the transaction
263 let $statement= DROP TEMPORARY TABLE IF EXISTS temp_t;
264 source extra/rpl_tests/rpl_temp_error.test;
265
266 --echo
267 --echo # Verify that the SQL thread doesn't retry the transaction if one
268 --echo # of the sub-statements has modified a non-transactional table.
269 --echo # ----------------------------------------------------------------
270
271 connection master;
272 --delimiter |
273
274 CREATE FUNCTION f_insert()
275 RETURNS INT
276 BEGIN
277 INSERT INTO t2 VALUES(1);
278 RETURN 2;
279 END|
280
281 CREATE FUNCTION f_insert_select()
282 RETURNS INT
283 BEGIN
284 INSERT INTO t2 SELECT 2;
285 RETURN 2;
286 END|
287
288 CREATE FUNCTION f_update()
289 RETURNS INT
290 BEGIN
291 UPDATE t2 SET c1=3;
292 RETURN 2;
293 END |
294
295 CREATE TABLE t4 (c1 INT) |
296 INSERT INTO t4 VAlUES(1),(2) |
297 CREATE FUNCTION f_multi_update()
298 RETURNS INT
299 BEGIN
300 UPDATE t2, t4 SET t2.c1=4, t4.c1= 4;
301 RETURN 2;
302 END |
303
304 CREATE FUNCTION f_delete()
305 RETURNS INT
306 BEGIN
307 DELETE FROM t2;
308 RETURN 2;
309 END |
310 --delimiter ;
311
312 --echo
313 --echo # The INSERT statement in a function inserts a row into a
314 --echo # non-transactional table.
315 let $statement= INSERT INTO t3 VALUES(f_insert());
316 source extra/rpl_tests/rpl_temp_error.test;
317
318 --echo
319 --echo # The INSERT ... SELECT statement in a function inserts a row into a
320 --echo # non-transactional table.
321 let $statement= INSERT INTO t3 VALUES(f_insert());
322 source extra/rpl_tests/rpl_temp_error.test;
323
324 --echo
325 --echo # The UPDATE statement in a function updates a row of a
326 --echo # non-transactional table.
327 connection master;
328 let $statement= INSERT INTO t3 VALUES(f_update());
329 source extra/rpl_tests/rpl_temp_error.test;
330
331 --echo
332 --echo # The MULTI-UPDATE statement in a function updates a row of a
333 --echo # non-transactional table.
334 connection master;
335 let $statement= INSERT INTO t3 VALUES(f_multi_update());
336 source extra/rpl_tests/rpl_temp_error.test;
337
338 --echo
339 --echo # The DELETE statement in a function deletes a row from a
340 --echo # non-transactional table.
341 connection master;
342 let $statement= INSERT INTO t3 VALUES(f_delete());
343 source extra/rpl_tests/rpl_temp_error.test;
344
345 SET @@global.innodb_lock_wait_timeout= @timeout_old;
346
347 --echo #
348 --echo # Clean up
349 --echo #
350 connection master;
351 DROP DATABASE replicate_do_db;
352 DROP DATABASE binlog_ignore_db;
353 --source include/rpl_end.inc