Debian Codegraph @

mysql-test/suite/multi_source/gtid_ignore_duplicates.result

raw
1 *** Test all-to-all replication with --gtid-ignore-duplicates ***
2 connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1;
3 connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2;
4 connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3;
5 connect server_4,127.0.0.1,root,,,$SERVER_MYPORT_4;
6 connection server_1;
7 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
8 SET GLOBAL slave_parallel_threads=5;
9 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
10 SET GLOBAL gtid_ignore_duplicates=1;
11 SET GLOBAL gtid_domain_id= 1;
12 SET SESSION gtid_domain_id= 1;
13 CHANGE MASTER 'b2a' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
14 CHANGE MASTER 'c2a' TO master_port=MYPORT_3, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
15 set default_master_connection = 'b2a';
16 START SLAVE;
17 include/wait_for_slave_to_start.inc
18 set default_master_connection = 'c2a';
19 START SLAVE;
20 include/wait_for_slave_to_start.inc
21 set default_master_connection = '';
22 connection server_2;
23 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
24 SET GLOBAL slave_parallel_threads=5;
25 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
26 SET GLOBAL gtid_ignore_duplicates=1;
27 SET GLOBAL gtid_domain_id= 2;
28 SET SESSION gtid_domain_id= 2;
29 CHANGE MASTER 'a2b' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
30 CHANGE MASTER 'c2b' TO master_port=MYPORT_3, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
31 set default_master_connection = 'a2b';
32 START SLAVE;
33 include/wait_for_slave_to_start.inc
34 set default_master_connection = 'c2b';
35 START SLAVE;
36 include/wait_for_slave_to_start.inc
37 set default_master_connection = '';
38 connection server_3;
39 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
40 SET GLOBAL slave_parallel_threads=5;
41 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
42 SET GLOBAL gtid_ignore_duplicates=1;
43 SET GLOBAL gtid_domain_id= 3;
44 SET SESSION gtid_domain_id= 3;
45 CHANGE MASTER 'a2c' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
46 CHANGE MASTER 'b2c' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
47 set default_master_connection = 'a2c';
48 START SLAVE;
49 include/wait_for_slave_to_start.inc
50 set default_master_connection = 'b2c';
51 START SLAVE;
52 include/wait_for_slave_to_start.inc
53 set default_master_connection = '';
54 connection server_4;
55 SET @old_parallel= @@GLOBAL.slave_parallel_threads;
56 SET GLOBAL slave_parallel_threads=5;
57 SET @old_ignore_duplicates= @@GLOBAL.gtid_ignore_duplicates;
58 SET GLOBAL gtid_ignore_duplicates=1;
59 SET GLOBAL gtid_domain_id= 1;
60 SET SESSION gtid_domain_id= 1;
61 CHANGE MASTER 'a2d' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos;
62 set default_master_connection = 'a2d';
63 START SLAVE;
64 include/wait_for_slave_to_start.inc
65 set default_master_connection = '';
66 connection server_1;
67 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
68 CALL mtr.add_suppression("This change will not take full effect until all SQL threads have been restarted");
69 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
70 INSERT INTO t1 VALUES (1);
71 BEGIN;
72 INSERT INTO t1 VALUES (2);
73 INSERT INTO t1 VALUES (3);
74 COMMIT;
75 INSERT INTO t1 VALUES (4), (5);
76 INSERT INTO t1 VALUES (6);
77 include/save_master_gtid.inc
78 connection server_2;
79 include/sync_with_master_gtid.inc
80 SELECT * FROM t1 ORDER BY a;
81 a
82 1
83 2
84 3
85 4
86 5
87 6
88 connection server_3;
89 include/sync_with_master_gtid.inc
90 SELECT * FROM t1 ORDER BY a;
91 a
92 1
93 2
94 3
95 4
96 5
97 6
98 connection server_4;
99 include/sync_with_master_gtid.inc
100 SELECT * FROM t1 ORDER BY a;
101 a
102 1
103 2
104 3
105 4
106 5
107 6
108 connection server_1;
109 include/sync_with_master_gtid.inc
110 SELECT * FROM t1 ORDER BY a;
111 a
112 1
113 2
114 3
115 4
116 5
117 6
118 connection server_3;
119 INSERT INTO t1 VALUES (10);
120 include/save_master_gtid.inc
121 connection server_2;
122 include/sync_with_master_gtid.inc
123 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
124 a
125 10
126 STOP SLAVE "c2b";
127 SET default_master_connection = "c2b";
128 include/wait_for_slave_to_stop.inc
129 STOP SLAVE "a2b";
130 SET default_master_connection = "a2b";
131 include/wait_for_slave_to_stop.inc
132 connection server_3;
133 INSERT INTO t1 VALUES (11);
134 include/save_master_gtid.inc
135 connection server_1;
136 include/sync_with_master_gtid.inc
137 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
138 a
139 10
140 11
141 SET default_master_connection = "b2a";
142 STOP SLAVE;
143 include/wait_for_slave_to_stop.inc
144 connection server_2;
145 INSERT INTO t1 VALUES (12);
146 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
147 a
148 10
149 12
150 include/save_master_gtid.inc
151 connection server_1;
152 START SLAVE "b2a";
153 SET default_master_connection = "b2a";
154 include/wait_for_slave_to_start.inc
155 include/sync_with_master_gtid.inc
156 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
157 a
158 10
159 11
160 12
161 connection server_2;
162 START SLAVE "c2b";
163 SET default_master_connection = "c2b";
164 include/wait_for_slave_to_start.inc
165 START SLAVE "a2b";
166 SET default_master_connection = "a2b";
167 include/wait_for_slave_to_start.inc
168 connection server_1;
169 include/save_master_gtid.inc
170 connection server_2;
171 include/sync_with_master_gtid.inc
172 SELECT * FROM t1 WHERE a >= 10 ORDER BY a;
173 a
174 10
175 11
176 12
177 *** MDEV-33475: --gtid-ignore-duplicate can double-apply event in case of parallel replication retry
178 connection server_2;
179 STOP SLAVE "c2b";
180 SET default_master_connection = "c2b";
181 include/wait_for_slave_to_stop.inc
182 STOP SLAVE "a2b";
183 SET default_master_connection = "a2b";
184 include/wait_for_slave_to_stop.inc
185 connection server_1;
186 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
187 BEGIN;
188 INSERT INTO t2 VALUES (0, 0);
189 INSERT INTO t2 VALUES (1, 0);
190 INSERT INTO t2 VALUES (2, 0);
191 INSERT INTO t2 VALUES (3, 0);
192 INSERT INTO t2 VALUES (4, 0);
193 INSERT INTO t2 VALUES (5, 0);
194 INSERT INTO t2 VALUES (6, 0);
195 INSERT INTO t2 VALUES (7, 0);
196 INSERT INTO t2 VALUES (8, 0);
197 INSERT INTO t2 VALUES (9, 0);
198 COMMIT;
199 BEGIN;
200 INSERT INTO t2 VALUES (0+10, 100);
201 UPDATE t2 SET b=0 WHERE a<10;
202 INSERT INTO t2 VALUES (0+20, 200);
203 COMMIT;
204 BEGIN;
205 INSERT INTO t2 VALUES (1+10, 100);
206 UPDATE t2 SET b=1 WHERE a<10;
207 INSERT INTO t2 VALUES (1+20, 200);
208 COMMIT;
209 BEGIN;
210 INSERT INTO t2 VALUES (2+10, 100);
211 UPDATE t2 SET b=2 WHERE a<10;
212 INSERT INTO t2 VALUES (2+20, 200);
213 COMMIT;
214 BEGIN;
215 INSERT INTO t2 VALUES (3+10, 100);
216 UPDATE t2 SET b=3 WHERE a<10;
217 INSERT INTO t2 VALUES (3+20, 200);
218 COMMIT;
219 BEGIN;
220 INSERT INTO t2 VALUES (4+10, 100);
221 UPDATE t2 SET b=4 WHERE a<10;
222 INSERT INTO t2 VALUES (4+20, 200);
223 COMMIT;
224 BEGIN;
225 INSERT INTO t2 VALUES (5+10, 100);
226 UPDATE t2 SET b=5 WHERE a<10;
227 INSERT INTO t2 VALUES (5+20, 200);
228 COMMIT;
229 BEGIN;
230 INSERT INTO t2 VALUES (6+10, 100);
231 UPDATE t2 SET b=6 WHERE a<10;
232 INSERT INTO t2 VALUES (6+20, 200);
233 COMMIT;
234 BEGIN;
235 INSERT INTO t2 VALUES (7+10, 100);
236 UPDATE t2 SET b=7 WHERE a<10;
237 INSERT INTO t2 VALUES (7+20, 200);
238 COMMIT;
239 BEGIN;
240 INSERT INTO t2 VALUES (8+10, 100);
241 UPDATE t2 SET b=8 WHERE a<10;
242 INSERT INTO t2 VALUES (8+20, 200);
243 COMMIT;
244 BEGIN;
245 INSERT INTO t2 VALUES (9+10, 100);
246 UPDATE t2 SET b=9 WHERE a<10;
247 INSERT INTO t2 VALUES (9+20, 200);
248 COMMIT;
249 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
250 COUNT(*) SUM(a) SUM(b)
251 30 435 3090
252 include/save_master_gtid.inc
253 connection server_2;
254 SET @old_mode= @@GLOBAL.slave_parallel_mode;
255 SET GLOBAL slave_parallel_mode=aggressive;
256 SET default_master_connection = "a2b";
257 START SLAVE;
258 include/wait_for_slave_to_start.inc
259 SET default_master_connection = "c2b";
260 START SLAVE;
261 include/wait_for_slave_to_start.inc
262 include/sync_with_master_gtid.inc
263 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
264 COUNT(*) SUM(a) SUM(b)
265 30 435 3090
266 connection server_3;
267 include/sync_with_master_gtid.inc
268 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
269 COUNT(*) SUM(a) SUM(b)
270 30 435 3090
271 connection server_4;
272 include/sync_with_master_gtid.inc
273 SELECT COUNT(*), SUM(a), SUM(b) FROM t2;
274 COUNT(*) SUM(a) SUM(b)
275 30 435 3090
276 *** Test also with not using parallel replication.
277 connection server_1;
278 SET default_master_connection = "b2a";
279 STOP SLAVE;
280 include/wait_for_slave_to_stop.inc
281 SET default_master_connection = "c2a";
282 STOP SLAVE;
283 include/wait_for_slave_to_stop.inc
284 SET GLOBAL slave_parallel_threads=0;
285 SET default_master_connection = "b2a";
286 START SLAVE;
287 include/wait_for_slave_to_start.inc
288 SET default_master_connection = "c2a";
289 START SLAVE;
290 include/wait_for_slave_to_start.inc
291 connection server_2;
292 SET default_master_connection = "a2b";
293 STOP SLAVE;
294 include/wait_for_slave_to_stop.inc
295 SET default_master_connection = "c2b";
296 STOP SLAVE;
297 include/wait_for_slave_to_stop.inc
298 SET GLOBAL slave_parallel_threads=0;
299 SET default_master_connection = "a2b";
300 START SLAVE;
301 include/wait_for_slave_to_start.inc
302 SET default_master_connection = "c2b";
303 START SLAVE;
304 include/wait_for_slave_to_start.inc
305 connection server_3;
306 SET default_master_connection = "a2c";
307 STOP SLAVE;
308 include/wait_for_slave_to_stop.inc
309 SET default_master_connection = "b2c";
310 STOP SLAVE;
311 include/wait_for_slave_to_stop.inc
312 SET GLOBAL slave_parallel_threads=0;
313 SET default_master_connection = "a2c";
314 START SLAVE;
315 include/wait_for_slave_to_start.inc
316 SET default_master_connection = "b2c";
317 START SLAVE;
318 include/wait_for_slave_to_start.inc
319 connection server_4;
320 SET default_master_connection = "a2d";
321 STOP SLAVE;
322 include/wait_for_slave_to_stop.inc
323 SET GLOBAL slave_parallel_threads=0;
324 SET default_master_connection = "a2d";
325 START SLAVE;
326 include/wait_for_slave_to_start.inc
327 connection server_2;
328 INSERT INTO t1 VALUES (21);
329 BEGIN;
330 INSERT INTO t1 VALUES (22);
331 INSERT INTO t1 VALUES (23);
332 COMMIT;
333 INSERT INTO t1 VALUES (24), (25);
334 INSERT INTO t1 VALUES (26);
335 include/save_master_gtid.inc
336 connection server_1;
337 include/sync_with_master_gtid.inc
338 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
339 a
340 21
341 22
342 23
343 24
344 25
345 26
346 connection server_3;
347 include/sync_with_master_gtid.inc
348 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
349 a
350 21
351 22
352 23
353 24
354 25
355 26
356 connection server_4;
357 include/sync_with_master_gtid.inc
358 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
359 a
360 21
361 22
362 23
363 24
364 25
365 26
366 connection server_2;
367 include/sync_with_master_gtid.inc
368 SELECT * FROM t1 WHERE a >= 20 ORDER BY a;
369 a
370 21
371 22
372 23
373 24
374 25
375 26
376 *** MDEV-8354: out-of-order error with --gtid-ignore-duplicates and row-based replication ***
377 connection server_1;
378 SET default_master_connection = "b2a";
379 STOP SLAVE;
380 include/wait_for_slave_to_stop.inc
381 SET default_master_connection = "c2a";
382 STOP SLAVE;
383 include/wait_for_slave_to_stop.inc
384 connection server_2;
385 SET default_master_connection = "c2b";
386 STOP SLAVE;
387 include/wait_for_slave_to_stop.inc
388 connection server_3;
389 SET default_master_connection = "b2c";
390 STOP SLAVE;
391 include/wait_for_slave_to_stop.inc
392 SET @old_slave_mode=@@GLOBAL.slave_exec_mode;
393 SET GLOBAL slave_exec_mode=IDEMPOTENT;
394 SET @old_strict=@@GLOBAL.gtid_strict_mode;
395 SET GLOBAL gtid_strict_mode=1;
396 SET @old_dbug=@@GLOBAL.debug_dbug;
397 SET GLOBAL debug_dbug="+d,inject_sleep_gtid_100_x_x";
398 connection server_1;
399 SET @old_domain=@@SESSION.gtid_domain_id;
400 SET @old_format=@@SESSION.binlog_format;
401 SET SESSION gtid_domain_id=100;
402 SET SESSION binlog_format='row';
403 INSERT INTO t1 VALUES (30);
404 INSERT INTO t1 VALUES (31);
405 INSERT INTO t1 VALUES (32);
406 INSERT INTO t1 VALUES (33);
407 INSERT INTO t1 VALUES (34);
408 INSERT INTO t1 VALUES (35);
409 INSERT INTO t1 VALUES (36);
410 INSERT INTO t1 VALUES (37);
411 INSERT INTO t1 VALUES (38);
412 INSERT INTO t1 VALUES (39);
413 INSERT INTO t1 VALUES (40);
414 INSERT INTO t1 VALUES (41);
415 INSERT INTO t1 VALUES (42);
416 INSERT INTO t1 VALUES (43);
417 INSERT INTO t1 VALUES (44);
418 INSERT INTO t1 VALUES (45);
419 INSERT INTO t1 VALUES (46);
420 INSERT INTO t1 VALUES (47);
421 INSERT INTO t1 VALUES (48);
422 INSERT INTO t1 VALUES (49);
423 SET SESSION gtid_domain_id=@old_domain;
424 SET SESSION binlog_format=@old_format;
425 include/save_master_gtid.inc
426 connection server_2;
427 include/sync_with_master_gtid.inc
428 INSERT INTO t1 VALUES (50);
429 include/save_master_gtid.inc
430 connection server_3;
431 SET default_master_connection = "b2c";
432 START SLAVE;
433 include/wait_for_slave_to_start.inc
434 SELECT MASTER_GTID_WAIT("GTID", 30);
435 MASTER_GTID_WAIT("GTID", 30)
436 0
437 connection server_1;
438 SET default_master_connection = "b2a";
439 START SLAVE;
440 include/wait_for_slave_to_start.inc
441 SET default_master_connection = "c2a";
442 START SLAVE;
443 include/wait_for_slave_to_start.inc
444 include/sync_with_master_gtid.inc
445 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
446 a
447 30
448 31
449 32
450 33
451 34
452 35
453 36
454 37
455 38
456 39
457 40
458 41
459 42
460 43
461 44
462 45
463 46
464 47
465 48
466 49
467 50
468 connection server_2;
469 SET default_master_connection = "c2b";
470 START SLAVE;
471 include/wait_for_slave_to_start.inc
472 include/sync_with_master_gtid.inc
473 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
474 a
475 30
476 31
477 32
478 33
479 34
480 35
481 36
482 37
483 38
484 39
485 40
486 41
487 42
488 43
489 44
490 45
491 46
492 47
493 48
494 49
495 50
496 connection server_3;
497 include/sync_with_master_gtid.inc
498 SET GLOBAL debug_dbug=@old_dbug;
499 SELECT * FROM t1 WHERE a >= 30 ORDER BY a;
500 a
501 30
502 31
503 32
504 33
505 34
506 35
507 36
508 37
509 38
510 39
511 40
512 41
513 42
514 43
515 44
516 45
517 46
518 47
519 48
520 49
521 50
522 SET GLOBAL slave_exec_mode=@old_slave_mode;
523 SET GLOBAL gtid_strict_mode=@old_strict;
524 *** MDEV-8496: gtid_ignore_duplicates treats gtid_seq_no as 32-bit ***
525 connection server_1;
526 SET @old_domain= @@SESSION.gtid_domain_id;
527 SET SESSION gtid_domain_id=102;
528 SET SESSION gtid_seq_no=4294967294;
529 INSERT INTO t1 VALUES (60);
530 INSERT INTO t1 VALUES (61);
531 INSERT INTO t1 VALUES (62);
532 SET SESSION gtid_domain_id= @old_domain;
533 include/save_master_gtid.inc
534 connection server_4;
535 include/sync_with_master_gtid.inc
536 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
537 a
538 60
539 61
540 62
541 connection server_2;
542 SET default_master_connection = "c2b";
543 include/sync_with_master_gtid.inc
544 SET default_master_connection = "a2b";
545 include/sync_with_master_gtid.inc
546 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
547 a
548 60
549 61
550 62
551 connection server_3;
552 SET default_master_connection = "b2c";
553 include/sync_with_master_gtid.inc
554 SET default_master_connection = "a2c";
555 include/sync_with_master_gtid.inc
556 SELECT * FROM t1 WHERE a >= 60 ORDER BY a;
557 a
558 60
559 61
560 62
561 connection server_1;
562 SET GLOBAL gtid_domain_id=0;
563 STOP ALL SLAVES;
564 Warnings:
565 Note 1938 SLAVE 'b2a' stopped
566 Note 1938 SLAVE 'c2a' stopped
567 SET GLOBAL slave_parallel_threads= @old_parallel;
568 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
569 connection server_2;
570 SET GLOBAL gtid_domain_id=0;
571 STOP ALL SLAVES;
572 Warnings:
573 Note 1938 SLAVE 'a2b' stopped
574 Note 1938 SLAVE 'c2b' stopped
575 SET GLOBAL slave_parallel_threads= @old_parallel;
576 SET GLOBAL slave_parallel_mode= @old_mode;
577 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
578 connection server_3;
579 SET GLOBAL gtid_domain_id=0;
580 STOP ALL SLAVES;
581 Warnings:
582 Note 1938 SLAVE 'a2c' stopped
583 Note 1938 SLAVE 'b2c' stopped
584 SET GLOBAL slave_parallel_threads= @old_parallel;
585 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
586 connection server_4;
587 SET GLOBAL gtid_domain_id=0;
588 STOP ALL SLAVES;
589 Warnings:
590 Note 1938 SLAVE 'a2d' stopped
591 SET GLOBAL slave_parallel_threads= @old_parallel;
592 SET GLOBAL gtid_ignore_duplicates= @old_ignore_duplicates;
593 connection server_1;
594 DROP TABLE t1, t2;
595 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
596 include/reset_master_slave.inc
597 disconnect server_1;
598 connection server_2;
599 DROP TABLE t1, t2;
600 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
601 include/reset_master_slave.inc
602 disconnect server_2;
603 connection server_3;
604 DROP TABLE t1, t2;
605 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
606 include/reset_master_slave.inc
607 disconnect server_3;
608 connection server_4;
609 DROP TABLE t1, t2;
610 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
611 include/reset_master_slave.inc
612 disconnect server_4;