Debian Codegraph @

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

raw
1 #
2 # BF-BF conflict on MDL locks between DDL and update 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 UPDATE 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 UPDATE 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
85 INSERT INTO t2 VALUES (1,1,1,1234);
86 INSERT INTO t2 VALUES (2,2,2,1234);
87
88 INSERT INTO t3 VALUES (1,1,1234);
89 INSERT INTO t3 VALUES (2,2,1234);
90
91 INSERT INTO t4 VALUES (1,1,1234);
92 INSERT INTO t4 VALUES (2,2,1234);
93
94 INSERT INTO t5 VALUES (1,1,1234);
95 INSERT INTO t5 VALUES (2,2,1234);
96
97 ALTER TABLE t2 ADD CONSTRAINT key_t5_id FOREIGN KEY (t5_id)
98 REFERENCES t5 (id) ON UPDATE CASCADE ON DELETE CASCADE;
99
100 --connection node_1
101 --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"
102 --source include/wait_condition.inc
103
104
105 --let $fk_parent_query = DROP TABLE t6
106 --let $fk_child_query = UPDATE t1 SET f2 = 1 WHERE id=2
107 --let $fk_mdl_lock_num = 5
108 --source galera_multi_level_foreign_key.inc
109
110
111 #
112 # Verify Foreign key for referenced table added.
113 #
114 --connection node_1
115 --let assert_text= Foreign key referenced table found: 4 tables
116 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
117 --let assert_count= 4
118 --let assert_select= Foreign key referenced table found:
119 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
120 --source include/assert_grep.inc
121
122 --let assert_text= Foreign key referenced table found: test.t2
123 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
124 --let assert_count= 1
125 --let assert_select= Foreign key referenced table found: test.t2
126 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
127 --source include/assert_grep.inc
128
129 --let assert_text= Foreign key referenced table found: test.t3
130 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
131 --let assert_count= 1
132 --let assert_select= Foreign key referenced table found: test.t3
133 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
134 --source include/assert_grep.inc
135
136 --let assert_text= Foreign key referenced table found: test.t4
137 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
138 --let assert_count= 1
139 --let assert_select= Foreign key referenced table found: test.t4
140 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
141 --source include/assert_grep.inc
142
143 --let assert_text= Foreign key referenced table found: test.t5
144 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
145 --let assert_count= 1
146 --let assert_select= Foreign key referenced table found: test.t5
147 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
148 --source include/assert_grep.inc
149
150
151 #
152 # Verify update and drop table has succeded.
153 #
154 --connection node_2
155 --let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1 where id=2 and f2=1;
156 --source include/wait_condition.inc
157
158 select * from t1;
159 select * from t2;
160 select * from t3;
161 select * from t4;
162 select * from t5;
163 --error ER_NO_SUCH_TABLE
164 select * from t6;
165
166 --connection node_1
167 select * from t1;
168 select * from t2;
169 select * from t3;
170 select * from t4;
171 select * from t5;
172 --error ER_NO_SUCH_TABLE
173 select * from t6;
174
175
176 #
177 # Cleanup
178 #
179 ALTER TABLE t2 DROP FOREIGN KEY key_t5_id;
180 DROP TABLE t5, t4, t3, t2, t1;
181
182
183 --echo #
184 --echo # 2. BF-BF conflict on MDL locks between:
185 --echo # ALTER TABLE t3 (whose parent table are t3 -> t2 -> t1), and
186 --echo # UPDATE on t1 with t2 referencing t1, and t3 referencing t2.
187 --echo #
188
189 #
190 # Setup
191 #
192 --connection node_2
193 SET GLOBAL wsrep_slave_threads=2;
194
195 CREATE TABLE t1 (
196 id INTEGER PRIMARY KEY,
197 f2 INTEGER
198 );
199
200 CREATE TABLE t2 (
201 id INT PRIMARY KEY,
202 t1_id INT NOT NULL,
203 f2 INTEGER NOT NULL,
204 KEY key_t1_id(t1_id),
205 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
206 );
207
208 CREATE TABLE t3 (
209 id INT PRIMARY KEY,
210 t2_id INT NOT NULL,
211 f2 INTEGER NOT NULL,
212 KEY key_t2_id(t2_id)
213 );
214
215 INSERT INTO t1 VALUES (1,0);
216 INSERT INTO t1 VALUES (2,0);
217
218 INSERT INTO t2 VALUES (1,1,1234);
219 INSERT INTO t2 VALUES (2,2,1234);
220
221
222 #
223 # ALTER TABLE t3 and wait for it to reach node_2
224 #
225 --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
226 #
227 # Issue a UPDATE to table that references t1
228 # Notice that we update field f2, not the primary key,
229 # and not foreign key. Bug does not manifest if we update
230 # one of those fields (because FK keys appended in those cases).
231 #
232 --let $fk_child_query = UPDATE t1 SET f2 = 1 WHERE id=2
233 --let $fk_mdl_lock_num = 3
234 --source galera_multi_level_foreign_key.inc
235
236
237 #
238 # Verify Foreign key for referenced table added.
239 #
240 --connection node_1
241 --let assert_text= Foreign key referenced table found: 2 tables
242 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
243 --let assert_count= 6
244 --let assert_select= Foreign key referenced table found:
245 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
246 --source include/assert_grep.inc
247
248 --let assert_text= Foreign key referenced table found: test.t2
249 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
250 --let assert_count= 2
251 --let assert_select= Foreign key referenced table found: test.t2
252 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
253 --source include/assert_grep.inc
254
255 --let assert_text= Foreign key referenced table found: test.t3
256 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
257 --let assert_count= 2
258 --let assert_select= Foreign key referenced table found: test.t3
259 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
260 --source include/assert_grep.inc
261
262
263 #
264 # Verify update and drop table has succeded.
265 #
266 --connection node_2
267 --let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1 where id=2 and f2=1;
268 --source include/wait_condition.inc
269
270 select * from t1;
271 select * from t2;
272 select * from t3;
273
274 --connection node_1
275 select * from t1;
276 select * from t2;
277 select * from t3;
278
279
280 #
281 # Cleanup
282 #
283 DROP TABLE t3, t2, t1;
284
285
286 --echo #
287 --echo # 3. BF-BF conflict on MDL locks between:
288 --echo # CREATE TABLE t3 (whose parent table are t3 -> t2 -> t1), and
289 --echo # UPDATE on t1 with t2 referencing t1, and t3 referencing t2.
290 --echo #
291
292 #
293 # Setup
294 #
295 --connection node_2
296 SET GLOBAL wsrep_slave_threads=2;
297
298 CREATE TABLE t1 (
299 id INTEGER PRIMARY KEY,
300 f2 INTEGER
301 );
302
303 CREATE TABLE t2 (
304 id INT PRIMARY KEY,
305 t1_id INT NOT NULL,
306 f2 INTEGER NOT NULL,
307 KEY key_t1_id(t1_id),
308 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
309 );
310
311
312 INSERT INTO t1 VALUES (1,0);
313 INSERT INTO t1 VALUES (2,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 = UPDATE t1 SET f2 = 1 WHERE id=2
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:
334 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
335 --source include/assert_grep.inc
336
337 --let assert_text= Foreign key referenced table found: test.t2
338 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
339 --let assert_count= 3
340 --let assert_select= Foreign key referenced table found: test.t2
341 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
342 --source include/assert_grep.inc
343
344 --let assert_text= Foreign key referenced table found: test.t3
345 --let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err
346 --let assert_count= 3
347 --let assert_select= Foreign key referenced table found: test.t3
348 --let assert_only_after= CURRENT_TEST: galera.galera_multi_level_fk_ddl_update
349 --source include/assert_grep.inc
350
351
352 #
353 # Verify update and drop table has succeded.
354 #
355 --connection node_2
356 --let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1 where id=2 and f2=1;
357 --source include/wait_condition.inc
358
359 select * from t1;
360 select * from t2;
361 select * from t3;
362
363 --connection node_1
364 select * from t1;
365 select * from t2;
366 select * from t3;
367
368
369 #
370 # Cleanup
371 #
372 DROP TABLE t3, t2, t1;
373
374
375 --echo #
376 --echo # 4. BF-BF conflict on MDL locks between:
377 --echo # OPTIMIZE TABLE t2 (whose parent table are t2 -> t1), and
378 --echo # UPDATE on t1.
379 --echo #
380
381 #
382 # Setup
383 #
384 --connection node_2
385 SET GLOBAL wsrep_slave_threads=2;
386
387 CREATE TABLE t1 (
388 id INTEGER PRIMARY KEY,
389 f2 INTEGER
390 );
391
392 CREATE TABLE t2 (
393 id INT PRIMARY KEY,
394 t1_id INT NOT NULL,
395 f2 INTEGER NOT NULL,
396 KEY key_t1_id(t1_id),
397 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
398 );
399
400
401 INSERT INTO t1 VALUES (1,0);
402 INSERT INTO t1 VALUES (2,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 = UPDATE t1 SET f2 = 1 WHERE id=2
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_update
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_update
431 --source include/assert_grep.inc
432
433
434 #
435 # Verify update has succeded.
436 #
437 --connection node_2
438 --let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1 where id=2 and f2=1;
439 --source include/wait_condition.inc
440
441 select * from t1;
442 select * from t2;
443
444 --connection node_1
445 select * from t1;
446 select * from t2;
447
448
449 #
450 # Cleanup
451 #
452 DROP TABLE t2, t1;