Debian Codegraph @

mysql-test/suite/multi_source/gtid_ignore_duplicates.test

raw
1 --source include/not_embedded.inc
2 --source include/have_innodb.inc
3 --source include/have_debug.inc
4
5 --disable_query_log
6 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
7 --enable_query_log
8
9 --echo *** Test all-to-all replication with --gtid-ignore-duplicates ***
10
11 --connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1)
12 --connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2)
13 --connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3)
14 --connect (server_4,127.0.0.1,root,,,$SERVER_MYPORT_4)
15
16 # Setup A <-> B, B <-> C, C <-> A, and A -> D.
17
18 --connection server_1
19 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
20 SET GLOBAL slave_parallel_threads=5;
21 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
22 SET GLOBAL gtid_ignore_duplicates=1;
23 SET GLOBAL gtid_domain_id= 1;
24 SET SESSION gtid_domain_id= 1;
25 --replace_result $SERVER_MYPORT_2 MYPORT_2
26 eval CHANGE MASTER 'b2a' TO master_port=$SERVER_MYPORT_2, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
27 --replace_result $SERVER_MYPORT_3 MYPORT_3
28 eval CHANGE MASTER 'c2a' TO master_port=$SERVER_MYPORT_3, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
29 set default_master_connection = 'b2a';
30 START SLAVE;
31 --source include/wait_for_slave_to_start.inc
32 set default_master_connection = 'c2a';
33 START SLAVE;
34 --source include/wait_for_slave_to_start.inc
35 set default_master_connection = '';
36
37 --connection server_2
38 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
39 SET GLOBAL slave_parallel_threads=5;
40 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
41 SET GLOBAL gtid_ignore_duplicates=1;
42 SET GLOBAL gtid_domain_id= 2;
43 SET SESSION gtid_domain_id= 2;
44 --replace_result $SERVER_MYPORT_1 MYPORT_1
45 eval CHANGE MASTER 'a2b' TO master_port=$SERVER_MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
46 --replace_result $SERVER_MYPORT_3 MYPORT_3
47 eval CHANGE MASTER 'c2b' TO master_port=$SERVER_MYPORT_3, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
48 set default_master_connection = 'a2b';
49 START SLAVE;
50 --source include/wait_for_slave_to_start.inc
51 set default_master_connection = 'c2b';
52 START SLAVE;
53 --source include/wait_for_slave_to_start.inc
54 set default_master_connection = '';
55
56 --connection server_3
57 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
58 SET GLOBAL slave_parallel_threads=5;
59 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
60 SET GLOBAL gtid_ignore_duplicates=1;
61 SET GLOBAL gtid_domain_id= 3;
62 SET SESSION gtid_domain_id= 3;
63 --replace_result $SERVER_MYPORT_1 MYPORT_1
64 eval CHANGE MASTER 'a2c' TO master_port=$SERVER_MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
65 --replace_result $SERVER_MYPORT_2 MYPORT_2
66 eval CHANGE MASTER 'b2c' TO master_port=$SERVER_MYPORT_2, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
67 set default_master_connection = 'a2c';
68 START SLAVE;
69 --source include/wait_for_slave_to_start.inc
70 set default_master_connection = 'b2c';
71 START SLAVE;
72 --source include/wait_for_slave_to_start.inc
73 set default_master_connection = '';
74
75 --connection server_4
76 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
77 SET GLOBAL slave_parallel_threads=5;
78 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
79 SET GLOBAL gtid_ignore_duplicates=1;
80 SET GLOBAL gtid_domain_id= 1;
81 SET SESSION gtid_domain_id= 1;
82 --replace_result $SERVER_MYPORT_1 MYPORT_1
83 eval CHANGE MASTER 'a2d' TO master_port=$SERVER_MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
84 set default_master_connection = 'a2d';
85 START SLAVE;
86 --source include/wait_for_slave_to_start.inc
87 set default_master_connection = '';
88
89
90 --connection server_1
91 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
92 CALL mtr.add_suppression("This change will not take full effect until all SQL threads have been restarted");
93 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
94 INSERT INTO t1 VALUES (1);
95 BEGIN;
96 INSERT INTO t1 VALUES (2);
97 INSERT INTO t1 VALUES (3);
98 COMMIT;
99 INSERT INTO t1 VALUES (4), (5);
100 INSERT INTO t1 VALUES (6);
101
102 --source include/save_master_gtid.inc
103
104 --connection server_2
105 --source include/sync_with_master_gtid.inc
106 SELECT * FROM t1 ORDER BY a;
107
108 --connection server_3
109 --source include/sync_with_master_gtid.inc
110 SELECT * FROM t1 ORDER BY a;
111
112 --connection server_4
113 --source include/sync_with_master_gtid.inc
114 SELECT * FROM t1 ORDER BY a;
115
116 --connection server_1
117 --source include/sync_with_master_gtid.inc
118 SELECT * FROM t1 ORDER BY a;
119
120 # Test that we can connect at a GTID position that has not yet reached
121 # that master server.
122 # We stop the connections C->B and A->B, create an event on C, Check that
123 # the event has reached A (but not B). Then let A stop and re-connect to
124 # B, which will connect at the new event, which is in the future for B.
125
126 --connection server_3
127 INSERT INTO t1 VALUES (10);
128 --source include/save_master_gtid.inc
129
130 --connection server_2
131 --source include/sync_with_master_gtid.inc
132 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
133 STOP SLAVE "c2b";
134 SET default_master_connection = "c2b";
135 --source include/wait_for_slave_to_stop.inc
136 STOP SLAVE "a2b";
137 SET default_master_connection = "a2b";
138 --source include/wait_for_slave_to_stop.inc
139
140 --connection server_3
141 INSERT INTO t1 VALUES (11);
142 --source include/save_master_gtid.inc
143
144 --connection server_1
145 --source include/sync_with_master_gtid.inc
146 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
147 SET default_master_connection = "b2a";
148 STOP SLAVE;
149 --source include/wait_for_slave_to_stop.inc
150
151 --connection server_2
152 INSERT INTO t1 VALUES (12);
153 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
154 --source include/save_master_gtid.inc
155
156 --connection server_1
157 START SLAVE "b2a";
158 SET default_master_connection = "b2a";
159 --source include/wait_for_slave_to_start.inc
160 --source include/sync_with_master_gtid.inc
161 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
162
163 --connection server_2
164 START SLAVE "c2b";
165 SET default_master_connection = "c2b";
166 --source include/wait_for_slave_to_start.inc
167 START SLAVE "a2b";
168 SET default_master_connection = "a2b";
169 --source include/wait_for_slave_to_start.inc
170
171 --connection server_1
172 --source include/save_master_gtid.inc
173
174 --connection server_2
175 --source include/sync_with_master_gtid.inc
176 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
177
178
179 --echo *** MDEV-33475: --gtid-ignore-duplicate can double-apply event in case of parallel replication retry
180
181 # Create a bunch of transactions that will cause conflicts and retries.
182 # The bug was that the retry code was not handling the --gtid-ignore-duplicates
183 # option, so events could be doubly-applied.
184
185 --connection server_2
186 STOP SLAVE "c2b";
187 SET default_master_connection = "c2b";
188 --source include/wait_for_slave_to_stop.inc
189 STOP SLAVE "a2b";
190 SET default_master_connection = "a2b";
191 --source include/wait_for_slave_to_stop.inc
192
193 --connection server_1
194 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
195 BEGIN;
196 --let $i= 0
197 while ($i < 10) {
198 eval INSERT INTO t2 VALUES ($i, 0);
199 inc $i;
200 }
201 COMMIT;
202
203 --let $i= 0
204 while ($i < 10) {
205 BEGIN;
206 eval INSERT INTO t2 VALUES ($i+10, 100);
207 eval UPDATE t2 SET b=$i WHERE a<10;
208 eval INSERT INTO t2 VALUES ($i+20, 200);
209 COMMIT;
210 inc $i;
211 }
212
213 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
214 --source include/save_master_gtid.inc
215
216 --connection server_2
217 SET @old_mode= @@GLOBAL.slave_parallel_mode;
218 SET GLOBAL slave_parallel_mode=aggressive;
219 SET default_master_connection = "a2b";
220 START SLAVE;
221 --source include/wait_for_slave_to_start.inc
222 SET default_master_connection = "c2b";
223 START SLAVE;
224 --source include/wait_for_slave_to_start.inc
225
226 --source include/sync_with_master_gtid.inc
227 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
228
229 --connection server_3
230 --source include/sync_with_master_gtid.inc
231 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
232
233 --connection server_4
234 --source include/sync_with_master_gtid.inc
235 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
236
237
238 --echo *** Test also with not using parallel replication.
239
240 --connection server_1
241 SET default_master_connection = "b2a";
242 STOP SLAVE;
243 --source include/wait_for_slave_to_stop.inc
244 SET default_master_connection = "c2a";
245 STOP SLAVE;
246 --source include/wait_for_slave_to_stop.inc
247 SET GLOBAL slave_parallel_threads=0;
248 SET default_master_connection = "b2a";
249 START SLAVE;
250 --source include/wait_for_slave_to_start.inc
251 SET default_master_connection = "c2a";
252 START SLAVE;
253 --source include/wait_for_slave_to_start.inc
254
255
256 --connection server_2
257 SET default_master_connection = "a2b";
258 STOP SLAVE;
259 --source include/wait_for_slave_to_stop.inc
260 SET default_master_connection = "c2b";
261 STOP SLAVE;
262 --source include/wait_for_slave_to_stop.inc
263 SET GLOBAL slave_parallel_threads=0;
264 SET default_master_connection = "a2b";
265 START SLAVE;
266 --source include/wait_for_slave_to_start.inc
267 SET default_master_connection = "c2b";
268 START SLAVE;
269 --source include/wait_for_slave_to_start.inc
270
271
272 --connection server_3
273 SET default_master_connection = "a2c";
274 STOP SLAVE;
275 --source include/wait_for_slave_to_stop.inc
276 SET default_master_connection = "b2c";
277 STOP SLAVE;
278 --source include/wait_for_slave_to_stop.inc
279 SET GLOBAL slave_parallel_threads=0;
280 SET default_master_connection = "a2c";
281 START SLAVE;
282 --source include/wait_for_slave_to_start.inc
283 SET default_master_connection = "b2c";
284 START SLAVE;
285 --source include/wait_for_slave_to_start.inc
286
287
288 --connection server_4
289 SET default_master_connection = "a2d";
290 STOP SLAVE;
291 --source include/wait_for_slave_to_stop.inc
292 SET GLOBAL slave_parallel_threads=0;
293 SET default_master_connection = "a2d";
294 START SLAVE;
295 --source include/wait_for_slave_to_start.inc
296
297
298 --connection server_2
299 INSERT INTO t1 VALUES (21);
300 BEGIN;
301 INSERT INTO t1 VALUES (22);
302 INSERT INTO t1 VALUES (23);
303 COMMIT;
304 INSERT INTO t1 VALUES (24), (25);
305 INSERT INTO t1 VALUES (26);
306
307 --source include/save_master_gtid.inc
308
309 --connection server_1
310 --source include/sync_with_master_gtid.inc
311 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
312
313 --connection server_3
314 --source include/sync_with_master_gtid.inc
315 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
316
317 --connection server_4
318 --source include/sync_with_master_gtid.inc
319 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
320
321 --connection server_2
322 --source include/sync_with_master_gtid.inc
323 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
324
325
326 --echo *** MDEV-8354: out-of-order error with --gtid-ignore-duplicates and row-based replication ***
327
328 # Have only A->C A->B initially.
329 --connection server_1
330 SET default_master_connection = "b2a";
331 STOP SLAVE;
332 --source include/wait_for_slave_to_stop.inc
333 SET default_master_connection = "c2a";
334 STOP SLAVE;
335 --source include/wait_for_slave_to_stop.inc
336
337 --connection server_2
338 SET default_master_connection = "c2b";
339 STOP SLAVE;
340 --source include/wait_for_slave_to_stop.inc
341
342 --connection server_3
343 SET default_master_connection = "b2c";
344 STOP SLAVE;
345 --source include/wait_for_slave_to_stop.inc
346 SET @old_slave_mode=@@GLOBAL.slave_exec_mode;
347 SET GLOBAL slave_exec_mode=IDEMPOTENT;
348 SET @old_strict=@@GLOBAL.gtid_strict_mode;
349 SET GLOBAL gtid_strict_mode=1;
350
351 SET @old_dbug=@@GLOBAL.debug_dbug;
352 # This will inject a small sleep that helps trigger the race. I did not manage
353 # to create a non-sleeping version with debug_sync for this; the problem is
354 # that once the bug is fixed, the race becomes impossible, so even with
355 # debug_sync at best we can check that the debug_sync times out. Which is
356 # just another way of adding a sleep.
357 #
358 # The bug was a race at this point where another multi-source connection
359 # could incorrectly re-apply the same GTID, in case of row-based replication.
360 SET GLOBAL debug_dbug="+d,inject_sleep_gtid_100_x_x";
361
362 --connection server_1
363 SET @old_domain=@@SESSION.gtid_domain_id;
364 SET @old_format=@@SESSION.binlog_format;
365 SET SESSION gtid_domain_id=100;
366 SET SESSION binlog_format='row';
367 INSERT INTO t1 VALUES (30);
368 INSERT INTO t1 VALUES (31);
369 INSERT INTO t1 VALUES (32);
370 INSERT INTO t1 VALUES (33);
371 INSERT INTO t1 VALUES (34);
372 INSERT INTO t1 VALUES (35);
373 INSERT INTO t1 VALUES (36);
374 INSERT INTO t1 VALUES (37);
375 INSERT INTO t1 VALUES (38);
376 INSERT INTO t1 VALUES (39);
377 INSERT INTO t1 VALUES (40);
378 INSERT INTO t1 VALUES (41);
379 INSERT INTO t1 VALUES (42);
380 INSERT INTO t1 VALUES (43);
381 INSERT INTO t1 VALUES (44);
382 INSERT INTO t1 VALUES (45);
383 INSERT INTO t1 VALUES (46);
384 INSERT INTO t1 VALUES (47);
385 INSERT INTO t1 VALUES (48);
386 INSERT INTO t1 VALUES (49);
387 SET SESSION gtid_domain_id=@old_domain;
388 SET SESSION binlog_format=@old_format;
389 --source include/save_master_gtid.inc
390
391 --connection server_2
392 --source include/sync_with_master_gtid.inc
393 INSERT INTO t1 VALUES (50);
394 --let $gtid=`SELECT @@last_gtid`
395 --source include/save_master_gtid.inc
396
397 --connection server_3
398 SET default_master_connection = "b2c";
399 START SLAVE;
400 --source include/wait_for_slave_to_start.inc
401 --replace_result $gtid GTID
402 eval SELECT MASTER_GTID_WAIT("$gtid", 30);
403 # The bug occurred here, the slave would get an out-of-order binlog error
404 # due to trying to re-apply the 100-x-x transaction.
405
406 # Restart stopped multi-source connections, and sync up.
407 --connection server_1
408 SET default_master_connection = "b2a";
409 START SLAVE;
410 --source include/wait_for_slave_to_start.inc
411 SET default_master_connection = "c2a";
412 START SLAVE;
413 --source include/wait_for_slave_to_start.inc
414 --source include/sync_with_master_gtid.inc
415 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
416
417 --connection server_2
418 SET default_master_connection = "c2b";
419 START SLAVE;
420 --source include/wait_for_slave_to_start.inc
421 --source include/sync_with_master_gtid.inc
422 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
423
424 --connection server_3
425 --source include/sync_with_master_gtid.inc
426 SET GLOBAL debug_dbug=@old_dbug;
427 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
428 SET GLOBAL slave_exec_mode=@old_slave_mode;
429 SET GLOBAL gtid_strict_mode=@old_strict;
430
431
432 --echo *** MDEV-8496: gtid_ignore_duplicates treats gtid_seq_no as 32-bit ***
433
434 --connection server_1
435 SET @old_domain= @@SESSION.gtid_domain_id;
436 SET SESSION gtid_domain_id=102;
437 SET SESSION gtid_seq_no=4294967294;
438 INSERT INTO t1 VALUES (60);
439 INSERT INTO t1 VALUES (61);
440 INSERT INTO t1 VALUES (62);
441 # The bug was an overflow, the seq_no value 4294967296 (2**32) was treated
442 # as 0, causing the last transaction to be ignored.
443 SET SESSION gtid_domain_id= @old_domain;
444 --source include/save_master_gtid.inc
445
446 --connection server_4
447 --source include/sync_with_master_gtid.inc
448 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
449
450 --connection server_2
451 SET default_master_connection = "c2b";
452 --source include/sync_with_master_gtid.inc
453 SET default_master_connection = "a2b";
454 --source include/sync_with_master_gtid.inc
455 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
456
457 --connection server_3
458 SET default_master_connection = "b2c";
459 --source include/sync_with_master_gtid.inc
460 SET default_master_connection = "a2c";
461 --source include/sync_with_master_gtid.inc
462 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
463
464
465
466 # Clean up.
467 --connection server_1
468 SET GLOBAL gtid_domain_id=0;
469 --sorted_result
470 STOP ALL SLAVES;
471 SET GLOBAL slave_parallel_threads= @old_parallel;
472 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
473
474 --connection server_2
475 SET GLOBAL gtid_domain_id=0;
476 --sorted_result
477 STOP ALL SLAVES;
478 SET GLOBAL slave_parallel_threads= @old_parallel;
479 SET GLOBAL slave_parallel_mode= @old_mode;
480 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
481
482 --connection server_3
483 SET GLOBAL gtid_domain_id=0;
484 --sorted_result
485 STOP ALL SLAVES;
486 SET GLOBAL slave_parallel_threads= @old_parallel;
487 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
488
489 --connection server_4
490 SET GLOBAL gtid_domain_id=0;
491 --sorted_result
492 STOP ALL SLAVES;
493 SET GLOBAL slave_parallel_threads= @old_parallel;
494 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
495
496 --connection server_1
497 DROP TABLE t1, t2;
498 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
499 --source include/reset_master_slave.inc
500 --disconnect server_1
501
502 --connection server_2
503 DROP TABLE t1, t2;
504 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
505 --source include/reset_master_slave.inc
506 --disconnect server_2
507
508 --connection server_3
509 DROP TABLE t1, t2;
510 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
511 --source include/reset_master_slave.inc
512 --disconnect server_3
513
514 --connection server_4
515 DROP TABLE t1, t2;
516 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
517 --source include/reset_master_slave.inc
518 --disconnect server_4