Debian Codegraph @

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

raw
1 #
2 # BF-BF conflict on MDL locks between DDL and insert 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 INSERT applies, it
8 # will be granted a MDL lock of type SHARED_READ
9 # for table t1. When resumed, the DROP/ALTER/CREATE/OPTIMIZE
10 # TABLE will 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 t4 and INSERT t1
21 --echo # with foreign key references as below:
22 --echo # - t1<-t2<-t3<-t4
23 --echo #
24
25 #
26 # Setup
27 #
28 --connection node_2
29 SET GLOBAL wsrep_slave_threads=2;
30
31 CREATE TABLE t1 (
32 id INTEGER PRIMARY KEY,
33 f2 INTEGER
34 );
35
36 CREATE TABLE t2 (
37 id INT PRIMARY KEY,
38 t1_id INT NOT NULL,
39 f2 INTEGER NOT NULL,
40 KEY key_t1_id(t1_id),
41 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
42 );
43
44 CREATE TABLE t3 (
45 id INT PRIMARY KEY,
46 t2_id INT NOT NULL,
47 f2 INTEGER NOT NULL,
48 KEY key_t2_id(t2_id),
49 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
50 );
51
52 CREATE TABLE t4 (
53 id INT PRIMARY KEY,
54 t3_id INT NOT NULL,
55 f2 INTEGER NOT NULL,
56 KEY key_t3_id(t3_id),
57 CONSTRAINT key_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
58 );
59
60 INSERT INTO t1 VALUES (1,0);
61 INSERT INTO t1 VALUES (2,0);
62
63 INSERT INTO t2 VALUES (1,1,1234);
64 INSERT INTO t2 VALUES (2,2,1234);
65
66 INSERT INTO t3 VALUES (1,1,1234);
67 INSERT INTO t3 VALUES (2,2,1234);
68
69 INSERT INTO t4 VALUES (1,1,1234);
70 INSERT INTO t4 VALUES (2,2,1234);
71
72
73 --let $fk_parent_query = DROP TABLE t4
74 --let $fk_child_query = INSERT INTO t1 VALUES (3,0)
75 --let $fk_mdl_lock_num = 2
76 --source galera_multi_level_foreign_key_insert.inc
77
78 #
79 # Verify insert and drop table has succeded.
80 #
81 --connection node_2
82 --let $wait_condition = SELECT COUNT(*) = 3 FROM test.t1
83 --source include/wait_condition.inc
84
85 select * from t1;
86 select * from t2;
87 select * from t3;
88 --error ER_NO_SUCH_TABLE
89 select * from t4;
90
91 --connection node_1
92 select * from t1;
93 select * from t2;
94 select * from t3;
95 --error ER_NO_SUCH_TABLE
96 select * from t4;
97
98
99 #
100 # Cleanup
101 #
102 DROP TABLE t3, t2, t1;
103
104
105 --echo #
106 --echo # 2. BF-BF conflict on MDL locks between:
107 --echo # ALTER TABLE t4 (whose parent table are t4 -> t3 -> t2 -> t1), and
108 --echo # INSERT on t1.
109 --echo #
110
111 #
112 # Setup
113 #
114 --connection node_2
115 SET GLOBAL wsrep_slave_threads=2;
116
117 CREATE TABLE t1 (
118 id INTEGER PRIMARY KEY,
119 f2 INTEGER
120 );
121
122 CREATE TABLE t2 (
123 id INT PRIMARY KEY,
124 t1_id INT NOT NULL,
125 f2 INTEGER NOT NULL,
126 KEY key_t1_id(t1_id),
127 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
128 );
129
130 CREATE TABLE t3 (
131 id INT PRIMARY KEY,
132 t2_id INT NOT NULL,
133 f2 INTEGER NOT NULL,
134 KEY key_t2_id(t2_id),
135 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
136 );
137
138 CREATE TABLE t4 (
139 id INT PRIMARY KEY,
140 t3_id INT NOT NULL,
141 f2 INTEGER NOT NULL,
142 KEY key_t3_id(t3_id)
143 );
144
145
146 INSERT INTO t1 VALUES (1,0);
147 INSERT INTO t1 VALUES (2,0);
148
149 INSERT INTO t2 VALUES (1,1,1234);
150 INSERT INTO t2 VALUES (2,2,1234);
151
152 INSERT INTO t3 VALUES (1,1,1234);
153 INSERT INTO t3 VALUES (2,2,1234);
154
155 INSERT INTO t4 VALUES (1,1,1234);
156 INSERT INTO t4 VALUES (2,2,1234);
157
158
159 #
160 # ALTER TABLE t3 and wait for it to reach node_2
161 #
162 --let $fk_parent_query = ALTER TABLE t4 ADD CONSTRAINT key_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
163 #
164 # Issue a INSERT to table that references t1
165 #
166 --let $fk_child_query = INSERT INTO t1 VALUES (3,0)
167 --let $fk_mdl_lock_num = 2
168 --source galera_multi_level_foreign_key_insert.inc
169
170
171 #
172 # Verify insert has succeded.
173 #
174 --connection node_2
175 --let $wait_condition = SELECT COUNT(*) = 3 FROM test.t1
176 --source include/wait_condition.inc
177
178 select * from t1;
179 select * from t2;
180 select * from t3;
181
182 --connection node_1
183 select * from t1;
184 select * from t2;
185 select * from t3;
186
187
188 #
189 # Cleanup
190 #
191 DROP TABLE t4, t3, t2, t1;
192
193
194 --echo #
195 --echo # 3. BF-BF conflict on MDL locks between:
196 --echo # CREATE TABLE t3 (whose parent table are t4 -> t3 -> t2 -> t1), and
197 --echo # INSERT on t1.
198 --echo #
199
200 #
201 # Setup
202 #
203 --connection node_2
204 SET GLOBAL wsrep_slave_threads=2;
205
206 CREATE TABLE t1 (
207 id INTEGER PRIMARY KEY,
208 f2 INTEGER
209 );
210
211 CREATE TABLE t2 (
212 id INT PRIMARY KEY,
213 t1_id INT NOT NULL,
214 f2 INTEGER NOT NULL,
215 KEY key_t1_id(t1_id),
216 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
217 );
218
219 CREATE TABLE t3 (
220 id INT PRIMARY KEY,
221 t2_id INT NOT NULL,
222 f2 INTEGER NOT NULL,
223 KEY key_t2_id(t2_id),
224 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
225 );
226
227 INSERT INTO t1 VALUES (1,0);
228 INSERT INTO t1 VALUES (2,0);
229
230 INSERT INTO t2 VALUES (1,1,1234);
231 INSERT INTO t2 VALUES (2,2,1234);
232
233 INSERT INTO t3 VALUES (1,1,1234);
234 INSERT INTO t3 VALUES (2,2,1234);
235
236
237 --let $fk_parent_query = CREATE TABLE t4 (id INT PRIMARY KEY, t3_id INT NOT NULL, f2 INTEGER NOT NULL, KEY key_t3_id(t3_id), CONSTRAINT key_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE)
238 --let $fk_child_query = INSERT INTO t1 VALUES (3,0)
239 --let $fk_mdl_lock_num = 2
240 --source galera_multi_level_foreign_key_insert.inc
241
242
243 #
244 # Verify insert has succeded.
245 #
246 --connection node_2
247 --let $wait_condition = SELECT COUNT(*) = 3 FROM test.t1
248 --source include/wait_condition.inc
249
250 select * from t1;
251 select * from t2;
252 select * from t3;
253
254 --connection node_1
255 select * from t1;
256 select * from t2;
257 select * from t3;
258
259
260 #
261 # Cleanup
262 #
263 DROP TABLE t4, t3, t2, t1;
264
265
266 --echo #
267 --echo # 4. BF-BF conflict on MDL locks between:
268 --echo # OPTIMIZE TABLE t2 (whose parent table are t2 -> t1), and
269 --echo # INSERT t1.
270 --echo #
271
272 #
273 # Setup
274 #
275 --connection node_2
276 SET GLOBAL wsrep_slave_threads=2;
277
278 CREATE TABLE t1 (
279 id INTEGER PRIMARY KEY,
280 f2 INTEGER
281 );
282
283 CREATE TABLE t2 (
284 id INT PRIMARY KEY,
285 t1_id INT NOT NULL,
286 f2 INTEGER NOT NULL,
287 KEY key_t1_id(t1_id),
288 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
289 );
290
291 CREATE TABLE t3 (
292 id INT PRIMARY KEY,
293 t2_id INT NOT NULL,
294 f2 INTEGER NOT NULL,
295 KEY key_t2_id(t2_id),
296 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
297 );
298
299
300 INSERT INTO t1 VALUES (1,0);
301 INSERT INTO t1 VALUES (2,0);
302
303 INSERT INTO t2 VALUES (1,1,1234);
304 INSERT INTO t2 VALUES (2,2,1234);
305
306 INSERT INTO t3 VALUES (1,1,1234);
307 INSERT INTO t3 VALUES (2,2,1234);
308
309
310 --let $fk_parent_query = OPTIMIZE TABLE t3
311 --let $fk_child_query = INSERT INTO t1 VALUES (3,0)
312 --let $fk_mdl_lock_num = 2
313 --source galera_multi_level_foreign_key_insert.inc
314
315
316 #
317 # Verify insert has succeded.
318 #
319 --connection node_2
320 --let $wait_condition = SELECT COUNT(*) = 3 FROM test.t1
321 --source include/wait_condition.inc
322
323 select * from t1;
324 select * from t2;
325
326 --connection node_1
327 select * from t1;
328 select * from t2;
329
330
331 #
332 # Cleanup
333 #
334 DROP TABLE t3, t2, t1;