Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_disable.result

raw
1 create table parent (
2 a int primary key auto_increment,
3 b int not null,
4 c int not null,
5 unique(b) using hash,
6 index(c)) engine = ndb;
7 create table child (
8 a int primary key auto_increment,
9 b int not null,
10 c int not null,
11 unique(b) using hash,
12 index(c)) engine = ndb;
13 alter table child add constraint fkname foreign key (a) references parent(a) on delete restrict on update restrict;
14 insert into parent values (1,1,1);
15 insert into child values (1,1,1);
16 insert into child values (2,2,2);
17 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
18 # Disabling foreign key checks
19 set foreign_key_checks = 0;
20 insert into child values (2,2,2);
21 select * from child order by 1,2,3;
22 a b c
23 1 1 1
24 2 2 2
25 set foreign_key_checks = 1;
26 begin;
27 delete from parent;
28 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
29 rollback;
30 # Disabling foreign key checks
31 set foreign_key_checks = 0;
32 begin;
33 delete from parent;
34 rollback;
35 delete from child;
36 delete from parent;
37 set foreign_key_checks = 1;
38 begin;
39 insert into child values (2,2,2);
40 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
41 rollback;
42 # Disabling foreign key checks
43 set foreign_key_checks = 0;
44 begin;
45 insert into child values (2,2,2);
46 rollback;
47 set foreign_key_checks = 1;
48 delete from child;
49 delete from parent;
50 insert into parent values (1,1,1);
51 begin;
52 insert into child values (1,1,1);
53 begin;
54 delete from parent;
55 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
56 commit;
57 commit;
58 delete from child;
59 delete from parent;
60 # Disabling foreign key checks
61 set foreign_key_checks = 0;
62 insert into parent values (1,1,1);
63 # Disabling foreign key checks
64 set foreign_key_checks = 0;
65 begin;
66 insert into child values (1,1,1);
67 # Disabling foreign key checks
68 set foreign_key_checks = 0;
69 begin;
70 delete from parent;
71 commit;
72 set foreign_key_checks = 1;
73 commit;
74 set foreign_key_checks = 1;
75 select * from parent order by 1,2,3;
76 a b c
77 select * from child order by 1,2,3;
78 a b c
79 1 1 1
80 delete from child;
81 delete from parent;
82 set foreign_key_checks = 0;
83 insert into parent values (1,1,1);
84 insert into parent values (1,1,1);
85 ERROR 23000: Duplicate entry '1' for key 'parent.PRIMARY'
86 insert into parent values (2,1,1);
87 ERROR 23000: Duplicate entry '1' for key 'parent.b'
88 delete from child;
89 delete from parent;
90 insert into parent values (1,1,1);
91 insert into child values (1,1,1);
92 set ndb_deferred_constraints = 1;
93 set foreign_key_checks = 1;
94 set foreign_key_checks = 0;
95 begin;
96 set foreign_key_checks = 1;
97 insert into child values (2,2,2);
98 commit;
99 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
100 begin;
101 insert into child values (2,2,2);
102 set foreign_key_checks = 0;
103 commit;
104 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
105 begin;
106 insert into child values (2,2,2);
107 set foreign_key_checks = 1;
108 commit;
109 begin;
110 insert into child values (3,3,3);
111 insert into child values (4,4,4);
112 set foreign_key_checks = 0;
113 insert into parent values (3,3,3);
114 commit;
115 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
116 set foreign_key_checks = 1;
117 begin;
118 insert into child values (3,3,3);
119 set foreign_key_checks = 0;
120 insert into parent values (3,3,3);
121 insert into child values (4,4,4);
122 commit;
123 set foreign_key_checks = 1;
124 set ndb_deferred_constraints = 0;
125 alter table child drop foreign key fkname;
126 delete from child;
127 delete from parent;
128 alter table child add constraint fkname foreign key (a) references parent(a) on delete no action on update no action;
129 insert into parent values (1,1,1);
130 insert into child values (1,1,1);
131 insert into child values (2,2,2);
132 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
133 insert into parent values (2,2,2);
134 insert into child values (2,2,2);
135 begin;
136 update parent set a = a + 2;
137 commit;
138 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
139 select * from parent order by 1,2,3;
140 a b c
141 1 1 1
142 2 2 2
143 select * from child order by 1,2,3;
144 a b c
145 1 1 1
146 2 2 2
147 # Disabling foreign key checks
148 set foreign_key_checks = 0;
149 insert into child values (3,3,3);
150 insert into parent values (3,3,3);
151 begin;
152 update parent set a = a + 3;
153 commit;
154 select * from parent order by 1,2,3;
155 a b c
156 4 1 1
157 5 2 2
158 6 3 3
159 select * from child order by 1,2,3;
160 a b c
161 1 1 1
162 2 2 2
163 3 3 3
164 set foreign_key_checks = 1;
165 alter table child drop foreign key fkname;
166 delete from child;
167 delete from parent;
168 alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
169 create table grandchild (
170 a int primary key auto_increment,
171 b int not null,
172 c int not null,
173 unique(b) using hash,
174 index(c)) engine = ndb;
175 alter table grandchild add constraint fkname_child foreign key (a) references child (a) on delete cascade on update restrict;
176 insert into parent values (1,1,1),(2,2,2);
177 insert into child values (1,1,1),(2,2,2);
178 insert into grandchild values (1,1,1),(2,2,2);
179 begin;
180 delete from parent where a = 1;
181 select * from child order by 1,2,3;
182 a b c
183 2 2 2
184 select * from grandchild order by 1,2,3;
185 a b c
186 2 2 2
187 commit;
188 # Disabling foreign key checks
189 set foreign_key_checks = 0;
190 begin;
191 delete from parent where a = 2;
192 select * from child order by 1,2,3;
193 a b c
194 2 2 2
195 select * from grandchild order by 1,2,3;
196 a b c
197 2 2 2
198 commit;
199 set foreign_key_checks = 1;
200 alter table child drop foreign key fkname;
201 alter table grandchild drop foreign key fkname_child;
202 delete from grandchild;
203 delete from child;
204 delete from parent;
205 alter table child add constraint fkname foreign key (b) references parent(b) on delete restrict on update cascade;
206 alter table grandchild add constraint fkname_child foreign key (b) references child(b) on delete restrict on update cascade;
207 insert into parent values (1,1,1), (2,2,2);
208 insert into child values (1,1,1),(2,2,2);
209 insert into grandchild values (1,1,1),(2,2,2);
210 begin;
211 update parent set b = 3 where a = 1;
212 select * from child order by 1,2,3;
213 a b c
214 1 3 1
215 2 2 2
216 select * from grandchild order by 1,2,3;
217 a b c
218 1 3 1
219 2 2 2
220 rollback;
221 # Disabling foreign key checks
222 set foreign_key_checks = 0;
223 begin;
224 update parent set b = 3 where a = 1;
225 select * from child order by 1,2,3;
226 a b c
227 1 1 1
228 2 2 2
229 select * from grandchild order by 1,2,3;
230 a b c
231 1 1 1
232 2 2 2
233 rollback;
234 set foreign_key_checks = 1;
235 alter table child drop foreign key fkname;
236 alter table grandchild drop foreign key fkname_child;
237 delete from grandchild;
238 delete from child;
239 delete from parent;
240 alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
241 alter table grandchild add constraint fkname_child foreign key (a) references child (a) on delete cascade on update restrict;
242 set ndb_deferred_constraints = 1;
243 insert into parent values (1,1,1), (2,2,2);
244 insert into child values (1,1,1),(2,2,2);
245 insert into grandchild values (1,1,1),(2,2,2);
246 begin;
247 # Disabling foreign key checks
248 set foreign_key_checks = 0;
249 delete from parent where a = 1;
250 select * from child order by 1,2,3;
251 a b c
252 1 1 1
253 2 2 2
254 select * from grandchild order by 1,2,3;
255 a b c
256 1 1 1
257 2 2 2
258 commit;
259 select * from child order by 1,2,3;
260 a b c
261 1 1 1
262 2 2 2
263 select * from grandchild order by 1,2,3;
264 a b c
265 1 1 1
266 2 2 2
267 delete from grandchild;
268 delete from child;
269 delete from parent;
270 insert into parent values (1,1,1), (2,2,2);
271 insert into child values (1,1,1),(2,2,2);
272 insert into grandchild values (1,1,1),(2,2,2);
273 set foreign_key_checks = 1;
274 begin;
275 delete from parent where a = 1;
276 # Disabling foreign key checks
277 set foreign_key_checks = 0;
278 select * from child order by 1,2,3;
279 a b c
280 1 1 1
281 2 2 2
282 select * from grandchild order by 1,2,3;
283 a b c
284 1 1 1
285 2 2 2
286 commit;
287 select * from child order by 1,2,3;
288 a b c
289 2 2 2
290 select * from grandchild order by 1,2,3;
291 a b c
292 2 2 2
293 set ndb_deferred_constraints = 0;
294 set foreign_key_checks = 1;
295 alter table child drop foreign key fkname;
296 alter table grandchild drop foreign key fkname_child;
297 delete from grandchild;
298 delete from child;
299 delete from parent;
300 alter table child add constraint fkname foreign key (b) references parent(b) on delete restrict on update cascade;
301 alter table grandchild add constraint fkname_child foreign key (b) references child(b) on delete restrict on update cascade;
302 set ndb_deferred_constraints = 1;
303 insert into parent values (1,1,1), (2,2,2);
304 insert into child values (1,1,1),(2,2,2);
305 insert into grandchild values (1,1,1),(2,2,2);
306 begin;
307 # Disabling foreign key checks
308 set foreign_key_checks = 0;
309 update parent set b = 3 where a = 1;
310 select * from child order by 1,2,3;
311 a b c
312 1 1 1
313 2 2 2
314 select * from grandchild order by 1,2,3;
315 a b c
316 1 1 1
317 2 2 2
318 commit;
319 select * from child order by 1,2,3;
320 a b c
321 1 1 1
322 2 2 2
323 select * from grandchild order by 1,2,3;
324 a b c
325 1 1 1
326 2 2 2
327 delete from grandchild;
328 delete from child;
329 delete from parent;
330 insert into parent values (1,1,1), (2,2,2);
331 insert into child values (1,1,1),(2,2,2);
332 insert into grandchild values (1,1,1),(2,2,2);
333 set foreign_key_checks = 1;
334 begin;
335 update parent set b = 3 where a = 1;
336 # Disabling foreign key checks
337 set foreign_key_checks = 0;
338 select * from child order by 1,2,3;
339 a b c
340 1 1 1
341 2 2 2
342 select * from grandchild order by 1,2,3;
343 a b c
344 1 1 1
345 2 2 2
346 commit;
347 select * from child order by 1,2,3;
348 a b c
349 1 3 1
350 2 2 2
351 select * from grandchild order by 1,2,3;
352 a b c
353 1 3 1
354 2 2 2
355 set ndb_deferred_constraints = 0;
356 set foreign_key_checks = 1;
357 alter table child drop foreign key fkname;
358 alter table grandchild drop foreign key fkname_child;
359 delete from grandchild;
360 delete from child;
361 delete from parent;
362 drop table parent, child, grandchild;
363 set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
364 set ndb_join_pushdown = true;
365 create table t1 (
366 a int not null,
367 b int not null,
368 c int,
369 d int not null,
370 primary key (a),
371 unique (b),
372 unique (c),
373 unique (d)
374 ) engine=ndb;
375 create table t2 (
376 a int not null,
377 b int not null,
378 c int,
379 d int,
380 primary key (a)
381 ) engine=ndb;
382 alter table t2 add constraint fa foreign key (a) references t1 (a) on delete cascade on update restrict;
383 alter table t2 add constraint fb foreign key (b) references t1 (b) on delete cascade on update restrict;
384 alter table t2 add constraint fc foreign key (c) references t1 (c) on delete cascade on update restrict;
385 alter table t2 add constraint fd foreign key (d) references t1 (d) on delete cascade on update restrict;
386 insert into t1 values
387 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6);
388 insert into t2 values
389 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6);
390 update t1 set b = 17 where c > 5;
391 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fb` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
392 update t1 set c = null where c = 2;
393 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fc` FOREIGN KEY (`c`) REFERENCES `t1` (`c`) ON DELETE CASCADE ON UPDATE RESTRICT)
394 select *
395 from t1,t2
396 where t2.a = t1.b and t2.b = t1.c;
397 a b c d a b c d
398 1 1 1 1 1 1 1 1
399 2 2 2 2 2 2 2 2
400 3 3 3 3 3 3 3 3
401 4 4 4 4 4 4 4 4
402 5 5 5 5 5 5 5 5
403 6 6 6 6 6 6 6 6
404 delete from t1 where d = 3;
405 select * from t2 order by a;
406 a b c d
407 1 1 1 1
408 2 2 2 2
409 4 4 4 4
410 5 5 5 5
411 6 6 6 6
412 select *
413 from t1,t2
414 where t2.a = t1.b + 1;
415 a b c d a b c d
416 1 1 1 1 2 2 2 2
417 4 4 4 4 5 5 5 5
418 5 5 5 5 6 6 6 6
419 insert into t1 values (3,3,3,3);
420 insert into t2 values (3,3,3,3);
421 # Disabling foreign key checks
422 set foreign_key_checks = 0;
423 update t1 set b = 17 where c > 5;
424 update t1 set c = null where c = 2;
425 select *
426 from t1,t2
427 where t2.a = t1.b and t2.b = t1.c;
428 a b c d a b c d
429 1 1 1 1 1 1 1 1
430 3 3 3 3 3 3 3 3
431 4 4 4 4 4 4 4 4
432 5 5 5 5 5 5 5 5
433 select * from t2 order by a;
434 a b c d
435 1 1 1 1
436 2 2 2 2
437 3 3 3 3
438 4 4 4 4
439 5 5 5 5
440 6 6 6 6
441 delete from t1 where d = 3;
442 select * from t2 order by a;
443 a b c d
444 1 1 1 1
445 2 2 2 2
446 3 3 3 3
447 4 4 4 4
448 5 5 5 5
449 6 6 6 6
450 select *
451 from t1,t2
452 where t2.a = t1.b + 1;
453 a b c d a b c d
454 1 1 1 1 2 2 2 2
455 2 2 NULL 2 3 3 3 3
456 4 4 4 4 5 5 5 5
457 5 5 5 5 6 6 6 6
458 drop table t2, t1;
459 set ndb_join_pushdown = @save_ndb_join_pushdown;
460 set foreign_key_checks = 1;
461 #
462 # Bug#25063506 : Updating parent row cascades to child but not to grandchild
463 #
464 # Setup tables
465 create table parent(
466 id int primary key auto_increment,
467 val1 int unique,
468 val2 int
469 ) engine ndb;
470 create table child(
471 ref int unique,
472 foreign key fk1(ref) references parent(val1) on update cascade
473 ) engine ndb;
474 create table grandchild(
475 ref int,
476 foreign key fk2(ref) references child(ref) on update cascade
477 ) engine ndb;
478 insert into parent(val1, val2) values(10, 100), (20, 400), (30, 900);
479 insert into child(ref) values(10), (20), (30);
480 insert into grandchild(ref) values(10), (20), (30);
481 # Change ndb_optimized_node_selection to 2
482 # so that the following queries goes to the same node
483 set ndb_optimized_node_selection=2;
484 # Perform query with the foreign_key_checks off
485 # This transaction will use an indexOperation internally and release it when done
486 set foreign_key_checks = 0;
487 delete from parent where val1 = 100;
488 set foreign_key_checks = 1;
489 begin;
490 # The following transaction would reuse the indexOperation
491 # when the update is cascaded to grandchild
492 update parent set val1 = 40 where val2 = 100;
493 # Print tuples to check if they are consistent
494 select * from parent order by id;
495 id val1 val2
496 1 40 100
497 2 20 400
498 3 30 900
499 select * from child order by ref;
500 ref
501 20
502 30
503 40
504 select * from grandchild order by ref;
505 ref
506 20
507 30
508 40
509 rollback;
510 # Cleanup
511 set ndb_optimized_node_selection=3;
512 drop table grandchild, child, parent;