Debian Codegraph @

mysql-test/suite/galera/t/galera_multi_level_fk_ddl_delete.test

raw
1 #
2 # BF-BF conflict on MDL locks between DDL and delete query
3 # when multi-level foreign key like t3 -> t2 -> t1
4 # are present.
5 #
6 # If bug is present, expect the wait condition
7 # to timeout and when the DELETE applies, it
8 # will be granted a MDL lock of type SHARED_READ
9 # for table t1. When resumed, the DROP TABLE will
10 # also try to MDL lock t1, causing a BF-BF conflict
11 # on that MDL lock.
12
13 --source include/galera_cluster.inc
14 --source include/have_innodb.inc
15 --source include/have_debug.inc
16 --source include/have_debug_sync.inc
17 --source include/have_perfschema.inc
18
19 --echo #
20 --echo # 1. BF-BF conflict on MDL locks between: DROP TABLE t6 and DELETE on t1
21 --echo # with foreign key references as below:
22 --echo # - t1<-t2<-t3<-t4
23 --echo # - t3<-t5
24 --echo # - t2<-t6
25 --echo #
26
27
28 #
29 # Setup
30 #
31 --connection node_2
32 SET GLOBAL wsrep_slave_threads=2;
33
34 CREATE TABLE t1 (
35 id INTEGER PRIMARY KEY,
36 f2 INTEGER
37 );
38
39 CREATE TABLE t2 (
40 id INT PRIMARY KEY,
41 t1_id INT NOT NULL,
42 t5_id INT NOT NULL,
43 f2 INTEGER NOT NULL,
44 KEY key_t1_id(t1_id),
45 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE,
46 KEY key_t5_id(t5_id)
47 );
48
49 CREATE TABLE t3 (
50 id INT PRIMARY KEY,
51 t2_id INT NOT NULL,
52 f2 INTEGER NOT NULL,
53 KEY key_t2_id(t2_id),
54 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
55 );
56
57 CREATE TABLE t4 (
58 id INT PRIMARY KEY,
59 t3_id INT NOT NULL,
60 f2 INTEGER NOT NULL,
61 KEY key_t3_id(t3_id),
62 CONSTRAINT key_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
63 );
64
65 CREATE TABLE t5 (
66 id INT PRIMARY KEY,
67 t3_id_1 INT NOT NULL,
68 f2 INTEGER NOT NULL,
69 KEY key_t3_id_1(t3_id_1),
70 CONSTRAINT key_t3_id_1 FOREIGN KEY (t3_id_1) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
71 );
72
73 CREATE TABLE t6 (
74 id INT PRIMARY KEY,
75 t2_id INT NOT NULL,
76 f2 INTEGER NOT NULL,
77 KEY key_t2_id_1(t2_id),
78 CONSTRAINT key_t2_id_1 FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
79 );
80
81
82 INSERT INTO t1 VALUES (1,0);
83 INSERT INTO t1 VALUES (2,0);
84 INSERT INTO t1 VALUES (3,0);
85
86 INSERT INTO t2 VALUES (1,1,1,1234);
87 INSERT INTO t2 VALUES (2,2,2,1234);
88
89 INSERT INTO t3 VALUES (1,1,1234);
90 INSERT INTO t3 VALUES (2,2,1234);
91
92 INSERT INTO t4 VALUES (1,1,1234);
93 INSERT INTO t4 VALUES (2,2,1234);
94
95 INSERT INTO t5 VALUES (1,1,1234);
96 INSERT INTO t5 VALUES (2,2,1234);
97
98 ALTER TABLE t2 ADD CONSTRAINT key_t5_id FOREIGN KEY (t5_id)
99 REFERENCES t5 (id) ON UPDATE CASCADE ON DELETE CASCADE;
100
101 --connection node_1
102 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = "t2" AND CONSTRAINT_TYPE = "FOREIGN KEY" AND CONSTRAINT_NAME="key_t5_id"
103 --source include/wait_condition.inc
104
105
106 --let $fk_parent_query = DROP TABLE t6
107 --let $fk_child_query = DELETE FROM t1 WHERE id = 3
108 --let $fk_mdl_lock_num = 5
109 --source galera_multi_level_foreign_key.inc
110
111
112 #
113 # Verify Foreign key for referenced table added.
114 #
115 --connection node_1
116 --let assert_text= Foreign key referenced table found: 4 tables
117 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
118 --let assert_count= 4
119 --let assert_select= Foreign key referenced table found:
120 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
121 --source include/assert_grep.inc
122
123 --let assert_text= Foreign key referenced table found: test.t2
124 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
125 --let assert_count= 1
126 --let assert_select= Foreign key referenced table found: test.t2
127 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
128 --source include/assert_grep.inc
129
130 --let assert_text= Foreign key referenced table found: test.t3
131 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
132 --let assert_count= 1
133 --let assert_select= Foreign key referenced table found: test.t3
134 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
135 --source include/assert_grep.inc
136
137 --let assert_text= Foreign key referenced table found: test.t4
138 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
139 --let assert_count= 1
140 --let assert_select= Foreign key referenced table found: test.t4
141 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
142 --source include/assert_grep.inc
143
144 --let assert_text= Foreign key referenced table found: test.t5
145 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
146 --let assert_count= 1
147 --let assert_select= Foreign key referenced table found: test.t5
148 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
149 --source include/assert_grep.inc
150
151
152 #
153 # Verify delete and drop table has succeded.
154 #
155 --connection node_2
156 --let $wait_condition = SELECT COUNT(*) = 2 FROM test.t1
157 --source include/wait_condition.inc
158
159 select * from t1;
160 select * from t2;
161 select * from t3;
162 select * from t4;
163 select * from t5;
164 --error ER_NO_SUCH_TABLE
165 select * from t6;
166
167 --connection node_1
168 select * from t1;
169 select * from t2;
170 select * from t3;
171 select * from t4;
172 select * from t5;
173 --error ER_NO_SUCH_TABLE
174 select * from t6;
175
176
177 #
178 # Cleanup
179 #
180 ALTER TABLE t2 DROP FOREIGN KEY key_t5_id;
181 DROP TABLE t5, t4, t3, t2, t1;
182
183
184 --echo #
185 --echo # 2. BF-BF conflict on MDL locks between:
186 --echo # ALTER TABLE t3 (whose parent table are t3 -> t2 -> t1), and
187 --echo # DELETE on t1 with t2 referencing t1, and t3 referencing t2.
188 --echo #
189
190 #
191 # Setup
192 #
193 --connection node_2
194 SET GLOBAL wsrep_slave_threads=2;
195
196 CREATE TABLE t1 (
197 id INTEGER PRIMARY KEY,
198 f2 INTEGER
199 );
200
201 CREATE TABLE t2 (
202 id INT PRIMARY KEY,
203 t1_id INT NOT NULL,
204 f2 INTEGER NOT NULL,
205 KEY key_t1_id(t1_id),
206 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
207 );
208
209 CREATE TABLE t3 (
210 id INT PRIMARY KEY,
211 t2_id INT NOT NULL,
212 f2 INTEGER NOT NULL,
213 KEY key_t2_id(t2_id)
214 );
215
216 INSERT INTO t1 VALUES (1,0);
217 INSERT INTO t1 VALUES (2,0);
218 INSERT INTO t1 VALUES (3,0);
219
220 INSERT INTO t2 VALUES (1,1,1234);
221 INSERT INTO t2 VALUES (2,2,1234);
222
223
224 #
225 # ALTER TABLE t3 and wait for it to reach node_2
226 #
227 --let $fk_parent_query = ALTER TABLE t3 ADD CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
228 #
229 # Issue a DELETE to table that references t1
230 #
231 --let $fk_child_query = DELETE FROM t1 WHERE id = 3
232 --let $fk_mdl_lock_num = 3
233 --source galera_multi_level_foreign_key.inc
234
235
236 #
237 # Verify Foreign key for referenced table added.
238 #
239 --connection node_1
240 --let assert_text= Foreign key referenced table found: 2 tables
241 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
242 --let assert_count= 6
243 --let assert_select= Foreign key referenced table found:
244 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
245 --source include/assert_grep.inc
246
247 --let assert_text= Foreign key referenced table found: test.t2
248 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
249 --let assert_count= 2
250 --let assert_select= Foreign key referenced table found: test.t2
251 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
252 --source include/assert_grep.inc
253
254 --let assert_text= Foreign key referenced table found: test.t3
255 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
256 --let assert_count= 2
257 --let assert_select= Foreign key referenced table found: test.t3
258 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
259 --source include/assert_grep.inc
260
261
262 #
263 # Verify delete and alter table has succeded.
264 #
265 --connection node_2
266 --let $wait_condition = SELECT COUNT(*) = 2 FROM test.t1
267 --source include/wait_condition.inc
268
269 select * from t1;
270 select * from t2;
271 select * from t3;
272
273 --connection node_1
274 select * from t1;
275 select * from t2;
276 select * from t3;
277
278
279 #
280 # Cleanup
281 #
282 DROP TABLE t3, t2, t1;
283
284
285 --echo #
286 --echo # 3. BF-BF conflict on MDL locks between:
287 --echo # CREATE TABLE t3 (whose parent table are t3 -> t2 -> t1), and
288 --echo # DELETE on t1 with t2 referencing t1, and t3 referencing t2.
289 --echo #
290
291 #
292 # Setup
293 #
294 --connection node_2
295 SET GLOBAL wsrep_slave_threads=2;
296
297 CREATE TABLE t1 (
298 id INTEGER PRIMARY KEY,
299 f2 INTEGER
300 );
301
302 CREATE TABLE t2 (
303 id INT PRIMARY KEY,
304 t1_id INT NOT NULL,
305 f2 INTEGER NOT NULL,
306 KEY key_t1_id(t1_id),
307 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
308 );
309
310
311 INSERT INTO t1 VALUES (1,0);
312 INSERT INTO t1 VALUES (2,0);
313 INSERT INTO t1 VALUES (3,0);
314
315 INSERT INTO t2 VALUES (1,1,1234);
316 INSERT INTO t2 VALUES (2,2,1234);
317
318
319 --let $fk_parent_query = CREATE TABLE t3 (id INT PRIMARY KEY, t2_id INT NOT NULL, f2 INTEGER NOT NULL, KEY key_t2_id(t2_id), CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE)
320 --let $fk_child_query = DELETE FROM t1 WHERE id = 3
321 --let $fk_mdl_lock_num = 3
322 --source galera_multi_level_foreign_key.inc
323
324
325 #
326 # Verify Foreign key for referenced table added.
327 #
328 --connection node_1
329 --let assert_text= Foreign key referenced table found: 2 tables
330 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
331 --let assert_count= 8
332 --let assert_select= Foreign key referenced table found:
333 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
334 --source include/assert_grep.inc
335
336 --let assert_text= Foreign key referenced table found: test.t2
337 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
338 --let assert_count= 3
339 --let assert_select= Foreign key referenced table found: test.t2
340 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
341 --source include/assert_grep.inc
342
343 --let assert_text= Foreign key referenced table found: test.t3
344 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
345 --let assert_count= 3
346 --let assert_select= Foreign key referenced table found: test.t3
347 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
348 --source include/assert_grep.inc
349
350
351 #
352 # Verify delete and create table has succeded.
353 #
354 --connection node_2
355 --let $wait_condition = SELECT COUNT(*) = 2 FROM test.t1
356 --source include/wait_condition.inc
357
358 select * from t1;
359 select * from t2;
360 select * from t3;
361
362 --connection node_1
363 select * from t1;
364 select * from t2;
365 select * from t3;
366
367
368 #
369 # Cleanup
370 #
371 DROP TABLE t3, t2, t1;
372
373
374 --echo #
375 --echo # 4. BF-BF conflict on MDL locks between:
376 --echo # OPTIMIZE TABLE t2 (whose parent table are t2 -> t1), and
377 --echo # DELETE on t1.
378 --echo #
379
380 #
381 # Setup
382 #
383 --connection node_2
384 SET GLOBAL wsrep_slave_threads=2;
385
386 CREATE TABLE t1 (
387 id INTEGER PRIMARY KEY,
388 f2 INTEGER
389 );
390
391 CREATE TABLE t2 (
392 id INT PRIMARY KEY,
393 t1_id INT NOT NULL,
394 f2 INTEGER NOT NULL,
395 KEY key_t1_id(t1_id),
396 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
397 );
398
399
400 INSERT INTO t1 VALUES (1,0);
401 INSERT INTO t1 VALUES (2,0);
402 INSERT INTO t1 VALUES (3,0);
403
404 INSERT INTO t2 VALUES (1,1,1234);
405 INSERT INTO t2 VALUES (2,2,1234);
406
407
408 --let $fk_parent_query = OPTIMIZE TABLE t2
409 --let $fk_child_query = DELETE FROM t1 WHERE id = 3
410 --let $fk_mdl_lock_num = 2
411 --source galera_multi_level_foreign_key.inc
412
413
414 #
415 # Verify Foreign key for referenced table added.
416 #
417 --connection node_1
418 --let assert_text= Foreign key referenced table found: 1 tables
419 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
420 --let assert_count= 9
421 --let assert_select= Foreign key referenced table found:
422 --let $assert_only_after = CURRENT_TEST:
423 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
424 --source include/assert_grep.inc
425
426 --let assert_text= Foreign key referenced table found: test.t2
427 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
428 --let assert_count= 4
429 --let assert_select= Foreign key referenced table found: test.t2
430 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_delete
431 --source include/assert_grep.inc
432
433
434 #
435 # Verify delete and create table has succeded.
436 #
437 --connection node_2
438 --let $wait_condition = SELECT COUNT(*) = 2 FROM test.t1
439 --source include/wait_condition.inc
440
441
442 select * from t1;
443 select * from t2;
444
445 --connection node_1
446 select * from t1;
447 select * from t2;
448
449
450 #
451 # Cleanup
452 #
453 DROP TABLE t2, t1;