Debian Codegraph @

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

raw
1 --source include/have_ndb.inc
2
3 # Create view which displays all user tables (thus including mock tables).
4 # NOTE! Uses a format which is backward compatible with the output
5 # from "ndb_show_tables" previously used by this test - for example names
6 # have surrounding quotes.
7 --disable_query_log ONCE
8 CREATE VIEW ndb_show_tables_results AS
9 SELECT
10 id,
11 /* Hardcoded since only user tables are selected */
12 'UserTable' AS type,
13 /* Database is first part of fq_name */
14 CONCAT("'", CONCAT(SUBSTRING_INDEX(fq_name, "/", 1), "'")) AS _database,
15 /* Table name is last part of fq_name */
16 CONCAT("'", CONCAT(SUBSTRING_INDEX(fq_name, "/", -1), "'")) AS name
17 FROM ndbinfo.dict_obj_info WHERE type = 2 /* User table */;
18
19 --echo #
20 --echo # Create child tables before parents
21 --echo # by turning off foreign_key_checks for this session
22 --echo #
23 select @@foreign_key_checks;
24 set @@foreign_key_checks=0;
25
26 create table t1 (
27 pk int not null primary key,
28 b int,
29 foreign key (b) references t2(pk1)
30 ) engine=ndb;
31
32 create table t3 (
33 pk int not null primary key,
34 b int, c int,
35 d int, e int, f int,
36 g int, h int, i int, j int,
37 foreign key (b, c) references t4(pk1, pk2),
38 foreign key (d, e, f) references t5(uk1, uk2, uk3),
39 foreign key (g, h, i, j) references t6(oi1, oi2, oi3, oi4)
40 ) engine=ndb;
41
42 --echo # There should be 4 mock tables created
43 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
44
45 let $id_t1 = `select id from ndb_show_tables_results where name = "'t1'"`;
46 --echo # There should be 1 mock table created for t1
47 --replace_result $id_t1 <id>
48 eval select type, name from ndb_show_tables_results
49 where name like "'NDB\$FKM_$id_t1%'";
50
51 let $id_t3 = `select id from ndb_show_tables_results where name = "'t3'"`;
52 --echo # There should be 3 mock tables created for t3
53 --replace_result $id_t3 <id>
54 eval select type, name from ndb_show_tables_results
55 where name like "'NDB\$FKM_$id_t3%'" order by name;
56
57 drop table t3;
58
59 --echo # There should be 1 mock table
60 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
61
62 --echo # Only t1 should have 1 mock table
63 --replace_result $id_t1 <id>
64 eval select type, name from ndb_show_tables_results
65 where name like "'NDB\$FKM_$id_t1%'";
66
67 drop table t1;
68
69 --echo # There should be 0 mock tables
70 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
71
72 --echo #
73 --echo # Create child table before parent
74 --echo # by turning off foreign_key_checks for this session
75 --echo #
76
77 create table t1 (
78 pk int not null primary key,
79 b int,
80 foreign key fk1(b) references t2(pk1)
81 ) engine=ndb;
82
83 create table t2 (
84 pk1 int not null primary key,
85 c int
86 ) engine=ndb;
87
88 --echo # There should be 0 mock tables
89 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
90
91 --echo # Verify that fk is working now when parent has been resolved
92 set foreign_key_checks=1;
93 --error ER_NO_REFERENCED_ROW_2
94 insert into t1 values(1, 2);
95 set foreign_key_checks=0;
96
97 drop table t1, t2;
98
99
100 --echo #
101 --echo # Exceed table name when creating the mock table by referencing
102 --echo # a not yet created table with really long name
103 --echo #
104
105 --echo # 1) By blowing the mock_tabname buffer(currently 512 bytes)
106 let $parent_tabname = `select repeat('abcdefghijklmnopqrstuvxyz_', 20)`;
107 --error ER_WRONG_TABLE_NAME
108 eval create table t1 (
109 pk int not null primary key,
110 b int,
111 foreign key (b) references $parent_tabname(pk1)
112 ) engine=ndb;
113 show warnings;
114
115 #
116 # Disabled for now, NdbApi fails to fetch the table from NDB and
117 # claims "283 table is being dropped". This looks like some problem in NdbApi
118 # (search for DBUG_PRINT with text "dropped by another thread")
119 #--echo # 2) By blowing the max tabname length in NDB
120 #let $parent_tabname = `select repeat('abcdefghijklmnopqrstuvxyz_', 15)`;
121 #--error ER_CANNOT_ADD_FOREIGN
122 #eval create table t1 (
123 # pk int not null primary key,
124 # b int,
125 # foreign key (b) references $parent_tabname(pk1)
126 #) engine=ndb;
127 #show warnings;
128
129
130 --echo #
131 --echo # SHOW CREATE TABLE for table with mock table references
132 --echo #
133 --echo # In the past SHOW CREATE TABLE has shown mock table names
134 --echo # in @@ndb_show_foreign_key_mock_tables=1 mode. However,
135 --echo # after move of related code to SQL-layer, this is no
136 --echo # longer supported.
137 create table t1 (
138 a int not null,
139 b int not null,
140 c int not null,
141 primary key (a),
142 unique key (b) using hash,
143 key (c),
144 constraint fk1 foreign key(a) references t2 (a),
145 constraint fk2 foreign key(b) references t3 (a),
146 constraint fk3 foreign key(c) references t4 (a),
147 constraint fk4 foreign key(a) references t2 (b),
148 constraint fk5 foreign key(b) references t3 (b),
149 constraint fk6 foreign key(c) references t4 (b)
150 ) engine=ndbcluster;
151
152 --disable_warnings
153 show create table t1;
154 --enable_warnings
155
156 create table t2 (
157 a int primary key,
158 b int not null,
159 c int not null,
160 unique(b) using hash,
161 index(c)
162 ) engine = ndb;
163
164 --disable_warnings
165 show create table t1;
166 --enable_warnings
167
168 --disable_warnings
169 set @@ndb_show_foreign_key_mock_tables= 1;
170 show create table t1;
171 --enable_warnings
172 set @@ndb_show_foreign_key_mock_tables= 0;
173
174 drop table t1, t2;
175
176
177 --echo #
178 --echo # Create child table and then the parent
179 --echo # which does not match the fk
180 --echo #
181
182 # Suppress the warning this test case generates
183 --disable_query_log ONCE
184 call mtr.add_suppression(".* have all the referenced columns");
185
186 create table t1 (
187 pk int not null primary key,
188 b int,
189 foreign key (b) references t2(pk1)
190 ) engine=ndb;
191
192 --error ER_FK_NO_COLUMN_PARENT
193 create table t2 (
194 pk2 int not null primary key,
195 c int
196 ) engine=ndb;
197
198 --echo # There should be 1 mock table still
199 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
200
201 drop table t1;
202
203 --echo #
204 --echo # Create child table and then the parent
205 --echo # which does not match any index
206 --echo #
207
208 # Suppress the warning this test case generates
209 --disable_query_log ONCE
210 call mtr.add_suppression(".* no matching index could be found");
211
212 create table t1 (
213 pk int not null primary key,
214 b int,
215 foreign key (b) references t2(c)
216 ) engine=ndb;
217
218 --error ER_FK_NO_INDEX_PARENT
219 create table t2 (
220 pk1 int not null primary key,
221 c int
222 ) engine=ndb;
223
224 --echo # There should be 1 mock table still
225 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
226
227 drop table t1;
228
229 # Turn on ndb_show_foreign_key_mock_tables to get more info about
230 # when creating and dropping mock tables
231 set @@ndb_show_foreign_key_mock_tables= 1;
232
233 --echo #
234 --echo # Test where FK goes from t1->t2->t3->t4 and tables are created in that order
235
236 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
237 create table t1 (
238 pk11 int not null primary key,
239 b int,
240 foreign key (b) references t2(pk21)
241 ) engine=ndb;
242
243 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
244 --sorted_result
245 create table t2 (
246 pk21 int not null primary key,
247 b int, c int,
248 foreign key (b, c) references t3(pk31, pk32)
249 ) engine=ndb;
250
251 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
252 --sorted_result
253 create table t3 (
254 pk31 int not null,
255 pk32 int not null,
256 primary key(pk31, pk32),
257 d int, e int,f int,
258 foreign key (d,e,f) references t4(pk41, pk42, pk43)
259 ) engine=ndb;
260
261 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
262 create table t4 (
263 pk41 int not null,
264 pk42 int not null,
265 pk43 int not null,
266 primary key(pk41, pk42, pk43),
267 m varchar(55)
268 ) engine=ndb;
269
270 --echo # There should be no mock tables
271 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
272
273 drop table t1, t2, t3, t4;
274
275
276 --echo #
277 --echo # Test where FK 'fans out' t1->t2 t1->t3 t1->t4, create t1 first
278
279 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
280 --sorted_result
281 create table t1 (
282 pk11 int not null primary key,
283 b int,
284 foreign key (b) references t2(c),
285 d int, e int,
286 foreign key (d, e) references t3(f, g),
287 h varchar(55), i int, j datetime,
288 foreign key (h, i, j) references t4(m, l, k)
289 ) engine=ndb;
290
291 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
292 create table t2 (
293 pk21 int not null primary key,
294 c int,
295 unique(c)
296 ) engine=ndb;
297
298 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
299 create table t3 (
300 pk31 int not null,
301 pk32 int not null,
302 primary key(pk31, pk32),
303 g int, f int,
304 unique(f, g)
305 ) engine=ndb;
306
307 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
308 create table t4 (
309 pk41 int not null,
310 pk42 int not null,
311 pk43 int not null,
312 primary key(pk41, pk42, pk43),
313 m varchar(55), l int, k datetime,
314 unique(k, l, m)
315 ) engine=ndb;
316
317 --echo # There should be no mock tables
318 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
319
320 drop table t1, t2, t3, t4;
321
322 --echo #
323 --echo # Test where FK refers to 2+ different indices in parent t1->t2(1) t1->t2(2)
324
325 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
326 --sorted_result
327 create table t1 (
328 pk11 int not null primary key,
329 foreign key (pk11) references t2(pk21),
330 b int,
331 foreign key (b) references t2(c),
332 d int, e int,
333 foreign key (d, e) references t2(f, g),
334 h varchar(55), i int, j datetime,
335 foreign key (h, i, j) references t2(m, l, k)
336 ) engine=ndb;
337
338 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
339 --sorted_result
340 create table t2 (
341 pk21 int not null primary key,
342 c int,
343 unique(c),
344 g int, f int,
345 unique(f, g),
346 m varchar(55), l int, k datetime,
347 unique(k, l, m)
348 ) engine=ndb;
349
350 --echo # There should be no mock tables
351 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
352
353 drop table t1, t2;
354
355
356 --echo #
357 --echo # Test drop of referenced table(mock table should be created)
358 --echo #
359
360 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
361 create table t1 (
362 pk1 int not null primary key,
363 foreign key (pk1) references t2(pk2),
364 a varchar(255)
365 ) engine=ndb;
366
367 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
368 create table t2 (
369 pk2 int not null primary key,
370 b datetime
371 ) engine=ndb;
372
373 --echo # There should be no mock tables
374 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
375
376 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
377 drop table t2;
378
379 let $id_t1 = `select id from ndb_show_tables_results where name = "'t1'"`;
380 --echo # There should be 1 mock table created for t1
381 --replace_result $id_t1 <id>
382 eval select type, name from ndb_show_tables_results
383 where name like "'NDB\$FKM_$id_t1%'";
384
385 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
386 drop table t1;
387
388 --echo # There should be no mock tables
389 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
390
391 # Turn off ndb_show_foreign_key_mock_tables again
392 set @@ndb_show_foreign_key_mock_tables= 0;
393
394
395 --echo #
396 --echo # Test mock tables in different databases, mock table
397 --echo # should be created in same database as the real parent would.
398 --echo #
399
400 set @@ndb_show_foreign_key_mock_tables= 1;
401
402 create database mydb0;
403 create database mydb1;
404
405 # Create child referencing parent in different db
406 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
407 create table mydb1.t1 (
408 a int primary key,
409 constraint fk1 foreign key(a) references mydb0.t2(a)
410 ) engine = ndb;
411
412 let $id_t1 = `select id from ndb_show_tables_results
413 where _database = "'mydb1'" and name = "'t1'"`;
414 --echo # There should be 1 mock table created for t1
415 --replace_result $id_t1 <id>
416 eval select type, _database, name from ndb_show_tables_results
417 where name like "'NDB\$FKM_$id_t1%'";
418
419 # Drop child table, should also remove the mock table
420 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
421 drop table mydb1.t1;
422
423 --echo # There should be no mock tables
424 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
425
426 # Create child again
427 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
428 create table mydb1.t1 (
429 a int primary key,
430 constraint fk1 foreign key(a) references mydb0.t2(a)
431 ) engine = ndb;
432
433 # And the parent
434 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
435 create table mydb0.t2 (
436 a int primary key
437 ) engine = ndb;
438
439 --echo # There should be no mock tables
440 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
441
442 --echo # Drop child then parent
443 drop table mydb1.t1;
444 drop table mydb0.t2;
445
446 # Create child again
447 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
448 create table mydb1.t1 (
449 a int primary key,
450 constraint fk1 foreign key(a) references mydb0.t2(a)
451 ) engine = ndb;
452
453 # And the parent
454 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
455 create table mydb0.t2 (
456 a int primary key
457 ) engine = ndb;
458
459 --echo # There should be no mock tables
460 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
461
462 --echo # Drop parent then child(creates mock which is then dropped)
463 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
464 drop table mydb0.t2;
465 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
466 drop table mydb1.t1;
467
468 --echo # There should be no mock tables
469 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
470
471 --echo #
472 --echo # Create spagetthi foreign keys between tables in three different databases
473 --echo #
474 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
475 --sorted_result
476 create table t1 (
477 pk int not null primary key,
478 b int,
479 foreign key (b) references mydb1.t2(pk1),
480 c int,
481 unique(c),
482 j int,
483 foreign key (j) references mydb0.t3(pk2),
484 k int,
485 foreign key (k) references mydb0.t1(pk5),
486 l int,
487 foreign key (l) references mydb1.t1(pk6),
488 y int,
489 foreign key (y) references mydb1.t1(z)
490 ) engine=ndb;
491
492 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
493 --sorted_result
494 create table mydb1.t2 (
495 pk1 int not null primary key,
496 d int,
497 foreign key (d) references mydb0.t3(pk2)
498 ) engine=ndb;
499
500 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
501 --sorted_result
502 create table mydb0.t3 (
503 pk2 int not null primary key,
504 e int,
505 foreign key (e) references test.t1(c)
506 ) engine=ndb;
507
508 create table mydb0.t4 (
509 pk3 int not null primary key,
510 f int,
511 foreign key (f) references t3(pk2)
512 ) engine=ndb;
513
514 create table mydb0.t5 (
515 pk4 int not null primary key,
516 g int,
517 foreign key (g) references test.t1(pk),
518 h int,
519 foreign key (h) references mydb1.t2(pk1),
520 i int,
521 foreign key (i) references mydb0.t3(pk2)
522 ) engine=ndb;
523
524 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
525 create table mydb0.t1 (
526 pk5 int not null primary key,
527 m int,
528 foreign key (m) references test.t1(pk),
529 n int,
530 foreign key (n) references mydb1.t2(pk1),
531 o int,
532 foreign key (o) references mydb0.t3(pk2)
533 ) engine=ndb;
534
535 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
536 --sorted_result
537 create table mydb1.t1 (
538 pk6 int not null primary key,
539 p int,
540 foreign key (p) references test.t1(pk),
541 q int,
542 foreign key (q) references mydb1.t2(pk1),
543 x int,
544 foreign key (x) references mydb0.t3(pk2),
545 z int,
546 unique(z)
547 ) engine=ndb;
548
549 set @@ndb_show_foreign_key_mock_tables= 0;
550
551 --echo # There should be no mock tables
552 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
553
554 --echo # Drop the database mydb0 with all tables still there
555 drop database mydb0;
556
557 --echo # There should be 4 mock tables created by dropping mydb0
558 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
559
560 --echo # Drop also database mydb1
561 drop database mydb1;
562
563 --echo # There should be 5 mock tables all referenced by test.t1
564 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
565
566 --echo # Drop t1 and the 5 remaining mocks
567 drop table t1;
568
569 --echo # There should be no mock tables
570 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
571
572
573 --echo #
574 --echo # ALTER a table which references mock parent
575 --echo #
576
577 create table t1 (
578 pk int not null primary key,
579 b int,
580 foreign key (b) references t2(pk1)
581 ) engine=ndb;
582
583 --echo # There should be one mock table in place of t2
584 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
585 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
586
587 alter table t1 add column c int;
588
589 --echo # There should be one mock table still
590 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
591 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
592
593 alter table t1 algorithm = copy, add column d int not null;
594
595 --echo # There should be one mock table still
596 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
597 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
598
599 drop table t1;
600
601 --echo # There should be no mock tables
602 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
603
604
605 --echo #
606 --echo # Alter table to add foreign keys referencing non existing parents. One
607 --echo # new mock should be created per fk
608 --echo #
609
610 create table child (
611 a int primary key auto_increment,
612 b int not null,
613 c int not null,
614 unique(b) using hash,
615 index(c)
616 ) engine = ndb;
617
618 alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a);
619 alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(col1);
620 alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(col2);
621 alter table child algorithm=inplace,
622 add constraint fk4 foreign key (c) references parent(col3),
623 add constraint fk5 foreign key (c) references parent(col4),
624 add constraint fk6 foreign key (c) references parent(col5);
625
626 --echo # There should be 6 mock tables
627 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
628 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
629
630 alter table child algorithm=inplace, drop foreign key fk1;
631
632 --echo # There should be 5 mock tables
633 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
634 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
635
636 alter table child algorithm=inplace,
637 drop foreign key fk3,
638 drop foreign key fk4,
639 drop foreign key fk6;
640
641 --echo # There should be 2 mock tables left
642 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
643 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
644
645 drop table child;
646
647 --echo # There should be no mock tables
648 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
649
650 --echo #
651 --echo # Alter table into place(i.e rename) as new parent, should remove
652 --echo # the mock table
653 --echo #
654
655 create table t1 (
656 pk1 int not null primary key,
657 b int,
658 foreign key (b) references t2(pk2)
659 ) engine=ndb;
660
661 create table t3 (
662 pk2 int not null primary key,
663 b int
664 ) engine=ndb;
665
666 --echo # There should be one mock table for t1
667 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
668
669 set @@ndb_show_foreign_key_mock_tables= 1;
670 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
671 alter table t3 rename t2;
672 set @@ndb_show_foreign_key_mock_tables= 0;
673
674 # Rename table referenced by foreign key -> t1 references t3
675 alter table t2 rename t3;
676 --disable_warnings
677 show create table t1;
678 --enable_warnings
679
680 drop table t3;
681 create table t2 (
682 pk2 int not null primary key,
683 b int
684 ) engine=ndb;
685
686 --echo # There should be one mock table for t1
687 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
688
689 set @@ndb_show_foreign_key_mock_tables= 1;
690 --replace_regex /FKM_[0-9]+_[0-9]+_/FKM_<child_id>_<id>_/
691 alter table t2 algorithm=inplace, rename t3;
692 set @@ndb_show_foreign_key_mock_tables= 0;
693
694 --echo # There should be no mock tables
695 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
696
697 drop table t3, t1;
698
699
700 --echo #
701 --echo # Truncating table which references mock table
702 --echo #
703 create table t1 (
704 pk1 int not null primary key,
705 b int,
706 foreign key fk1 (b) references t2(pk2)
707 ) engine=ndb;
708
709 set foreign_key_checks=1;
710 truncate table t1;
711 set foreign_key_checks=0;
712
713 set @@ndb_show_foreign_key_mock_tables= 1;
714 truncate table t1;
715 set @@ndb_show_foreign_key_mock_tables= 0;
716
717 --disable_warnings
718 show create table t1;
719 --enable_warnings
720
721 drop table t1;
722
723
724 --echo #
725 --echo # Truncating referenced table
726 --echo #
727 create table t2 (
728 pk2 int not null primary key,
729 c int
730 ) engine=ndb;
731 create table t1 (
732 pk1 int not null primary key,
733 b int,
734 foreign key (b) references t2(pk2)
735 ) engine=ndb;
736
737 set foreign_key_checks=1;
738 --replace_regex /(FK|fk)_[0-9]+_[0-9]+/FK_<child_id>_<id>/
739 --error ER_TRUNCATE_ILLEGAL_FK
740 truncate table t2;
741 set foreign_key_checks=0;
742 --let $ndb_table_name=t2
743 --source print_foreign_keys.inc
744 truncate table t2;
745 --echo Verify all the foreign key are re-created after truncate
746 --let $ndb_table_name=t2
747 --source print_foreign_keys.inc
748
749 drop table t1, t2;
750
751 --echo #
752 --echo # Truncating referenced table which references
753 --echo # another table
754 --echo #
755 create table t3 (
756 pk3 int not null primary key,
757 d int
758 ) engine=ndb;
759 create table t2 (
760 pk2 int not null primary key,
761 c int,
762 foreign key (c) references t3(pk3)
763 ) engine=ndb;
764 create table t1 (
765 pk1 int not null primary key,
766 b int,
767 foreign key (b) references t2(pk2)
768 ) engine=ndb;
769
770 set foreign_key_checks=1;
771 --replace_regex /(FK|fk)_[0-9]+_[0-9]+/FK_<child_id>_<id>/
772 --error ER_TRUNCATE_ILLEGAL_FK
773 truncate table t2;
774
775 set foreign_key_checks=0;
776 --let $ndb_table_name=t2
777 --source print_foreign_keys.inc
778
779 truncate table t2;
780 --echo Verify all the foreign key are re-created after truncate
781 --let $ndb_table_name=t2
782 --source print_foreign_keys.inc
783
784 drop table t1, t2, t3;
785
786
787 --echo #
788 --echo # Truncating referencing table
789 --echo #
790 create table t2 (
791 pk2 int not null primary key,
792 c int
793 ) engine=ndb;
794 create table t1 (
795 pk1 int not null primary key,
796 b int,
797 foreign key fk1 (b) references t2(pk2)
798 ) engine=ndb;
799
800 set @@ndb_show_foreign_key_mock_tables= 1;
801 set foreign_key_checks=1;
802 truncate table t1;
803 set foreign_key_checks=0;
804
805 truncate table t1;
806 set @@ndb_show_foreign_key_mock_tables= 0;
807
808 --disable_warnings
809 show create table t1;
810 --enable_warnings
811
812 drop table t1, t2;
813
814 --echo #
815 --echo # Truncate with multiple foreign keys
816 --echo #
817 create table t2 (
818 pk2 int not null primary key,
819 c int
820 ) engine=ndb;
821 create table t1 (
822 pk1 int not null primary key,
823 b int,
824 c int,
825 d int,
826 e int,
827 foreign key fk1 (b) references t2(pk2),
828 foreign key fk2 (c) references t3(pk3),
829 foreign key fk3 (d) references t3(pk4),
830 foreign key fk4 (e) references t4(pk5)
831 ) engine=ndb;
832
833 set @@ndb_show_foreign_key_mock_tables= 1;
834 set foreign_key_checks=1;
835 truncate table t1;
836 set foreign_key_checks=0;
837
838 truncate table t1;
839 set @@ndb_show_foreign_key_mock_tables= 0;
840
841 --disable_warnings
842 show create table t1;
843 --enable_warnings
844
845 drop table t1, t2;
846
847 --echo #
848 --echo # Create child table referencing mock parent, insert data and
849 --echo # then create the real parent (like mysqldump does it)
850 --echo #
851 CREATE TABLE `address` (
852 `no` int NOT NULL AUTO_INCREMENT,
853 `type` int NOT NULL DEFAULT '37',
854 `customer_id` int NOT NULL,
855 `street` varchar(255) DEFAULT NULL,
856 `country` int DEFAULT NULL,
857 PRIMARY KEY (`no`),
858 KEY `ca_fk1` (`customer_id`),
859 CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
860 ON DELETE CASCADE ON UPDATE RESTRICT
861 ) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
862
863 INSERT INTO `address` VALUES (1,37,1,'Main st. 1215',48);
864
865 CREATE TABLE `customer` (
866 `id` int NOT NULL,
867 PRIMARY KEY (`id`)
868 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
869
870 --echo # Check DML
871 insert into address (type, customer_id, street, country)
872 values (38,2,'Second st. 2326',49 );
873 insert into address (type, customer_id, street, country)
874 values (39,1,'Third st. 3437',50 );
875 update address set country=50 where no=2;
876 # Add a valid customer for first address
877 insert into customer values(1);
878
879 --echo # Check DML with foreign_key_checks on
880 set foreign_key_checks=1;
881
882 # Try to point first address to another customer which does not exists
883 --error ER_NO_REFERENCED_ROW_2
884 update address set customer_id = 3 where no = 1;
885
886 # Delete first customer, should delete two rows from address -> one remaining
887 delete from customer where id = 1;
888 select no, type, customer_id from address;
889
890 set foreign_key_checks=0;
891
892 drop table address, customer;
893
894
895 --echo #
896 --echo # Create child table referencing real parent, insert data
897 --echo # and then create fk(like mysqldump does not do it)
898 --echo #
899 CREATE TABLE `customer` (
900 `id` int NOT NULL,
901 PRIMARY KEY (`id`)
902 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
903
904 INSERT INTO `customer` VALUES (1);
905
906 CREATE TABLE `address` (
907 `no` int NOT NULL AUTO_INCREMENT,
908 `type` int NOT NULL DEFAULT '37',
909 `customer_id` int NOT NULL,
910 `street` varchar(255) DEFAULT NULL,
911 `country` int DEFAULT NULL,
912 PRIMARY KEY (`no`),
913 KEY `ca_fk1` (`customer_id`)
914 ) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
915
916 # Insert record pointing at existing customer
917 INSERT INTO `address` VALUES (1,37,1,'Main st. 1215',48);
918
919 # Insert record pointing at _non_ existing customer
920 INSERT INTO `address` VALUES (2,22,2,'Second st. 2222',2);
921
922 --echo # Add fk inplace
923 # Adding foreign key with foreign_key_checks=1, fails since data is inconsistent
924 set foreign_key_checks=1;
925 --error ER_CANNOT_ADD_FOREIGN
926 ALTER TABLE address ALGORITHM=inplace,
927 ADD CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
928 ON DELETE CASCADE ON UPDATE RESTRICT;
929 set foreign_key_checks=0;
930
931 # Turn off foreign_key_checks and create the fk anyway
932 ALTER TABLE address ALGORITHM=inplace,
933 ADD CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
934 ON DELETE CASCADE ON UPDATE RESTRICT;
935
936 --echo # Add another fk copying
937 # Adding foreign key with foreign_key_checks=1, fails since data is inconcistent
938 set foreign_key_checks=1;
939 #server prints a bad error with temporary tables for a copy alter failure. mask it.
940 --replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
941 --error ER_NO_REFERENCED_ROW_2
942 ALTER TABLE address ALGORITHM=copy,
943 ADD CONSTRAINT `ca_fk2` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
944 ON DELETE CASCADE ON UPDATE RESTRICT;
945 set foreign_key_checks=0;
946
947 # Turn off foreign_key_checks and create the fk anyway.
948 ALTER TABLE address ALGORITHM=copy,
949 ADD CONSTRAINT `ca_fk2` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
950 ON DELETE CASCADE ON UPDATE RESTRICT;
951
952 # Some more alters to exercise the "copy fk functions"
953 ALTER TABLE address ALGORITHM=inplace,
954 ADD COLUMN a int COLUMN_FORMAT DYNAMIC;
955 ALTER TABLE address ALGORITHM=copy,
956 ADD COLUMN b int not null;
957
958 --echo # Check DML with foreign_key_checks on
959 set foreign_key_checks=1;
960
961 # Try to point first address to another customer which does not exists
962 --replace_regex /ca_fk[1-2]/<constraint_name>/
963 --error ER_NO_REFERENCED_ROW_2
964 update address set customer_id = 3 where no = 1;
965
966 # Point second address to first customer
967 update address set customer_id = 1 where no = 2;
968
969 # Delete customer, should delete two rows from address -> table empty
970 delete from customer where id = 1;
971 select count(*) from address;
972
973 set foreign_key_checks=0;
974
975 drop table address, customer;
976
977 DROP VIEW ndb_show_tables_results;