Debian Codegraph @

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

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