Debian Codegraph @

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

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