Debian Codegraph @

mysql-test/suite/rpl/r/rpl_parallel_retry.result

raw
1 include/rpl_init.inc [topology=1->2]
2 *** Test retry of transactions that fail to replicate due to deadlock or similar temporary error. ***
3 connection server_1;
4 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
5 CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
6 INSERT INTO t1 VALUES (1,1);
7 SET sql_log_bin=0;
8 CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500))
9 RETURNS INT DETERMINISTIC
10 BEGIN
11 RETURN x;
12 END
13 ||
14 SET sql_log_bin=1;
15 connection server_2;
16 SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
17 include/stop_slave.inc
18 SET GLOBAL slave_parallel_threads=5;
19 include/start_slave.inc
20 SET sql_log_bin=0;
21 CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500))
22 RETURNS INT DETERMINISTIC
23 BEGIN
24 IF d1 != '' THEN
25 SET debug_sync = d1;
26 END IF;
27 IF d2 != '' THEN
28 SET debug_sync = d2;
29 END IF;
30 RETURN x;
31 END
32 ||
33 SET sql_log_bin=1;
34 include/stop_slave.inc
35 connection server_1;
36 SET gtid_seq_no = 100;
37 BEGIN;
38 INSERT INTO t1 VALUES (2,1);
39 UPDATE t1 SET b=b+1 WHERE a=1;
40 INSERT INTO t1 VALUES (3,1);
41 COMMIT;
42 SELECT * FROM t1 ORDER BY a;
43 a b
44 1 2
45 2 1
46 3 1
47 connection server_2;
48 SET @old_dbug= @@GLOBAL.debug_dbug;
49 SET GLOBAL debug_dbug="+d,rpl_parallel_simulate_temp_err_gtid_0_x_100";
50 include/start_slave.inc
51 SET GLOBAL debug_dbug=@old_dbug;
52 include/assert.inc [Performance Schema retries should match with actual retries]
53 SELECT * FROM t1 ORDER BY a;
54 a b
55 1 2
56 2 1
57 3 1
58 *** Test that double retry works when the first retry also fails with temp error ***
59 include/stop_slave.inc
60 connection server_1;
61 SET gtid_seq_no = 100;
62 SET @old_server_id = @@server_id;
63 SET server_id = 10;
64 BEGIN;
65 INSERT INTO t1 VALUES (4,1);
66 UPDATE t1 SET b=b+1 WHERE a=1;
67 INSERT INTO t1 VALUES (5,1);
68 INSERT INTO t1 VALUES (6,1);
69 COMMIT;
70 SET server_id = @old_server_id;
71 SELECT * FROM t1 ORDER BY a;
72 a b
73 1 3
74 2 1
75 3 1
76 4 1
77 5 1
78 6 1
79 connection server_2;
80 SET @old_dbug= @@GLOBAL.debug_dbug;
81 SET GLOBAL debug_dbug="+d,rpl_parallel_simulate_temp_err_gtid_0_x_100,rpl_parallel_simulate_double_temp_err_gtid_0_x_100";
82 include/start_slave.inc
83 SET GLOBAL debug_dbug=@old_dbug;
84 include/assert.inc [Performance Schema retries should match with actual retries]
85 SELECT * FROM t1 ORDER BY a;
86 a b
87 1 3
88 2 1
89 3 1
90 4 1
91 5 1
92 6 1
93 *** Test too many retries, eventually causing failure. ***
94 include/stop_slave.inc
95 connection server_1;
96 SET gtid_seq_no = 100;
97 SET @old_server_id = @@server_id;
98 SET server_id = 11;
99 BEGIN;
100 INSERT INTO t1 VALUES (7,1);
101 UPDATE t1 SET b=b+1 WHERE a=1;
102 INSERT INTO t1 VALUES (8,1);
103 INSERT INTO t1 VALUES (9,1);
104 COMMIT;
105 SET server_id = @old_server_id;
106 SELECT * FROM t1 ORDER BY a;
107 a b
108 1 4
109 2 1
110 3 1
111 4 1
112 5 1
113 6 1
114 7 1
115 8 1
116 9 1
117 connection server_2;
118 SET sql_log_bin=0;
119 CALL mtr.add_suppression("Slave worker thread retried transaction 10 time\\(s\\) in vain, giving up");
120 CALL mtr.add_suppression("Slave: Deadlock found when trying to get lock; try restarting transaction");
121 CALL mtr.add_suppression("Slave worker thread retried transaction .* in vain, giving up");
122 SET sql_log_bin=1;
123 SET @old_dbug= @@GLOBAL.debug_dbug;
124 SET GLOBAL debug_dbug="+d,rpl_parallel_simulate_temp_err_gtid_0_x_100,rpl_parallel_simulate_infinite_temp_err_gtid_0_x_100";
125 START SLAVE;
126 include/wait_for_slave_sql_error.inc [errno=1213]
127 SET GLOBAL debug_dbug=@old_dbug;
128 retries
129 10
130 include/assert.inc [Performance Schema retries should match with actual retries]
131 SELECT * FROM t1 ORDER BY a;
132 a b
133 1 3
134 2 1
135 3 1
136 4 1
137 5 1
138 6 1
139 STOP SLAVE IO_THREAD;
140 include/start_slave.inc
141 SELECT * FROM t1 ORDER BY a;
142 a b
143 1 4
144 2 1
145 3 1
146 4 1
147 5 1
148 6 1
149 7 1
150 8 1
151 9 1
152 *** Test retry of event group that spans multiple relay log files. ***
153 connection server_1;
154 CREATE TABLE t2 (a int PRIMARY KEY, b BLOB) ENGINE=InnoDB;
155 INSERT INTO t2 VALUES (1,"Hulubullu");
156 connection server_2;
157 include/stop_slave.inc
158 SET @old_max= @@GLOBAL.max_relay_log_size;
159 SET GLOBAL max_relay_log_size=4096;
160 connection server_1;
161 SET gtid_seq_no = 100;
162 SET @old_server_id = @@server_id;
163 SET server_id = 12;
164 BEGIN;
165 INSERT INTO t1 VALUES (10, 4);
166 COMMIT;
167 SET server_id = @old_server_id;
168 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
169 a b
170 10 4
171 SELECT a, LENGTH(b) FROM t2 ORDER BY a;
172 a LENGTH(b)
173 1 9
174 2 5006
175 3 5012
176 connection server_2;
177 SET @old_dbug= @@GLOBAL.debug_dbug;
178 SET GLOBAL debug_dbug="+d,rpl_parallel_simulate_temp_err_gtid_0_x_100";
179 include/start_slave.inc
180 SET GLOBAL debug_dbug=@old_dbug;
181 include/assert.inc [Performance Schema retries should match with actual retries]
182 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
183 a b
184 10 4
185 SELECT a, LENGTH(b) FROM t2 ORDER BY a;
186 a LENGTH(b)
187 1 9
188 2 5006
189 3 5012
190 connection server_1;
191 INSERT INTO t1 VALUES (11,11);
192 connection server_2;
193 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
194 a b
195 10 4
196 11 11
197 SELECT a, LENGTH(b) FROM t2 ORDER BY a;
198 a LENGTH(b)
199 1 9
200 2 5006
201 3 5012
202 4 5000
203 SET GLOBAL max_relay_log_size=@old_max;
204 *** MDEV-7065: Incorrect relay log position in parallel replication after retry of transaction ***
205 connection server_2;
206 include/stop_slave.inc
207 connection server_1;
208 BEGIN;
209 INSERT INTO t1 VALUES (100, 0);
210 INSERT INTO t1 VALUES (101, 0);
211 INSERT INTO t1 VALUES (102, 0);
212 INSERT INTO t1 VALUES (103, 0);
213 COMMIT;
214 SELECT * FROM t1 WHERE a >= 100 ORDER BY a;
215 a b
216 100 0
217 101 0
218 102 0
219 103 0
220 connection server_2;
221 SET @old_dbug= @@GLOBAL.debug_dbug;
222 SET GLOBAL debug_dbug="+d,rpl_parallel_simulate_temp_err_xid";
223 include/start_slave.inc
224 SET GLOBAL debug_dbug=@old_dbug;
225 include/assert.inc [Performance Schema retries should match with actual retries]
226 SELECT * FROM t1 WHERE a >= 100 ORDER BY a;
227 a b
228 100 0
229 101 0
230 102 0
231 103 0
232 include/stop_slave_sql.inc
233 connection server_1;
234 INSERT INTO t1 VALUES (104, 1);
235 INSERT INTO t1 VALUES (105, 1);
236 INSERT INTO t1 VALUES (106, 1);
237 INSERT INTO t1 VALUES (107, 1);
238 INSERT INTO t1 VALUES (108, 1);
239 INSERT INTO t1 VALUES (109, 1);
240 connection server_2;
241 include/start_slave.inc
242 SELECT * FROM t1 WHERE a >= 100 ORDER BY a;
243 a b
244 100 0
245 101 0
246 102 0
247 103 0
248 104 1
249 105 1
250 106 1
251 107 1
252 108 1
253 109 1
254 *** MDEV-6917: Parallel replication: "Commit failed due to failure of an earlier commit on which this one depends", but no prior failure seen **
255 connection server_1;
256 CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY b_idx(b)) ENGINE=InnoDB;
257 INSERT INTO t3 VALUES (1,NULL), (2,2), (3,NULL), (4,4), (5, NULL), (6, 6);
258 CREATE TABLE t4 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
259 INSERT INTO t4 VALUES(100, 100);
260 SET @old_format= @@SESSION.binlog_format;
261 SET binlog_format='statement';
262 connection server_2;
263 include/stop_slave.inc
264 CHANGE MASTER TO master_use_gtid=no;
265 connection server_1;
266 connect con1,127.0.0.1,root,,test,$SERVER_MYPORT_1,;
267 SET @old_format= @@SESSION.binlog_format;
268 SET binlog_format='statement';
269 BEGIN;
270 INSERT INTO t4 VALUES (10, foo(1, 'before_execute_sql_command WAIT_FOR t1_start', ''));
271 UPDATE t3 SET b=NULL WHERE a=6;
272 SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued1 WAIT_FOR master_cont1';
273 COMMIT;
274 connection server_1;
275 SET debug_sync='now WAIT_FOR master_queued1';
276 connect con2,127.0.0.1,root,,test,$SERVER_MYPORT_1,;
277 SET @old_format= @@SESSION.binlog_format;
278 SET binlog_format='statement';
279 BEGIN;
280 INSERT INTO t4 VALUES (20, foo(2, 'group_commit_waiting_for_prior SIGNAL t2_waiting', ''));
281 DELETE FROM t3 WHERE b <= 3;
282 SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2';
283 COMMIT;
284 connection server_1;
285 SET debug_sync='now WAIT_FOR master_queued2';
286 connect con3,127.0.0.1,root,,test,$SERVER_MYPORT_1,;
287 SET @old_format= @@SESSION.binlog_format;
288 SET binlog_format='statement';
289 BEGIN;
290 INSERT INTO t4 VALUES (30, foo(3, 'before_execute_sql_command WAIT_FOR t3_start', 'group_commit_waiting_for_prior SIGNAL t3_waiting'));
291 INSERT INTO t3 VALUES (7,7);
292 SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued3';
293 COMMIT;
294 connection server_1;
295 SET debug_sync='now WAIT_FOR master_queued3';
296 SET debug_sync='now SIGNAL master_cont1';
297 connection con1;
298 SET binlog_format=@old_format;
299 connection con2;
300 SET binlog_format=@old_format;
301 connection con3;
302 SET debug_sync='RESET';
303 SET binlog_format=@old_format;
304 connection server_1;
305 SELECT * FROM t3 ORDER BY a;
306 a b
307 1 NULL
308 3 NULL
309 4 4
310 5 NULL
311 6 NULL
312 7 7
313 connection server_2;
314 SET @old_dbug=@@GLOBAL.debug_dbug;
315 SET GLOBAL debug_dbug="+d,thd_need_ordering_with_force";
316 include/start_slave.inc
317 SET debug_sync='now WAIT_FOR t2_waiting';
318 SET debug_sync='now SIGNAL t3_start';
319 SET debug_sync='now WAIT_FOR t3_waiting';
320 SET debug_sync='now SIGNAL t1_start';
321 SET GLOBAL debug_dbug=@old_dbug;
322 SET debug_sync='RESET';
323 include/assert.inc [Performance Schema retries should match with actual retries]
324 SELECT * FROM t3 ORDER BY a;
325 a b
326 1 NULL
327 3 NULL
328 4 4
329 5 NULL
330 6 NULL
331 7 7
332 connection server_1;
333 SET binlog_format=@old_format;
334 connection server_2;
335 include/stop_slave.inc
336 SET GLOBAL slave_parallel_threads=@old_parallel_threads;
337 include/start_slave.inc
338 connection server_1;
339 DROP TABLE t1, t2, t3, t4;
340 DROP function foo;
341 connection server_2;
342 connection server_2;
343 include/stop_slave.inc
344 SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
345 SET GLOBAL slave_parallel_threads=4;
346 connection server_1;
347 CREATE TABLE t1 (a INT, b VARCHAR(123)) ENGINE=InnoDB;
348 INSERT INTO t1 VALUES(1, 'asdf');
349 UPDATE t1 SET b='zxf1' WHERE a=1;
350 UPDATE t1 SET b='\n' WHERE a=1;
351 connection server_2;
352 SET @old_dbug=@@GLOBAL.debug_dbug;
353 SET GLOBAL debug_dbug="+d,write_row_inject_sleep_before_ha_write_row";
354 include/start_slave.inc
355 connection server_1;
356 connection server_2;
357 connection server_1;
358 DROP TABLE t1;
359 connection server_2;
360 include/stop_slave.inc
361 SET GLOBAL debug_dbug=@old_dbug;
362 SET GLOBAL slave_parallel_threads=@old_parallel_threads;
363 include/start_slave.inc
364 connection server_1;
365 CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
366 INSERT INTO t1 VALUES(100, 100);
367 connection server_2;
368 include/stop_slave.inc
369 SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
370 SET @@GLOBAL.slave_parallel_threads=5;
371 SET @old_parallel_mode=@@GLOBAL.slave_parallel_mode;
372 SET @@GLOBAL.slave_parallel_mode='aggressive';
373 SET @old_lock_wait_timeout=@@GLOBAL.innodb_lock_wait_timeout;
374 SET @@GLOBAL.innodb_lock_wait_timeout=2;
375 SET @old_slave_transaction_retries=@@GLOBAL.slave_transaction_retries;
376 SET @@GLOBAL.slave_transaction_retries=1;
377 # Spoilers on the slave side causing temporary errors
378 connect spoiler_21,127.0.0.1,root,,test,$SLAVE_MYPORT;
379 BEGIN;
380 INSERT INTO t1 SET a=1,b=2;
381 connect spoiler_22,127.0.0.1,root,,test,$SLAVE_MYPORT;
382 BEGIN;
383 INSERT INTO t1 SET a=2,b=2;
384 # Master payload
385 connection server_1;
386 SET @@SESSION.GTID_SEQ_NO=1000;
387 INSERT INTO t1 SET a=1,b=1;
388 SET @@SESSION.GTID_SEQ_NO=1001;
389 INSERT INTO t1 SET a=2,b=1;
390 # Start slave whose both appliers is destined to being blocked
391 connection server_2;
392 SET @old_dbug= @@GLOBAL.debug_dbug;
393 SET @@GLOBAL.debug_dbug="+d,rpl_parallel_simulate_wait_at_retry";
394 include/start_slave.inc
395 # Make sure both workers are waiting at their sync points
396 # Signal to the 1st to proceed after it has reached termination state
397 SET @@DEBUG_SYNC='now SIGNAL proceed_by_1000';
398 connection spoiler_21;
399 ROLLBACK;
400 # Release the 2nd worker to proceed
401 connection spoiler_22;
402 ROLLBACK;
403 connection server_2;
404 SET @@DEBUG_SYNC='now SIGNAL proceed_by_1001';
405 # observe how it all ends
406 # Wait for the workers to go home and check the result of applying
407 # which is OK
408 connection server_2;
409 include/stop_slave.inc
410 SET @@GLOBAL.slave_parallel_threads=@old_parallel_threads;
411 SET @@GLOBAL.slave_parallel_mode=@old_parallel_mode;
412 SET @@GLOBAL.innodb_lock_wait_timeout=@old_lock_wait_timeout;
413 SET @@GLOBAL.slave_transaction_retries=@old_slave_transaction_retries;
414 SET @@GLOBAL.debug_dbug=@old_dbug;
415 SET debug_sync='RESET';
416 include/start_slave.inc
417 connection server_1;
418 DROP TABLE t1;
419 connection server_2;
420 include/rpl_end.inc