Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_disable.test

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