Debian Codegraph @

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

raw
1 connection node_2;
2 connection node_1;
3 #
4 # 1. BF-BF conflict on MDL locks between: DROP TABLE t6 and DELETE on t1
5 # with foreign key references as below:
6 # - t1<-t2<-t3<-t4
7 # - t3<-t5
8 # - t2<-t6
9 #
10 connection node_2;
11 SET GLOBAL wsrep_slave_threads=2;
12 CREATE TABLE t1 (
13 id INTEGER PRIMARY KEY,
14 f2 INTEGER
15 );
16 CREATE TABLE t2 (
17 id INT PRIMARY KEY,
18 t1_id INT NOT NULL,
19 t5_id INT NOT NULL,
20 f2 INTEGER NOT NULL,
21 KEY key_t1_id(t1_id),
22 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE,
23 KEY key_t5_id(t5_id)
24 );
25 CREATE TABLE t3 (
26 id INT PRIMARY KEY,
27 t2_id INT NOT NULL,
28 f2 INTEGER NOT NULL,
29 KEY key_t2_id(t2_id),
30 CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
31 );
32 CREATE TABLE t4 (
33 id INT PRIMARY KEY,
34 t3_id INT NOT NULL,
35 f2 INTEGER NOT NULL,
36 KEY key_t3_id(t3_id),
37 CONSTRAINT key_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
38 );
39 CREATE TABLE t5 (
40 id INT PRIMARY KEY,
41 t3_id_1 INT NOT NULL,
42 f2 INTEGER NOT NULL,
43 KEY key_t3_id_1(t3_id_1),
44 CONSTRAINT key_t3_id_1 FOREIGN KEY (t3_id_1) REFERENCES t3 (id) ON UPDATE CASCADE ON DELETE CASCADE
45 );
46 CREATE TABLE t6 (
47 id INT PRIMARY KEY,
48 t2_id INT NOT NULL,
49 f2 INTEGER NOT NULL,
50 KEY key_t2_id_1(t2_id),
51 CONSTRAINT key_t2_id_1 FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE
52 );
53 INSERT INTO t1 VALUES (1,0);
54 INSERT INTO t1 VALUES (2,0);
55 INSERT INTO t1 VALUES (3,0);
56 INSERT INTO t2 VALUES (1,1,1,1234);
57 INSERT INTO t2 VALUES (2,2,2,1234);
58 INSERT INTO t3 VALUES (1,1,1234);
59 INSERT INTO t3 VALUES (2,2,1234);
60 INSERT INTO t4 VALUES (1,1,1234);
61 INSERT INTO t4 VALUES (2,2,1234);
62 INSERT INTO t5 VALUES (1,1,1234);
63 INSERT INTO t5 VALUES (2,2,1234);
64 ALTER TABLE t2 ADD CONSTRAINT key_t5_id FOREIGN KEY (t5_id)
65 REFERENCES t5 (id) ON UPDATE CASCADE ON DELETE CASCADE;
66 connection node_1;
67 connection node_2;
68 SET GLOBAL DEBUG_DBUG = '+d,sync.wsrep_apply_toi';
69 connection node_1;
70 DROP TABLE t6;
71 connection node_2;
72 SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_toi_reached";
73 SET SESSION wsrep_sync_wait = 0;
74 connection node_1;
75 SET GLOBAL DEBUG_DBUG = '+d,wsrep_print_foreign_keys_table';
76 START TRANSACTION;
77 DELETE FROM t1 WHERE id = 3;
78 COMMIT;
79 connection node_2;
80 SET GLOBAL DEBUG_DBUG = '-d,sync.wsrep_apply_toi';
81 SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_toi";
82 SET DEBUG_SYNC = 'RESET';
83 SET GLOBAL DEBUG_DBUG = "";
84 SET GLOBAL wsrep_slave_threads=DEFAULT;
85 connection node_1;
86 SET DEBUG_SYNC = 'RESET';
87 SET GLOBAL DEBUG_DBUG = "";
88 SET GLOBAL wsrep_slave_threads=DEFAULT;
89 connection node_1;
90 include/assert_grep.inc [Foreign key referenced table found: 4 tables]
91 include/assert_grep.inc [Foreign key referenced table found: test.t2]
92 include/assert_grep.inc [Foreign key referenced table found: test.t3]
93 include/assert_grep.inc [Foreign key referenced table found: test.t4]
94 include/assert_grep.inc [Foreign key referenced table found: test.t5]
95 connection node_2;
96 select * from t1;
97 id f2
98 1 0
99 2 0
100 select * from t2;
101 id t1_id t5_id f2
102 1 1 1 1234
103 2 2 2 1234
104 select * from t3;
105 id t2_id f2
106 1 1 1234
107 2 2 1234
108 select * from t4;
109 id t3_id f2
110 1 1 1234
111 2 2 1234
112 select * from t5;
113 id t3_id_1 f2
114 1 1 1234
115 2 2 1234
116 select * from t6;
117 ERROR 42S02: Table 'test.t6' doesn't exist
118 connection node_1;
119 select * from t1;
120 id f2
121 1 0
122 2 0
123 select * from t2;
124 id t1_id t5_id f2
125 1 1 1 1234
126 2 2 2 1234
127 select * from t3;
128 id t2_id f2
129 1 1 1234
130 2 2 1234
131 select * from t4;
132 id t3_id f2
133 1 1 1234
134 2 2 1234
135 select * from t5;
136 id t3_id_1 f2
137 1 1 1234
138 2 2 1234
139 select * from t6;
140 ERROR 42S02: Table 'test.t6' doesn't exist
141 ALTER TABLE t2 DROP FOREIGN KEY key_t5_id;
142 DROP TABLE t5, t4, t3, t2, t1;
143 #
144 # 2. BF-BF conflict on MDL locks between:
145 # ALTER TABLE t3 (whose parent table are t3 -> t2 -> t1), and
146 # DELETE on t1 with t2 referencing t1, and t3 referencing t2.
147 #
148 connection node_2;
149 SET GLOBAL wsrep_slave_threads=2;
150 CREATE TABLE t1 (
151 id INTEGER PRIMARY KEY,
152 f2 INTEGER
153 );
154 CREATE TABLE t2 (
155 id INT PRIMARY KEY,
156 t1_id INT NOT NULL,
157 f2 INTEGER NOT NULL,
158 KEY key_t1_id(t1_id),
159 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
160 );
161 CREATE TABLE t3 (
162 id INT PRIMARY KEY,
163 t2_id INT NOT NULL,
164 f2 INTEGER NOT NULL,
165 KEY key_t2_id(t2_id)
166 );
167 INSERT INTO t1 VALUES (1,0);
168 INSERT INTO t1 VALUES (2,0);
169 INSERT INTO t1 VALUES (3,0);
170 INSERT INTO t2 VALUES (1,1,1234);
171 INSERT INTO t2 VALUES (2,2,1234);
172 connection node_2;
173 SET GLOBAL DEBUG_DBUG = '+d,sync.wsrep_apply_toi';
174 connection node_1;
175 ALTER TABLE t3 ADD CONSTRAINT key_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (id) ON UPDATE CASCADE ON DELETE CASCADE;
176 connection node_2;
177 SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_toi_reached";
178 SET SESSION wsrep_sync_wait = 0;
179 connection node_1;
180 SET GLOBAL DEBUG_DBUG = '+d,wsrep_print_foreign_keys_table';
181 START TRANSACTION;
182 DELETE FROM t1 WHERE id = 3;
183 COMMIT;
184 connection node_2;
185 SET GLOBAL DEBUG_DBUG = '-d,sync.wsrep_apply_toi';
186 SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_toi";
187 SET DEBUG_SYNC = 'RESET';
188 SET GLOBAL DEBUG_DBUG = "";
189 SET GLOBAL wsrep_slave_threads=DEFAULT;
190 connection node_1;
191 SET DEBUG_SYNC = 'RESET';
192 SET GLOBAL DEBUG_DBUG = "";
193 SET GLOBAL wsrep_slave_threads=DEFAULT;
194 connection node_1;
195 include/assert_grep.inc [Foreign key referenced table found: 2 tables]
196 include/assert_grep.inc [Foreign key referenced table found: test.t2]
197 include/assert_grep.inc [Foreign key referenced table found: test.t3]
198 connection node_2;
199 select * from t1;
200 id f2
201 1 0
202 2 0
203 select * from t2;
204 id t1_id f2
205 1 1 1234
206 2 2 1234
207 select * from t3;
208 id t2_id f2
209 connection node_1;
210 select * from t1;
211 id f2
212 1 0
213 2 0
214 select * from t2;
215 id t1_id f2
216 1 1 1234
217 2 2 1234
218 select * from t3;
219 id t2_id f2
220 DROP TABLE t3, t2, t1;
221 #
222 # 3. BF-BF conflict on MDL locks between:
223 # CREATE TABLE t3 (whose parent table are t3 -> t2 -> t1), and
224 # DELETE on t1 with t2 referencing t1, and t3 referencing t2.
225 #
226 connection node_2;
227 SET GLOBAL wsrep_slave_threads=2;
228 CREATE TABLE t1 (
229 id INTEGER PRIMARY KEY,
230 f2 INTEGER
231 );
232 CREATE TABLE t2 (
233 id INT PRIMARY KEY,
234 t1_id INT NOT NULL,
235 f2 INTEGER NOT NULL,
236 KEY key_t1_id(t1_id),
237 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
238 );
239 INSERT INTO t1 VALUES (1,0);
240 INSERT INTO t1 VALUES (2,0);
241 INSERT INTO t1 VALUES (3,0);
242 INSERT INTO t2 VALUES (1,1,1234);
243 INSERT INTO t2 VALUES (2,2,1234);
244 connection node_2;
245 SET GLOBAL DEBUG_DBUG = '+d,sync.wsrep_apply_toi';
246 connection node_1;
247 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);
248 connection node_2;
249 SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_toi_reached";
250 SET SESSION wsrep_sync_wait = 0;
251 connection node_1;
252 SET GLOBAL DEBUG_DBUG = '+d,wsrep_print_foreign_keys_table';
253 START TRANSACTION;
254 DELETE FROM t1 WHERE id = 3;
255 COMMIT;
256 connection node_2;
257 SET GLOBAL DEBUG_DBUG = '-d,sync.wsrep_apply_toi';
258 SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_toi";
259 SET DEBUG_SYNC = 'RESET';
260 SET GLOBAL DEBUG_DBUG = "";
261 SET GLOBAL wsrep_slave_threads=DEFAULT;
262 connection node_1;
263 SET DEBUG_SYNC = 'RESET';
264 SET GLOBAL DEBUG_DBUG = "";
265 SET GLOBAL wsrep_slave_threads=DEFAULT;
266 connection node_1;
267 include/assert_grep.inc [Foreign key referenced table found: 2 tables]
268 include/assert_grep.inc [Foreign key referenced table found: test.t2]
269 include/assert_grep.inc [Foreign key referenced table found: test.t3]
270 connection node_2;
271 select * from t1;
272 id f2
273 1 0
274 2 0
275 select * from t2;
276 id t1_id f2
277 1 1 1234
278 2 2 1234
279 select * from t3;
280 id t2_id f2
281 connection node_1;
282 select * from t1;
283 id f2
284 1 0
285 2 0
286 select * from t2;
287 id t1_id f2
288 1 1 1234
289 2 2 1234
290 select * from t3;
291 id t2_id f2
292 DROP TABLE t3, t2, t1;
293 #
294 # 4. BF-BF conflict on MDL locks between:
295 # OPTIMIZE TABLE t2 (whose parent table are t2 -> t1), and
296 # DELETE on t1.
297 #
298 connection node_2;
299 SET GLOBAL wsrep_slave_threads=2;
300 CREATE TABLE t1 (
301 id INTEGER PRIMARY KEY,
302 f2 INTEGER
303 );
304 CREATE TABLE t2 (
305 id INT PRIMARY KEY,
306 t1_id INT NOT NULL,
307 f2 INTEGER NOT NULL,
308 KEY key_t1_id(t1_id),
309 CONSTRAINT key_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) ON UPDATE CASCADE ON DELETE CASCADE
310 );
311 INSERT INTO t1 VALUES (1,0);
312 INSERT INTO t1 VALUES (2,0);
313 INSERT INTO t1 VALUES (3,0);
314 INSERT INTO t2 VALUES (1,1,1234);
315 INSERT INTO t2 VALUES (2,2,1234);
316 connection node_2;
317 SET GLOBAL DEBUG_DBUG = '+d,sync.wsrep_apply_toi';
318 connection node_1;
319 OPTIMIZE TABLE t2;
320 Table Op Msg_type Msg_text
321 test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
322 test.t2 optimize status OK
323 connection node_2;
324 SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_toi_reached";
325 SET SESSION wsrep_sync_wait = 0;
326 connection node_1;
327 SET GLOBAL DEBUG_DBUG = '+d,wsrep_print_foreign_keys_table';
328 START TRANSACTION;
329 DELETE FROM t1 WHERE id = 3;
330 COMMIT;
331 connection node_2;
332 SET GLOBAL DEBUG_DBUG = '-d,sync.wsrep_apply_toi';
333 SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_toi";
334 SET DEBUG_SYNC = 'RESET';
335 SET GLOBAL DEBUG_DBUG = "";
336 SET GLOBAL wsrep_slave_threads=DEFAULT;
337 connection node_1;
338 SET DEBUG_SYNC = 'RESET';
339 SET GLOBAL DEBUG_DBUG = "";
340 SET GLOBAL wsrep_slave_threads=DEFAULT;
341 connection node_1;
342 include/assert_grep.inc [Foreign key referenced table found: 1 tables]
343 include/assert_grep.inc [Foreign key referenced table found: test.t2]
344 connection node_2;
345 select * from t1;
346 id f2
347 1 0
348 2 0
349 select * from t2;
350 id t1_id f2
351 1 1 1234
352 2 2 1234
353 connection node_1;
354 select * from t1;
355 id f2
356 1 0
357 2 0
358 select * from t2;
359 id t1_id f2
360 1 1 1234
361 2 2 1234
362 DROP TABLE t2, t1;