Debian Codegraph @

mysql-test/suite/galera/r/galera_ddl_fk_conflict.result

raw
1 connection node_2;
2 connection node_1;
3 connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1;
4 connection node_1a;
5 SET SESSION wsrep_sync_wait=0;
6 FLUSH STATUS;
7 connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1;
8 connection node_1b;
9 SET SESSION wsrep_sync_wait=0;
10 ######################################################################
11 # Test for ALTER TABLE ENGINE=INNODB
12 ######################################################################
13 ######################################################################
14 #
15 # Scenario #1: DML working on FK parent table BF aborted by DDL
16 # over child table
17 #
18 ######################################################################
19 connection node_1;
20 SET SESSION wsrep_sync_wait=0;
21 FLUSH STATUS;
22 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
23 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
24 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
25 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
26 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
27 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
28 INSERT INTO c1 VALUES (1,1);
29 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
30 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
31 connection node_1;
32 SET AUTOCOMMIT=ON;
33 START TRANSACTION;
34 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
35 connection node_2;
36 SET SESSION wsrep_sync_wait=0;
37 ALTER TABLE c1 ENGINE=INNODB;
38 connection node_1;
39 COMMIT;
40 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
41 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
42 EXPECT_1
43 1
44 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
45 EXPECT_2
46 2
47 connection node_2;
48 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
49 EXPECT_1
50 1
51 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
52 EXPECT_2
53 2
54 connection node_1;
55 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
56 INSERT IGNORE INTO c1 VALUES (1,1);
57 ######################################################################
58 #
59 # Scenario #2: DML working on FK parent table tries to replicate, but
60 # fails in certification for earlier DDL on child table
61 #
62 ######################################################################
63 connection node_1;
64 BEGIN;
65 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
66 connection node_2;
67 ALTER TABLE c1 ENGINE=INNODB;
68 connection node_1a;
69 SET SESSION wsrep_on = 0;
70 SET SESSION wsrep_on = 1;
71 SET GLOBAL wsrep_provider_options = 'dbug=';
72 connection node_1;
73 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
74 COMMIT;
75 connection node_1a;
76 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
77 connection node_1;
78 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
79 SELECT 'I deadlocked';
80 I deadlocked
81 I deadlocked
82 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
83 EXPECT_1
84 1
85 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
86 EXPECT_2
87 2
88 connection node_2;
89 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
90 EXPECT_1
91 1
92 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
93 EXPECT_2
94 2
95 connection node_1;
96 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
97 INSERT IGNORE INTO c1 VALUES (1,1);
98 ######################################################################
99 #
100 # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
101 # but fails in certification for earlier DDL on child table
102 # which is child to both FK parents
103 #
104 ######################################################################
105 connection node_1;
106 BEGIN;
107 connection node_1b;
108 BEGIN;
109 connection node_1a;
110 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
111 connection node_2;
112 ALTER TABLE c2 ENGINE=INNODB;
113 connection node_1a;
114 SET SESSION wsrep_on = 0;
115 SET SESSION wsrep_on = 1;
116 SET GLOBAL wsrep_provider_options = 'dbug=';
117 connection node_1;
118 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
119 COMMIT;
120 connection node_1b;
121 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
122 COMMIT;
123 connection node_1a;
124 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
125 connection node_1;
126 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
127 SELECT 'I deadlocked';
128 I deadlocked
129 I deadlocked
130 connection node_1b;
131 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
132 SELECT 'I deadlocked';
133 I deadlocked
134 I deadlocked
135 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
136 EXPECT_1
137 1
138 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
139 EXPECT_2
140 2
141 connection node_2;
142 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
143 EXPECT_1
144 1
145 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
146 EXPECT_2
147 2
148 DROP TABLE IF EXISTS c1, c2;
149 DROP TABLE p1, p2;
150 ######################################################################
151 # Test for TRUNCATE TABLE
152 ######################################################################
153 ######################################################################
154 #
155 # Scenario #1: DML working on FK parent table BF aborted by DDL
156 # over child table
157 #
158 ######################################################################
159 connection node_1;
160 SET SESSION wsrep_sync_wait=0;
161 FLUSH STATUS;
162 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
163 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
164 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
165 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
166 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
167 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
168 INSERT INTO c1 VALUES (1,1);
169 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
170 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
171 connection node_1;
172 SET AUTOCOMMIT=ON;
173 START TRANSACTION;
174 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
175 connection node_2;
176 SET SESSION wsrep_sync_wait=0;
177 TRUNCATE TABLE c1 ;
178 connection node_1;
179 COMMIT;
180 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
181 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
182 EXPECT_1
183 1
184 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
185 EXPECT_2
186 2
187 connection node_2;
188 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
189 EXPECT_1
190 1
191 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
192 EXPECT_2
193 2
194 connection node_1;
195 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
196 INSERT IGNORE INTO c1 VALUES (1,1);
197 ######################################################################
198 #
199 # Scenario #2: DML working on FK parent table tries to replicate, but
200 # fails in certification for earlier DDL on child table
201 #
202 ######################################################################
203 connection node_1;
204 BEGIN;
205 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
206 connection node_2;
207 TRUNCATE TABLE c1 ;
208 connection node_1a;
209 SET SESSION wsrep_on = 0;
210 SET SESSION wsrep_on = 1;
211 SET GLOBAL wsrep_provider_options = 'dbug=';
212 connection node_1;
213 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
214 COMMIT;
215 connection node_1a;
216 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
217 connection node_1;
218 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
219 SELECT 'I deadlocked';
220 I deadlocked
221 I deadlocked
222 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
223 EXPECT_1
224 1
225 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
226 EXPECT_2
227 2
228 connection node_2;
229 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
230 EXPECT_1
231 1
232 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
233 EXPECT_2
234 2
235 connection node_1;
236 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
237 INSERT IGNORE INTO c1 VALUES (1,1);
238 ######################################################################
239 #
240 # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
241 # but fails in certification for earlier DDL on child table
242 # which is child to both FK parents
243 #
244 ######################################################################
245 connection node_1;
246 BEGIN;
247 connection node_1b;
248 BEGIN;
249 connection node_1a;
250 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
251 connection node_2;
252 TRUNCATE TABLE c2 ;
253 connection node_1a;
254 SET SESSION wsrep_on = 0;
255 SET SESSION wsrep_on = 1;
256 SET GLOBAL wsrep_provider_options = 'dbug=';
257 connection node_1;
258 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
259 COMMIT;
260 connection node_1b;
261 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
262 COMMIT;
263 connection node_1a;
264 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
265 connection node_1;
266 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
267 SELECT 'I deadlocked';
268 I deadlocked
269 I deadlocked
270 connection node_1b;
271 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
272 SELECT 'I deadlocked';
273 I deadlocked
274 I deadlocked
275 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
276 EXPECT_1
277 1
278 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
279 EXPECT_2
280 2
281 connection node_2;
282 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
283 EXPECT_1
284 1
285 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
286 EXPECT_2
287 2
288 DROP TABLE IF EXISTS c1, c2;
289 DROP TABLE p1, p2;
290 ######################################################################
291 # Test for DROP TABLE
292 ######################################################################
293 ######################################################################
294 #
295 # Scenario #1: DML working on FK parent table BF aborted by DDL
296 # over child table
297 #
298 ######################################################################
299 connection node_1;
300 SET SESSION wsrep_sync_wait=0;
301 FLUSH STATUS;
302 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
303 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
304 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
305 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
306 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
307 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
308 INSERT INTO c1 VALUES (1,1);
309 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
310 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
311 connection node_1;
312 SET AUTOCOMMIT=ON;
313 START TRANSACTION;
314 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
315 connection node_2;
316 SET SESSION wsrep_sync_wait=0;
317 DROP TABLE c1 ;
318 connection node_1;
319 COMMIT;
320 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
321 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
322 EXPECT_1
323 1
324 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
325 EXPECT_2
326 2
327 connection node_2;
328 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
329 EXPECT_1
330 1
331 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
332 EXPECT_2
333 2
334 connection node_1;
335 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
336 INSERT IGNORE INTO c1 VALUES (1,1);
337 ######################################################################
338 #
339 # Scenario #2: DML working on FK parent table tries to replicate, but
340 # fails in certification for earlier DDL on child table
341 #
342 ######################################################################
343 connection node_1;
344 BEGIN;
345 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
346 connection node_2;
347 DROP TABLE c1 ;
348 connection node_1a;
349 SET SESSION wsrep_on = 0;
350 SET SESSION wsrep_on = 1;
351 SET GLOBAL wsrep_provider_options = 'dbug=';
352 connection node_1;
353 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
354 COMMIT;
355 connection node_1a;
356 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
357 connection node_1;
358 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
359 SELECT 'I deadlocked';
360 I deadlocked
361 I deadlocked
362 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
363 EXPECT_1
364 1
365 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
366 EXPECT_2
367 2
368 connection node_2;
369 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
370 EXPECT_1
371 1
372 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
373 EXPECT_2
374 2
375 connection node_1;
376 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
377 INSERT IGNORE INTO c1 VALUES (1,1);
378 ######################################################################
379 #
380 # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
381 # but fails in certification for earlier DDL on child table
382 # which is child to both FK parents
383 #
384 ######################################################################
385 connection node_1;
386 BEGIN;
387 connection node_1b;
388 BEGIN;
389 connection node_1a;
390 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
391 connection node_2;
392 DROP TABLE c2 ;
393 connection node_1a;
394 SET SESSION wsrep_on = 0;
395 SET SESSION wsrep_on = 1;
396 SET GLOBAL wsrep_provider_options = 'dbug=';
397 connection node_1;
398 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
399 COMMIT;
400 connection node_1b;
401 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
402 COMMIT;
403 connection node_1a;
404 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
405 connection node_1;
406 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
407 SELECT 'I deadlocked';
408 I deadlocked
409 I deadlocked
410 connection node_1b;
411 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
412 SELECT 'I deadlocked';
413 I deadlocked
414 I deadlocked
415 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
416 EXPECT_1
417 1
418 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
419 EXPECT_2
420 2
421 connection node_2;
422 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
423 EXPECT_1
424 1
425 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
426 EXPECT_2
427 2
428 DROP TABLE IF EXISTS c1, c2;
429 Warnings:
430 Note 1051 Unknown table 'test.c2'
431 DROP TABLE p1, p2;
432 ######################################################################
433 # Test for DROP TABLE
434 ######################################################################
435 connection node_1;
436 SET SESSION wsrep_sync_wait=0;
437 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
438 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
439 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
440 INSERT INTO c1 VALUES (1,1);
441 ######################################################################
442 #
443 # Scenario #4: DML working on FK parent table tries to replicate, but
444 # fails in certification for earlier DDL on child table
445 # and another temporary table. TMP table should be skipped
446 # but FK child table should be replicated with proper keys
447 #
448 ######################################################################
449 connection node_1;
450 BEGIN;
451 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
452 connection node_2;
453 CREATE TEMPORARY TABLE tmp1 (i int);
454 CREATE TEMPORARY TABLE tmp2 (i int);
455 DROP TABLE tmp1, c1, tmp2 ;
456 connection node_1a;
457 SET SESSION wsrep_on = 0;
458 SET SESSION wsrep_on = 1;
459 SET GLOBAL wsrep_provider_options = 'dbug=';
460 connection node_1;
461 INSERT INTO p1 VALUES (10, 'TO DEADLOCK');
462 COMMIT;
463 connection node_1a;
464 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
465 connection node_1;
466 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
467 SELECT 'I deadlocked';
468 I deadlocked
469 I deadlocked
470 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
471 EXPECT_1
472 1
473 connection node_2;
474 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
475 EXPECT_1
476 1
477 DROP TABLE IF EXISTS c1;
478 Warnings:
479 Note 1051 Unknown table 'test.c1'
480 DROP TABLE p1;
481 DROP TABLE IF EXISTS tmp1, tmp2;
482 Warnings:
483 Note 1051 Unknown table 'test.tmp1,test.tmp2'
484 ######################################################################
485 # Test for DROP TABLE IF EXISTS
486 ######################################################################
487 ######################################################################
488 #
489 # Scenario #1: DML working on FK parent table BF aborted by DDL
490 # over child table
491 #
492 ######################################################################
493 connection node_1;
494 SET SESSION wsrep_sync_wait=0;
495 FLUSH STATUS;
496 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
497 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
498 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
499 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
500 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
501 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
502 INSERT INTO c1 VALUES (1,1);
503 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
504 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
505 connection node_1;
506 SET AUTOCOMMIT=ON;
507 START TRANSACTION;
508 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
509 connection node_2;
510 SET SESSION wsrep_sync_wait=0;
511 DROP TABLE IF EXISTS c1 ;
512 connection node_1;
513 COMMIT;
514 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
515 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
516 EXPECT_1
517 1
518 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
519 EXPECT_2
520 2
521 connection node_2;
522 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
523 EXPECT_1
524 1
525 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
526 EXPECT_2
527 2
528 connection node_1;
529 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
530 INSERT IGNORE INTO c1 VALUES (1,1);
531 ######################################################################
532 #
533 # Scenario #2: DML working on FK parent table tries to replicate, but
534 # fails in certification for earlier DDL on child table
535 #
536 ######################################################################
537 connection node_1;
538 BEGIN;
539 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
540 connection node_2;
541 DROP TABLE IF EXISTS c1 ;
542 connection node_1a;
543 SET SESSION wsrep_on = 0;
544 SET SESSION wsrep_on = 1;
545 SET GLOBAL wsrep_provider_options = 'dbug=';
546 connection node_1;
547 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
548 COMMIT;
549 connection node_1a;
550 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
551 connection node_1;
552 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
553 SELECT 'I deadlocked';
554 I deadlocked
555 I deadlocked
556 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
557 EXPECT_1
558 1
559 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
560 EXPECT_2
561 2
562 connection node_2;
563 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
564 EXPECT_1
565 1
566 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
567 EXPECT_2
568 2
569 connection node_1;
570 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
571 INSERT IGNORE INTO c1 VALUES (1,1);
572 ######################################################################
573 #
574 # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
575 # but fails in certification for earlier DDL on child table
576 # which is child to both FK parents
577 #
578 ######################################################################
579 connection node_1;
580 BEGIN;
581 connection node_1b;
582 BEGIN;
583 connection node_1a;
584 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
585 connection node_2;
586 DROP TABLE IF EXISTS c2 ;
587 connection node_1a;
588 SET SESSION wsrep_on = 0;
589 SET SESSION wsrep_on = 1;
590 SET GLOBAL wsrep_provider_options = 'dbug=';
591 connection node_1;
592 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
593 COMMIT;
594 connection node_1b;
595 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
596 COMMIT;
597 connection node_1a;
598 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
599 connection node_1;
600 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
601 SELECT 'I deadlocked';
602 I deadlocked
603 I deadlocked
604 connection node_1b;
605 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
606 SELECT 'I deadlocked';
607 I deadlocked
608 I deadlocked
609 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
610 EXPECT_1
611 1
612 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
613 EXPECT_2
614 2
615 connection node_2;
616 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
617 EXPECT_1
618 1
619 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
620 EXPECT_2
621 2
622 DROP TABLE IF EXISTS c1, c2;
623 Warnings:
624 Note 1051 Unknown table 'test.c2'
625 DROP TABLE p1, p2;
626 ######################################################################
627 # Test for DROP TABLE IF EXISTS
628 ######################################################################
629 connection node_1;
630 SET SESSION wsrep_sync_wait=0;
631 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
632 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
633 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
634 INSERT INTO c1 VALUES (1,1);
635 ######################################################################
636 #
637 # Scenario #4: DML working on FK parent table tries to replicate, but
638 # fails in certification for earlier DDL on child table
639 # and another temporary table. TMP table should be skipped
640 # but FK child table should be replicated with proper keys
641 #
642 ######################################################################
643 connection node_1;
644 BEGIN;
645 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
646 connection node_2;
647 CREATE TEMPORARY TABLE tmp1 (i int);
648 CREATE TEMPORARY TABLE tmp2 (i int);
649 DROP TABLE IF EXISTS tmp1, c1, tmp2 ;
650 connection node_1a;
651 SET SESSION wsrep_on = 0;
652 SET SESSION wsrep_on = 1;
653 SET GLOBAL wsrep_provider_options = 'dbug=';
654 connection node_1;
655 INSERT INTO p1 VALUES (10, 'TO DEADLOCK');
656 COMMIT;
657 connection node_1a;
658 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
659 connection node_1;
660 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
661 SELECT 'I deadlocked';
662 I deadlocked
663 I deadlocked
664 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
665 EXPECT_1
666 1
667 connection node_2;
668 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
669 EXPECT_1
670 1
671 DROP TABLE IF EXISTS c1;
672 Warnings:
673 Note 1051 Unknown table 'test.c1'
674 DROP TABLE p1;
675 DROP TABLE IF EXISTS tmp1, tmp2;
676 Warnings:
677 Note 1051 Unknown table 'test.tmp1,test.tmp2'
678 ######################################################################
679 # Test for DROP TABLE IF EXISTS nonexisting,
680 ######################################################################
681 ######################################################################
682 #
683 # Scenario #1: DML working on FK parent table BF aborted by DDL
684 # over child table
685 #
686 ######################################################################
687 connection node_1;
688 SET SESSION wsrep_sync_wait=0;
689 FLUSH STATUS;
690 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
691 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
692 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
693 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
694 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
695 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
696 INSERT INTO c1 VALUES (1,1);
697 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
698 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
699 connection node_1;
700 SET AUTOCOMMIT=ON;
701 START TRANSACTION;
702 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
703 connection node_2;
704 SET SESSION wsrep_sync_wait=0;
705 DROP TABLE IF EXISTS nonexisting, c1 ;
706 Warnings:
707 Note 1051 Unknown table 'test.nonexisting'
708 connection node_1;
709 COMMIT;
710 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
711 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
712 EXPECT_1
713 1
714 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
715 EXPECT_2
716 2
717 connection node_2;
718 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
719 EXPECT_1
720 1
721 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
722 EXPECT_2
723 2
724 connection node_1;
725 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
726 INSERT IGNORE INTO c1 VALUES (1,1);
727 ######################################################################
728 #
729 # Scenario #2: DML working on FK parent table tries to replicate, but
730 # fails in certification for earlier DDL on child table
731 #
732 ######################################################################
733 connection node_1;
734 BEGIN;
735 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
736 connection node_2;
737 DROP TABLE IF EXISTS nonexisting, c1 ;
738 Warnings:
739 Note 1051 Unknown table 'test.nonexisting'
740 connection node_1a;
741 SET SESSION wsrep_on = 0;
742 SET SESSION wsrep_on = 1;
743 SET GLOBAL wsrep_provider_options = 'dbug=';
744 connection node_1;
745 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
746 COMMIT;
747 connection node_1a;
748 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
749 connection node_1;
750 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
751 SELECT 'I deadlocked';
752 I deadlocked
753 I deadlocked
754 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
755 EXPECT_1
756 1
757 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
758 EXPECT_2
759 2
760 connection node_2;
761 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
762 EXPECT_1
763 1
764 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
765 EXPECT_2
766 2
767 connection node_1;
768 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
769 INSERT IGNORE INTO c1 VALUES (1,1);
770 ######################################################################
771 #
772 # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
773 # but fails in certification for earlier DDL on child table
774 # which is child to both FK parents
775 #
776 ######################################################################
777 connection node_1;
778 BEGIN;
779 connection node_1b;
780 BEGIN;
781 connection node_1a;
782 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
783 connection node_2;
784 DROP TABLE IF EXISTS nonexisting, c2 ;
785 Warnings:
786 Note 1051 Unknown table 'test.nonexisting'
787 connection node_1a;
788 SET SESSION wsrep_on = 0;
789 SET SESSION wsrep_on = 1;
790 SET GLOBAL wsrep_provider_options = 'dbug=';
791 connection node_1;
792 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
793 COMMIT;
794 connection node_1b;
795 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
796 COMMIT;
797 connection node_1a;
798 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
799 connection node_1;
800 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
801 SELECT 'I deadlocked';
802 I deadlocked
803 I deadlocked
804 connection node_1b;
805 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
806 SELECT 'I deadlocked';
807 I deadlocked
808 I deadlocked
809 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
810 EXPECT_1
811 1
812 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
813 EXPECT_2
814 2
815 connection node_2;
816 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
817 EXPECT_1
818 1
819 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
820 EXPECT_2
821 2
822 DROP TABLE IF EXISTS c1, c2;
823 Warnings:
824 Note 1051 Unknown table 'test.c2'
825 DROP TABLE p1, p2;
826 ######################################################################
827 # Test for DROP TABLE IF EXISTS nonexisting,
828 ######################################################################
829 connection node_1;
830 SET SESSION wsrep_sync_wait=0;
831 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
832 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
833 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
834 INSERT INTO c1 VALUES (1,1);
835 ######################################################################
836 #
837 # Scenario #4: DML working on FK parent table tries to replicate, but
838 # fails in certification for earlier DDL on child table
839 # and another temporary table. TMP table should be skipped
840 # but FK child table should be replicated with proper keys
841 #
842 ######################################################################
843 connection node_1;
844 BEGIN;
845 SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
846 connection node_2;
847 CREATE TEMPORARY TABLE tmp1 (i int);
848 CREATE TEMPORARY TABLE tmp2 (i int);
849 DROP TABLE IF EXISTS nonexisting, tmp1, c1, tmp2 ;
850 Warnings:
851 Note 1051 Unknown table 'test.nonexisting'
852 connection node_1a;
853 SET SESSION wsrep_on = 0;
854 SET SESSION wsrep_on = 1;
855 SET GLOBAL wsrep_provider_options = 'dbug=';
856 connection node_1;
857 INSERT INTO p1 VALUES (10, 'TO DEADLOCK');
858 COMMIT;
859 connection node_1a;
860 SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
861 connection node_1;
862 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
863 SELECT 'I deadlocked';
864 I deadlocked
865 I deadlocked
866 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
867 EXPECT_1
868 1
869 connection node_2;
870 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
871 EXPECT_1
872 1
873 DROP TABLE IF EXISTS c1;
874 Warnings:
875 Note 1051 Unknown table 'test.c1'
876 DROP TABLE p1;
877 DROP TABLE IF EXISTS tmp1, tmp2;
878 Warnings:
879 Note 1051 Unknown table 'test.tmp1,test.tmp2'