Debian Codegraph @

mysql-test/suite/rpl_gtid/r/rpl_mts_spco_commands_binlog.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 [connection slave]
7 CALL mtr.add_suppression("You need to use --log-bin to make --binlog-format work");
8 CALL mtr.add_suppression("The transaction owned GTID is already in the gtid_executed table");
9 SET @save_replica_parallel_workers= @@global.replica_parallel_workers;
10 SET @save_replica_parallel_type= @@global.replica_parallel_type;
11 Warnings:
12 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
13 SET @save_replica_preserve_commit_order= @@global.replica_preserve_commit_order;
14 SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
15 Warnings:
16 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
17 SET GLOBAL replica_parallel_workers= 10;
18 SET GLOBAL replica_preserve_commit_order= ON;
19 include/start_slave.inc
20 [connection master1]
21 SET @save_binlog_transaction_dependency_tracking= @@global.binlog_transaction_dependency_tracking;
22 Warnings:
23 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
24 SET @@global.binlog_transaction_dependency_tracking = COMMIT_ORDER;
25 Warnings:
26 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
27
28 #################################################################
29 # 1. Check for ANALYZE TABLE
30 #################################################################
31
32 # Setup
33
34 [connection master]
35 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
36 include/sync_slave_sql_with_master.inc
37
38 # Block slave sql applier threads
39
40 [connection slave]
41 BEGIN; INSERT INTO t2 VALUES (1);
42 [connection slave]
43 [connection slave]
44
45 # Generate the transactions which can be applied in parallel on slave
46
47 [connection master]
48 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
49 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; ANALYZE TABLE t1;
50 Table Op Msg_type Msg_text
51 test.t1 analyze status OK
52 [connection server_1]
53 BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT;
54 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
55
56 # Verify the transactions are ordered correctly on slave
57
58 [connection server_2]
59 include/assert.inc [Verify table t2 has 0 rows]
60 [connection slave]
61 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
62
63 # Rollback the first insert so that slave applier threads can
64 # unblock and proceed. Verify the transactions are applied.
65
66 [connection slave]
67 ROLLBACK;
68 TRUNCATE TABLE t1; TRUNCATE TABLE t2;
69 include/sync_slave_sql_with_master.inc
70 include/rpl_connect.inc [creating master]
71
72 #################################################################
73 # 2. Check for REPAIR TABLE
74 #################################################################
75
76 # Setup
77
78 [connection master]
79 CREATE TABLE t3(a INT PRIMARY KEY)Engine=MyISAM; INSERT INTO t3 VALUES(1),(2),(3);
80 include/sync_slave_sql_with_master.inc
81
82 # Block slave sql applier threads
83
84 [connection slave]
85 BEGIN; INSERT INTO t2 VALUES (1);
86 [connection slave]
87 [connection slave]
88
89 # Generate the transactions which can be applied in parallel on slave
90
91 [connection master]
92 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
93 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; SET TIMESTAMP = 100; REPAIR TABLE t3;
94 Table Op Msg_type Msg_text
95 test.t3 repair status OK
96 [connection server_1]
97 BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT;
98 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
99
100 # Verify the transactions are ordered correctly on slave
101
102 [connection server_2]
103 include/assert.inc [Verify table t2 has 0 rows]
104 [connection slave]
105 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
106
107 # Rollback the first insert so that slave applier threads can
108 # unblock and proceed. Verify the transactions are applied.
109
110 [connection slave]
111 ROLLBACK;
112 TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLE t3;
113 include/sync_slave_sql_with_master.inc
114 include/rpl_connect.inc [creating master]
115
116 #################################################################
117 # 3. Check for OPTIMIZE TABLE
118 #################################################################
119
120 # Setup
121
122 [connection master]
123 CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t4 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
124 include/sync_slave_sql_with_master.inc
125
126 # Block slave sql applier threads
127
128 [connection slave]
129 BEGIN; INSERT INTO t4 VALUES (1);
130 [connection slave]
131 [connection slave]
132
133 # Generate the transactions which can be applied in parallel on slave
134
135 [connection master]
136 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
137 BEGIN; INSERT INTO t4 VALUES (1); COMMIT; BEGIN; INSERT INTO t4 VALUES (2); COMMIT; OPTIMIZE TABLE t3;
138 Table Op Msg_type Msg_text
139 test.t3 optimize note Table does not support optimize, doing recreate + analyze instead
140 test.t3 optimize status OK
141 [connection server_1]
142 BEGIN; INSERT INTO t4 VALUES (3); COMMIT; BEGIN; INSERT INTO t4 VALUES (4); COMMIT; BEGIN; INSERT INTO t4 VALUES (5); COMMIT;
143 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
144
145 # Verify the transactions are ordered correctly on slave
146
147 [connection server_2]
148 include/assert.inc [Verify table t4 is empty]
149 [connection slave]
150 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
151
152 # Rollback the first insert so that slave applier threads can
153 # unblock and proceed. Verify the transactions are applied.
154
155 [connection slave]
156 ROLLBACK;
157 DROP TABLE t3; DROP TABLE t4;
158 include/sync_slave_sql_with_master.inc
159 include/rpl_connect.inc [creating master]
160
161 #################################################################
162 # 4. Check for RENAME TABLE
163 #################################################################
164
165 # 4.1 Check for RENAME single table
166
167 # Setup
168
169 [connection master]
170 INSERT INTO t1 VALUES (10);
171 include/sync_slave_sql_with_master.inc
172
173 # Block slave sql applier threads
174
175 [connection slave]
176 BEGIN; INSERT INTO t2 VALUES (1);
177 [connection slave]
178 [connection slave]
179
180 # Generate the transactions which can be applied in parallel on slave
181
182 [connection master]
183 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
184 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; RENAME TABLE t1 to t10;
185 [connection server_1]
186 BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT;
187 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
188
189 # Verify the transactions are ordered correctly on slave
190
191 [connection server_2]
192 include/assert.inc [Verify table t2 is empty]
193 include/assert.inc [Verify tables t1 and t2 exist]
194 include/assert.inc [Verify table t10 does not exist]
195 [connection slave]
196 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
197
198 # Rollback the first insert so that slave applier threads can
199 # unblock and proceed. Verify the transactions are applied.
200
201 [connection slave]
202 ROLLBACK;
203 RENAME TABLE t10 to t1; TRUNCATE TABLE t1; TRUNCATE TABLE t2;
204 include/sync_slave_sql_with_master.inc
205
206 # 4.2 Check for RENAME multiple tables
207
208 # Setup
209
210 [connection master]
211 CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
212 include/sync_slave_sql_with_master.inc
213
214 # Block slave sql applier threads
215
216 [connection slave]
217 BEGIN; INSERT INTO t2 VALUES (1);
218 [connection slave]
219 [connection slave]
220
221 # Generate the transactions which can be applied in parallel on slave
222
223 [connection master]
224 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
225 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; RENAME TABLE t1 to t10, t3 to t30;
226 [connection server_1]
227 BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT;
228 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
229
230 # Verify the transactions are ordered correctly on slave
231
232 [connection server_2]
233 include/assert.inc [Verify table t2 is empty]
234 include/assert.inc [Verify tables t1, t2 and t3 exist]
235 include/assert.inc [Verify tables t10 and t30 do not exist]
236 [connection slave]
237 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
238
239 # Rollback the first insert so that slave applier threads can
240 # unblock and proceed. Verify the transactions are applied.
241
242 [connection slave]
243 ROLLBACK;
244 DROP TABLE t10, t2, t30;
245 include/sync_slave_sql_with_master.inc
246
247 ####################################################################
248 # 5. CREATE TABLE and CREATE TABLE IF NOT EXISTS
249 ####################################################################
250
251 # Setup
252
253 [connection master]
254 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
255 include/sync_slave_sql_with_master.inc
256
257 # Block slave sql applier threads
258
259 [connection slave]
260 BEGIN; INSERT INTO t1 VALUES (1);
261 [connection slave]
262 [connection slave]
263
264 # Generate the transactions which can be applied in parallel on slave
265
266 [connection master]
267 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
268 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE TABLE t10 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS t20 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
269 [connection server_1]
270 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
271 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
272
273 # Verify the transactions are ordered correctly on slave
274
275 [connection server_2]
276 include/assert.inc [Verify table t1 is empty]
277 include/assert.inc [Verify tables t1 and t2 exist]
278 include/assert.inc [Verify tables t10 and t20 do not exist]
279 [connection slave]
280 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
281
282 # Rollback the first insert so that slave applier threads can
283 # unblock and proceed. Verify the transactions are applied.
284
285 [connection slave]
286 ROLLBACK;
287 DROP TABLE t10, t20; TRUNCATE TABLE t1; TRUNCATE TABLE t2;
288 include/sync_slave_sql_with_master.inc
289
290 ####################################################################
291 # 6. DROP TABLE and DROP TABLE IF EXISTS
292 ####################################################################
293
294 # Setup
295
296 [connection master]
297 CREATE TABLE t3 LIKE t1; CREATE TABLE t4 LIKE t1; CREATE TABLE t5 LIKE t1;
298 include/sync_slave_sql_with_master.inc
299
300 # Block slave sql applier threads
301
302 [connection slave]
303 BEGIN; INSERT INTO t1 VALUES (1);
304 [connection slave]
305 [connection slave]
306
307 # Generate the transactions which can be applied in parallel on slave
308
309 [connection master]
310 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
311 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TABLE t2; DROP TABLE t3, t4; DROP TABLE IF EXISTS t5; DROP TABLE IF EXISTS t10;
312 Warnings:
313 Note 1051 Unknown table 'test.t10'
314 [connection server_1]
315 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
316 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
317
318 # Verify the transactions are ordered correctly on slave
319
320 [connection server_2]
321 include/assert.inc [Verify table t1 is empty]
322 include/assert.inc [Verify tables t1, t2, t3, t4, and t5 exist]
323 include/assert.inc [Verify table t10 does not exist]
324 [connection slave]
325 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
326
327 # Rollback the first insert so that slave applier threads can
328 # unblock and proceed. Verify the transactions are applied.
329
330 [connection slave]
331 ROLLBACK;
332 TRUNCATE TABLE t1; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB;
333 include/sync_slave_sql_with_master.inc
334
335 ####################################################################
336 # 7. Check for CREATE DATABASE and CREATE DATABASE IF NOT EXISTS
337 ####################################################################
338
339 # Setup
340
341 [connection master]
342 CREATE DATABASE db1;
343 include/sync_slave_sql_with_master.inc
344
345 # Block slave sql applier threads
346
347 [connection slave]
348 BEGIN; INSERT INTO t1 VALUES (1);
349 [connection slave]
350 [connection slave]
351
352 # Generate the transactions which can be applied in parallel on slave
353
354 [connection master]
355 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
356 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE DATABASE db10; CREATE DATABASE IF NOT EXISTS db20;
357 [connection server_1]
358 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
359 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
360
361 # Verify the transactions are ordered correctly on slave
362
363 [connection server_2]
364 include/assert.inc [Verify table t1 is empty]
365 include/assert.inc [Verify table t2 is empty]
366 include/assert.inc [Verify DATABASE db1 exists]
367 include/assert.inc [Verify DATABASES db10 and db20 do not exist]
368 [connection slave]
369 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
370
371 # Rollback the first insert so that slave applier threads can
372 # unblock and proceed. Verify the transactions are applied.
373
374 [connection slave]
375 ROLLBACK;
376 TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP DATABASE db1; DROP DATABASE db10; DROP DATABASE db20;
377 include/sync_slave_sql_with_master.inc
378
379 #################################################################
380 # 8. Check for DROP DATABASE and DROP DATABASE IF EXISTS
381 #################################################################
382
383 # Setup
384
385 [connection master]
386 CREATE DATABASE db1; CREATE DATABASE db2;
387 include/sync_slave_sql_with_master.inc
388
389 # Block slave sql applier threads
390
391 [connection slave]
392 BEGIN; INSERT INTO t1 VALUES (1);
393 [connection slave]
394 [connection slave]
395
396 # Generate the transactions which can be applied in parallel on slave
397
398 [connection master]
399 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
400 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP DATABASE db1; DROP DATABASE IF EXISTS db2; DROP DATABASE IF EXISTS db10;
401 Warnings:
402 Note 1008 Can't drop database 'db10'; database doesn't exist
403 [connection server_1]
404 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
405 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
406
407 # Verify the transactions are ordered correctly on slave
408
409 [connection server_2]
410 include/assert.inc [Verify table t1 is empty]
411 include/assert.inc [Verify table t2 is empty]
412 include/assert.inc [Verify DATABASES db1 and db2 exist]
413 include/assert.inc [Verify DATABASE db10 does not exist]
414 [connection slave]
415 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
416
417 # Rollback the first insert so that slave applier threads can
418 # unblock and proceed. Verify the transactions are applied.
419
420 [connection slave]
421 ROLLBACK;
422 TRUNCATE TABLE t1; TRUNCATE TABLE t2;
423 include/sync_slave_sql_with_master.inc
424
425 #################################################################
426 # 9. Check for CREATE TABLESPACE
427 #################################################################
428
429 # Setup
430
431 [connection master]
432 INSERT INTO t1 VALUES (10);
433 include/sync_slave_sql_with_master.inc
434
435 # Block slave sql applier threads
436
437 [connection slave]
438 BEGIN; INSERT INTO t1 VALUES (1);
439 [connection slave]
440 [connection slave]
441
442 # Generate the transactions which can be applied in parallel on slave
443
444 [connection master]
445 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
446 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
447 [connection server_1]
448 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
449 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
450
451 # Verify the transactions are ordered correctly on slave
452
453 [connection server_2]
454 include/assert.inc [Verify table t1 has one value]
455 include/assert.inc [Verify table t2 is empty]
456 include/assert.inc [Verify TABLESPACE does not exist]
457 [connection slave]
458 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
459
460 # Rollback the first insert so that slave applier threads can
461 # unblock and proceed. Verify the transactions are applied.
462
463 [connection slave]
464 ROLLBACK;
465 TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLESPACE ts1;
466 include/sync_slave_sql_with_master.inc
467
468 #################################################################
469 # 10. Check for ALTER TABLESPACE
470 #################################################################
471
472 # Setup
473
474 [connection master]
475 INSERT INTO t1 VALUES (10); CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB; CREATE TABLE t11 (a INT, b INT) ENGINE = InnoDB TABLESPACE=ts1;
476 include/sync_slave_sql_with_master.inc
477
478 # Block slave sql applier threads
479
480 [connection slave]
481 BEGIN; INSERT INTO t1 VALUES (1);
482 [connection slave]
483 [connection slave]
484
485 # Generate the transactions which can be applied in parallel on slave
486
487 [connection master]
488 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
489 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; ALTER TABLESPACE ts1 RENAME TO ts11;
490 [connection server_1]
491 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
492 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
493
494 # Verify the transactions are ordered correctly on slave
495
496 [connection server_2]
497 include/assert.inc [Verify table t1 has one value]
498 include/assert.inc [Verify TABLESPACE ts1 exists]
499 include/assert.inc [Verify TABLESPACE ts11 does not exists]
500 [connection slave]
501 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
502
503 # Rollback the first insert so that slave applier threads can
504 # unblock and proceed. Verify the transactions are applied.
505
506 [connection slave]
507 ROLLBACK;
508 TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLE t11; DROP TABLESPACE ts11;
509 include/sync_slave_sql_with_master.inc
510
511 #################################################################
512 # 11. Check for DROP TABLESPACE
513 #################################################################
514
515 # Setup
516
517 [connection master]
518 INSERT INTO t1 VALUES (10); CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
519 include/sync_slave_sql_with_master.inc
520
521 # Block slave sql applier threads
522
523 [connection slave]
524 BEGIN; INSERT INTO t1 VALUES (1);
525 [connection slave]
526 [connection slave]
527
528 # Generate the transactions which can be applied in parallel on slave
529
530 [connection master]
531 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
532 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TABLESPACE ts1;
533 [connection server_1]
534 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
535 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
536
537 # Verify the transactions are ordered correctly on slave
538
539 [connection server_2]
540 include/assert.inc [Verify table t1 has one value]
541 include/assert.inc [Verify table t2 is empty]
542 include/assert.inc [Verify TABLESPACE exists]
543 [connection slave]
544 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
545
546 # Rollback the first insert so that slave applier threads can
547 # unblock and proceed. Verify the transactions are applied.
548
549 [connection slave]
550 ROLLBACK;
551 TRUNCATE TABLE t1; TRUNCATE TABLE t2;
552 include/sync_slave_sql_with_master.inc
553
554 #################################################################
555 # 12. Check for CREATE INDEX
556 #################################################################
557
558 # Setup
559
560 [connection master]
561 INSERT INTO t1 VALUES (10); CREATE TABLE t3 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); INSERT INTO t3 VALUES(1,1);
562 include/sync_slave_sql_with_master.inc
563
564 # Block slave sql applier threads
565
566 [connection slave]
567 BEGIN; INSERT INTO t1 VALUES (1);
568 [connection slave]
569 [connection slave]
570
571 # Generate the transactions which can be applied in parallel on slave
572
573 [connection master]
574 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
575 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE INDEX idx1 ON t3 ((col1 + col2));
576 [connection server_1]
577 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
578 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
579
580 # Verify the transactions are ordered correctly on slave
581
582 [connection server_2]
583 include/assert.inc [Verify table t1 has one value]
584 include/assert.inc [Verify table t2 is empty]
585 include/assert.inc [Verify INDEX does not exist]
586 [connection slave]
587 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
588
589 # Rollback the first insert so that slave applier threads can
590 # unblock and proceed. Verify the transactions are applied.
591
592 [connection slave]
593 ROLLBACK;
594 TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP INDEX idx1 ON t3; DROP TABLE t3;
595 include/sync_slave_sql_with_master.inc
596
597 #################################################################
598 # 13. Check for DROP INDEX
599 #################################################################
600
601 # Setup
602
603 [connection master]
604 INSERT INTO t1 VALUES (10); CREATE TABLE t3 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); INSERT INTO t3 VALUES(1,1); CREATE INDEX idx1 ON t3 ((col1 + col2));
605 include/sync_slave_sql_with_master.inc
606
607 # Block slave sql applier threads
608
609 [connection slave]
610 BEGIN; INSERT INTO t1 VALUES (1);
611 [connection slave]
612 [connection slave]
613
614 # Generate the transactions which can be applied in parallel on slave
615
616 [connection master]
617 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
618 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP INDEX idx1 ON t3;
619 [connection server_1]
620 BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT;
621 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
622
623 # Verify the transactions are ordered correctly on slave
624
625 [connection server_2]
626 include/assert.inc [Verify table t1 has one value]
627 include/assert.inc [Verify table t2 is empty]
628 include/assert.inc [Verify INDEX exists]
629 [connection slave]
630 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
631
632 # Rollback the first insert so that slave applier threads can
633 # unblock and proceed. Verify the transactions are applied.
634
635 [connection slave]
636 ROLLBACK;
637 TRUNCATE TABLE t1; DROP TABLE t2, t3;
638 include/sync_slave_sql_with_master.inc
639
640 ####################################################################
641 # 13. DROP FUNCTION and DROP FUNCTION IF EXISTS
642 ####################################################################
643
644 # Setup
645
646 [connection master]
647 CREATE FUNCTION func1 (param1 INT) returns CHAR(50) return 'test'; CREATE FUNCTION func2 (param1 INT) returns CHAR(50) return 'test';
648 include/sync_slave_sql_with_master.inc
649
650 # Block slave sql applier threads
651
652 [connection slave]
653 BEGIN; INSERT INTO t1 VALUES (1);
654 [connection slave]
655 [connection slave]
656
657 # Generate the transactions which can be applied in parallel on slave
658
659 [connection master]
660 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
661 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP FUNCTION func1; DROP FUNCTION IF EXISTS func2; DROP FUNCTION IF EXISTS func3;
662 Warnings:
663 Note 1305 FUNCTION test.func3 does not exist
664 [connection server_1]
665 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
666 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
667
668 # Verify the transactions are ordered correctly on slave
669
670 [connection server_2]
671 include/assert.inc [Verify table t1 is empty]
672 include/assert.inc [Verify function func1 and func2 exists]
673 [connection slave]
674 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
675
676 # Rollback the first insert so that slave applier threads can
677 # unblock and proceed. Verify the transactions are applied.
678
679 [connection slave]
680 ROLLBACK;
681 DROP TABLE t1;
682 include/sync_slave_sql_with_master.inc
683
684 ####################################################################
685 # 14. DROP PROCEDURE and DROP PROCEDURE IF EXISTS
686 ####################################################################
687
688 # Setup
689
690 [connection master]
691 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB;;
692 CREATE PROCEDURE proc_t1 (OUT param1 INT) BEGIN SELECT 'test'; END; CREATE PROCEDURE proc_t2 (OUT param1 INT) BEGIN SELECT 'test'; END//
693 include/sync_slave_sql_with_master.inc
694
695 # Block slave sql applier threads
696
697 [connection slave]
698 BEGIN; INSERT INTO t1 VALUES (1);;
699 [connection slave]
700 [connection slave]
701
702 # Generate the transactions which can be applied in parallel on slave
703
704 [connection master]
705 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
706 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP PROCEDURE proc_t1; DROP PROCEDURE IF EXISTS proc_t2; DROP PROCEDURE IF EXISTS proc_t3;
707 Warnings:
708 Note 1305 PROCEDURE test.proc_t3 does not exist
709 [connection server_1]
710 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
711 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
712
713 # Verify the transactions are ordered correctly on slave
714
715 [connection server_2]
716 include/assert.inc [Verify table t1 is empty]
717 include/assert.inc [Verify procedure proc_t1 and proc_t2 exists]
718 [connection slave]
719 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
720
721 # Rollback the first insert so that slave applier threads can
722 # unblock and proceed. Verify the transactions are applied.
723
724 [connection slave]
725 ROLLBACK;
726 TRUNCATE TABLE t1;
727 include/sync_slave_sql_with_master.inc
728
729 ####################################################################
730 # 15. DROP VIEW and DROP VIEW IF EXISTS
731 ####################################################################
732
733 # Setup
734
735 [connection master]
736 CREATE VIEW view_t1 AS SELECT * FROM t1; CREATE VIEW view_t2 AS SELECT * FROM t1;
737 include/sync_slave_sql_with_master.inc
738
739 # Block slave sql applier threads
740
741 [connection slave]
742 BEGIN; INSERT INTO t1 VALUES (1);;
743 [connection slave]
744 [connection slave]
745
746 # Generate the transactions which can be applied in parallel on slave
747
748 [connection master]
749 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
750 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP VIEW view_t1; DROP VIEW IF EXISTS view_t2; DROP VIEW IF EXISTS view_t3;
751 Warnings:
752 Note 1051 Unknown table 'test.view_t3'
753 [connection server_1]
754 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
755 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
756
757 # Verify the transactions are ordered correctly on slave
758
759 [connection server_2]
760 include/assert.inc [Verify table t1 is empty]
761 include/assert.inc [Verify views view_t1 and view_t2 exists]
762 [connection slave]
763 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
764
765 # Rollback the first insert so that slave applier threads can
766 # unblock and proceed. Verify the transactions are applied.
767
768 [connection slave]
769 ROLLBACK;
770 DROP TABLE t1;
771 include/sync_slave_sql_with_master.inc
772
773 ####################################################################
774 # 16. DROP EVENT and DROP EVENT IF EXISTS
775 ####################################################################
776
777 # Setup
778
779 [connection master]
780 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB;;
781 CREATE EVENT event_t1 ON SCHEDULE EVERY 1 DAY DO BEGIN select 'test'; END; CREATE EVENT event_t2 ON SCHEDULE EVERY 1 DAY DO BEGIN select 'test'; END//
782 include/sync_slave_sql_with_master.inc
783
784 # Block slave sql applier threads
785
786 [connection slave]
787 BEGIN; INSERT INTO t1 VALUES (1);;
788 [connection slave]
789 [connection slave]
790
791 # Generate the transactions which can be applied in parallel on slave
792
793 [connection master]
794 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
795 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP EVENT event_t1; DROP EVENT IF EXISTS event_t2; DROP EVENT IF EXISTS event_t3;
796 Warnings:
797 Note 1305 Event event_t3 does not exist
798 [connection server_1]
799 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
800 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
801
802 # Verify the transactions are ordered correctly on slave
803
804 [connection server_2]
805 include/assert.inc [Verify table t1 is empty]
806 include/assert.inc [Verify events event_t1 and event_t2 exists]
807 [connection slave]
808 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
809
810 # Rollback the first insert so that slave applier threads can
811 # unblock and proceed. Verify the transactions are applied.
812
813 [connection slave]
814 ROLLBACK;
815 TRUNCATE TABLE t1;
816 include/sync_slave_sql_with_master.inc
817
818 ####################################################################
819 # 17. DROP TRIGGER and DROP TRIGGER IF EXISTS
820 ####################################################################
821
822 # Setup
823
824 [connection master]
825 CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TRIGGER trig_t1 BEFORE INSERT ON t2 FOR EACH ROW SET @sum = @sum + NEW.c1; CREATE TRIGGER trig_t2 BEFORE INSERT ON t3 FOR EACH ROW SET @sum = @sum + NEW.c1;
826 include/sync_slave_sql_with_master.inc
827
828 # Block slave sql applier threads
829
830 [connection slave]
831 BEGIN; INSERT INTO t1 VALUES (1);;
832 [connection slave]
833 [connection slave]
834
835 # Generate the transactions which can be applied in parallel on slave
836
837 [connection master]
838 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
839 BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TRIGGER trig_t1; DROP TRIGGER IF EXISTS trig_t2; DROP TRIGGER IF EXISTS trig_t3;
840 Warnings:
841 Note 1360 Trigger does not exist
842 [connection server_1]
843 BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT;
844 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
845
846 # Verify the transactions are ordered correctly on slave
847
848 [connection server_2]
849 include/assert.inc [Verify table t1 is empty]
850 include/assert.inc [Verify trigger trig_t1 and trig_t2 exists]
851 [connection slave]
852 include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation]
853
854 # Rollback the first insert so that slave applier threads can
855 # unblock and proceed. Verify the transactions are applied.
856
857 [connection slave]
858 ROLLBACK;
859 DROP TABLE t1; DROP TABLE t2; DROP TABLE t3;
860 include/sync_slave_sql_with_master.inc
861
862 #################################################################
863 # Cleanup
864 #################################################################
865 [connection master1]
866 SET GLOBAL binlog_transaction_dependency_tracking=@save_binlog_transaction_dependency_tracking;
867 Warnings:
868 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
869 include/sync_slave_sql_with_master.inc
870 include/stop_slave.inc
871 SET GLOBAL replica_parallel_type=@save_replica_parallel_type;
872 Warnings:
873 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
874 SET GLOBAL replica_parallel_workers=@save_replica_parallel_workers;
875 SET GLOBAL replica_preserve_commit_order=@save_replica_preserve_commit_order;
876 include/start_slave.inc
877 include/rpl_end.inc