Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2 -- source suite/ndb/include/backup_restore_setup.inc
3
4 -- disable_warnings
5 drop table if exists t2, t1;
6 -- enable_warnings
7
8 # Record DISK_RECORD usage (used by FK schema objects).
9 select SUM(used) INTO @used_dr from ndbinfo.resources
10 where resource_name='DISK_RECORDS';
11
12 # Record number of schema object records in use related to foreign keys.
13 select SUM(used) INTO @used_fk from ndbinfo.ndb$pools
14 where (pool_name like '%foreign%' or pool_name like '%trigger%');
15
16 # BUG 14095785 - DROPPING FK WITHOUT LABEL CAUSED MYSQLD TO CRASH
17 -- echo # bug#14095785
18
19 create table t1 (
20 a int not null,
21 b int not null,
22 primary key using hash (a),
23 unique key using hash (b)
24 ) engine ndb;
25
26 create table t2 (
27 a int not null,
28 b int not null,
29 primary key using hash (a),
30 key (b)
31 ) engine ndb;
32
33 alter table t2
34 add constraint foreign key (b) references t1 (b);
35
36 -- error ER_PARSE_ERROR
37 alter table t2
38 drop foreign key;
39
40 drop table t2, t1;
41
42 # BUG 14095838 - CHANGING PK CAUSES CASCADE FK TO DELETE ROWS
43 -- echo # bug#14095838
44
45 create table t1 (
46 a int not null,
47 primary key using hash (a)
48 ) engine ndb;
49
50 create table t2 (
51 a int not null,
52 b int not null,
53 primary key using hash (a),
54 key (b)
55 ) engine ndb;
56
57 -- error ER_CANNOT_ADD_FOREIGN
58 alter table t2
59 add constraint fk1 foreign key (b) references t1 (a)
60 on delete cascade on update cascade;
61 show warnings;
62
63 alter table t2
64 add constraint fk1 foreign key (b) references t1 (a)
65 on delete cascade on update restrict;
66
67 drop table t2, t1;
68
69 # Bug #14195170 FAILING CREATE TABLE WITH FK CONSTRAINTS LEAVES ORPHANED .FRM
70 --echo # bug#14195170
71
72 CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=NDB;
73
74 CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
75 price DECIMAL, PRIMARY KEY(category, id)) ENGINE=NDB;
76
77 --error ER_CANNOT_ADD_FOREIGN
78 CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
79 show warnings;
80
81 --error ER_CANNOT_ADD_FOREIGN
82 CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
83 show warnings;
84
85 DROP TABLE customer, product;
86
87 # Bug #14124623 - CREATING TABLE WITH FK CONSTRAINT FAILS AND LEAVES DB CORRUPT
88 --echo # bug#14124623
89
90 CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
91 country VARCHAR(30), INDEX county_country_index(country)) ENGINE=ndb;
92
93 INSERT INTO counties VALUES
94 ('Berkshire','England'),('Shropshire','England'),('Oxfordshire','England'),('Buckinghamshire','England');
95
96 CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county
97 VARCHAR(30), INDEX county_county_index (county), CONSTRAINT county_town
98 FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE
99 RESTRICT) ENGINE=ndb;
100
101 INSERT INTO towns VALUES
102 ('Maidenhead','Berkshire'),('Reading','Berkshire'),('Shrewsbury','Shropshire')
103 ,('Oxford','Oxfordshire');
104
105 --error ER_FK_NO_INDEX_PARENT
106 CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
107 country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
108 country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
109 RESTRICT ON UPDATE RESTRICT) engine=ndb;
110
111 --error ER_FK_NO_INDEX_PARENT
112 CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
113 country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
114 country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
115 RESTRICT ON UPDATE RESTRICT) engine=ndb;
116
117 CREATE TABLE languages (id INT NOT NULL PRIMARY KEY) engine=ndb;
118
119 drop table languages, towns, counties;
120
121 --echo # test: child vs parent column types
122
123 create table t1 (
124 a int not null,
125 b int not null,
126 primary key using hash (a),
127 unique key using hash (b)
128 ) engine ndb;
129
130 --error ER_FK_INCOMPATIBLE_COLUMNS
131 create table t2 (
132 a int not null,
133 b char(20) not null,
134 primary key using hash (a),
135 key (b),
136 constraint fk1 foreign key (b) references t1 (b)
137 ) engine ndb;
138
139 create table t2 (
140 a int not null,
141 b char(10) not null,
142 primary key using hash (a),
143 key (b)
144 ) engine ndb;
145
146 --error ER_FK_INCOMPATIBLE_COLUMNS
147 alter table t2 algorithm=inplace,
148 add constraint fk1 foreign key (b) references t1 (b);
149
150 drop table t2, t1;
151
152 --echo # test: parent table not exist
153
154 --error ER_FK_CANNOT_OPEN_PARENT
155 create table t2 (
156 a int not null,
157 b int not null,
158 primary key using hash (a),
159 key (b),
160 constraint fk1 foreign key (b) references t1 (b)
161 ) engine ndb;
162
163 create table t2 (
164 a int not null,
165 b int not null,
166 primary key using hash (a),
167 key (b)
168 ) engine ndb;
169
170 --error ER_FK_CANNOT_OPEN_PARENT
171 alter table t2 algorithm=inplace,
172 add constraint fk1 foreign key (b) references t1 (b);
173
174 drop table t2;
175
176 --echo # test: parent column not exist
177
178 create table t1 (
179 a int not null,
180 primary key using hash (a)
181 ) engine ndb;
182
183 --error ER_FK_NO_COLUMN_PARENT
184 create table t2 (
185 a int not null,
186 b int not null,
187 primary key using hash (a),
188 key (b),
189 constraint fk1 foreign key (b) references t1 (b)
190 ) engine ndb;
191
192 create table t2 (
193 a int not null,
194 b int not null,
195 primary key using hash (a),
196 key (b)
197 ) engine ndb;
198
199 --error ER_FK_NO_COLUMN_PARENT
200 alter table t2 algorithm=inplace,
201 add constraint fk1 foreign key (b) references t1 (b);
202
203 drop table t2, t1;
204
205 --echo # test: parent key no index
206
207 create table t1 (
208 a int not null,
209 b int not null,
210 primary key using hash (a)
211 ) engine ndb;
212
213 --error ER_FK_NO_INDEX_PARENT
214 create table t2 (
215 a int not null,
216 b int not null,
217 primary key using hash (a),
218 key (b),
219 constraint fk1 foreign key (b) references t1 (b)
220 ) engine ndb;
221
222 create table t2 (
223 a int not null,
224 b int not null,
225 primary key using hash (a),
226 key (b)
227 ) engine ndb;
228
229 --error ER_FK_NO_INDEX_PARENT
230 alter table t2 algorithm=inplace,
231 add constraint fk1 foreign key (b) references t1 (b);
232
233 drop table t2, t1;
234
235 --echo # test: parent key no unique index
236
237 create table t1 (
238 a int not null,
239 b int not null,
240 primary key using hash (a),
241 key (b)
242 ) engine ndb;
243
244 --error ER_FK_NO_INDEX_PARENT
245 create table t2 (
246 a int not null,
247 b int not null,
248 primary key using hash (a),
249 key (b),
250 constraint fk1 foreign key (b) references t1 (b)
251 ) engine ndb;
252
253 create table t2 (
254 a int not null,
255 b int not null,
256 primary key using hash (a),
257 key (b)
258 ) engine ndb;
259
260 --error ER_FK_NO_INDEX_PARENT
261 alter table t2 algorithm=inplace,
262 add constraint fk1 foreign key (b) references t1 (b);
263
264 drop table t2, t1;
265
266 --echo # test: child column not exist
267
268 create table t1 (
269 a int not null,
270 b int not null,
271 primary key using hash (a),
272 unique key using hash (b)
273 ) engine ndb;
274
275 --error ER_KEY_COLUMN_DOES_NOT_EXITS
276 create table t2 (
277 a int not null,
278 b int not null,
279 primary key using hash (a),
280 key (b),
281 constraint fk1 foreign key (c) references t1 (b)
282 ) engine ndb;
283 show warnings;
284
285 create table t2 (
286 a int not null,
287 b int not null,
288 primary key using hash (a),
289 key (b)
290 ) engine ndb;
291
292 --error ER_KEY_COLUMN_DOES_NOT_EXITS
293 alter table t2 algorithm=inplace,
294 add constraint fk1 foreign key (c) references t1 (b);
295 show warnings;
296
297 drop table t2, t1;
298
299 --echo # test: child column no index
300 --echo # no error - server creates KEY
301
302 create table t1 (
303 a int not null,
304 b int not null,
305 primary key using hash (a),
306 unique key using hash (b)
307 ) engine ndb;
308
309 create table t2 (
310 a int not null,
311 b int not null,
312 primary key using hash (a),
313 constraint fk1 foreign key (b) references t1 (b)
314 ) engine ndb;
315
316 drop table t2;
317
318 create table t2 (
319 a int not null,
320 b int not null,
321 primary key using hash (a)
322 ) engine ndb;
323
324 alter table t2 algorithm=inplace,
325 add constraint fk1 foreign key (b) references t1 (b);
326
327 drop table t2, t1;
328
329 --echo # test: child vs parent column count
330
331 create table t1 (
332 a int not null,
333 b int not null,
334 c int not null,
335 primary key using hash (a),
336 unique key using hash (b, c)
337 ) engine ndb;
338
339 --error ER_FK_NO_INDEX_PARENT
340 create table t2 (
341 a int not null,
342 b int not null,
343 primary key using hash (a),
344 key (b),
345 constraint fk1 foreign key (b) references t1 (b)
346 ) engine ndb;
347
348 create table t2 (
349 a int not null,
350 b int not null,
351 primary key using hash (a),
352 key (b)
353 ) engine ndb;
354
355 --error ER_FK_NO_INDEX_PARENT
356 alter table t2 algorithm=inplace,
357 add constraint fk1 foreign key (b) references t1 (b);
358
359 drop table t2, t1;
360
361 # Bug 16246874 - MYSQLD CRASH WHEN QUERYING KEY_COLUMN_USAGE IF NDB FOREIGN KEYS DEFINED
362 --echo # bug#16246874
363 create table t1 (
364 id int primary key
365 ) engine=ndb;
366
367 create table t2 (
368 id int,
369 foreign key(id) references t1(id)
370 ) engine=ndb;
371
372 # Run failing query, discard result since it will vary
373 --disable_result_log
374 select * from information_schema.key_column_usage;
375 --enable_result_log
376
377 # Show that t2 references test.t1.id
378 select REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
379 from information_schema.key_column_usage
380 where TABLE_SCHEMA = 'test' and TABLE_NAME = 't2';
381
382 drop table t2, t1;
383
384 # Bug#16275509 IMPLEMENT HA_NDBCLUSTER::CAN_SWITCH_ENGINES
385
386 --echo # bug#16275509 - case: alter engine from ndb
387
388 create table t1 (
389 a int not null,
390 primary key using hash (a)
391 ) engine=ndb;
392
393 create table t2 (
394 a int not null,
395 primary key using hash (a),
396 foreign key fk1 (a) references t1 (a)
397 ) engine=ndb;
398
399 --error ER_FK_CANNOT_CHANGE_ENGINE
400 alter table t1 engine=innodb;
401
402 --error ER_FK_CANNOT_CHANGE_ENGINE
403 alter table t2 engine=innodb;
404
405 drop table t2, t1;
406
407 create table t1 (
408 a int primary key,
409 b int,
410 key (b),
411 foreign key fk1 (b) references t1 (a)
412 ) engine=ndb;
413
414 --error ER_FK_CANNOT_CHANGE_ENGINE
415 alter table t1 engine=innodb;
416
417 drop table t1;
418
419 # unlike innodb
420 --echo # bug#16275509 - case: allow FKs on partitioned
421
422 set new=on;
423
424 # partition parent
425
426 create table t1 (
427 a int primary key
428 ) engine=ndb
429 partition by hash (a) partitions 5;
430
431 create table t2 (
432 a int primary key,
433 foreign key fk1 (a) references t1 (a)
434 ) engine=ndb;
435
436 drop table t2, t1;
437
438 create table t1 (
439 a int primary key
440 ) engine=ndb;
441
442 create table t2 (
443 a int primary key,
444 foreign key fk1 (a) references t1 (a)
445 ) engine=ndb;
446
447 alter table t1
448 partition by hash (a) partitions 5;
449
450 drop table t2, t1;
451
452 # partition child
453
454 create table t1 (
455 a int primary key
456 ) engine=ndb;
457
458 create table t2 (
459 a int primary key,
460 foreign key fk1 (a) references t1 (a)
461 ) engine=ndb
462 partition by hash (a) partitions 5;
463
464 drop table t2;
465
466 create table t2 (
467 a int primary key,
468 foreign key fk1 (a) references t1 (a)
469 ) engine=ndb;
470
471 alter table t2
472 partition by hash (a) partitions 5;
473
474 drop table t2, t1;
475
476 # Bug#16275684 - IMPLEMENT HA_NDBCLUSTER::GET_PARENT_FOREIGN_KEY_LIST
477
478 # truncate of NDB table does not hit get_parent_foreign_key_list().
479 # alter child FK to not null is tested in ndb_fk_alter.test.
480
481 --echo # bug#16275684 - case: alter FK parent column
482
483 create table t1 (
484 a int primary key,
485 b enum('a','b','c') not null,
486 unique key (b)
487 ) engine=ndb;
488
489 create table t2 (
490 a int primary key,
491 b enum('a','b','c') not null,
492 key (b),
493 constraint fk1 foreign key (b) references t1 (b)
494 ) engine=ndb;
495
496 insert into t1 values (1,'a'),(2,'b');
497 insert into t2 values (1,'a'),(2,'a'),(3,'b');
498
499 --error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
500 alter table t1 modify column b enum('a','b') not null;
501 alter table t2 drop foreign key fk1;
502 alter table t1 modify column b enum('a','b') not null;
503 --disable_warnings
504 show create table t1;
505 --enable_warnings
506
507 select * from t1 order by a;
508 select * from t2 order by a;
509
510 drop table t2, t1;
511
512 # Bug#16286164 - DROP/ALTER TABLE NOT PROTECTED FROM BREAKING NDB FK CONSTRAINTS
513
514 --echo # bug#16286164 - case: drop when referenced by different table
515 let $i=2;
516 while ($i)
517 {
518 eval set @i=$i;
519 let $engine=`select if(@i=1,'ndb','innodb')`;
520
521 eval create table t1 (
522 a int not null,
523 primary key using hash (a)
524 ) engine=$engine;
525
526 eval create table t2 (
527 a int not null,
528 b int,
529 primary key using hash (a),
530 key (b),
531 constraint fk1 foreign key (b) references t1 (a)
532 ) engine=$engine;
533
534 --error ER_FK_CANNOT_DROP_PARENT
535 drop table t1;
536
537 drop table t2, t1;
538 dec $i;
539 }
540
541 --echo # bug#16286164 - case: drop when referenced by same table
542 let $i=2;
543 while ($i)
544 {
545 eval set @i=$i;
546 let $engine=`select if(@i=1,'ndb','innodb')`;
547
548 eval create table t1 (
549 a int not null,
550 b int,
551 primary key using hash (a),
552 key (b),
553 constraint fk1 foreign key (b) references t1 (a)
554 ) engine=$engine;
555
556 drop table t1;
557
558 dec $i;
559 }
560
561 -- echo # BugXXZ - multi part unique index parent
562
563 create table t1 (
564 a int not null,
565 b int not null,
566 primary key using hash (a),
567 unique key using hash (a,b)
568 ) engine ndb;
569
570 create table t2 (
571 a int not null,
572 b int not null,
573 primary key using hash (a),
574 key (a,b),
575 foreign key fk1 (a,b) references t1 (a,b)
576 ) engine ndb;
577
578 drop table t2, t1;
579
580 --echo # child-part-key
581
582 create table t1 (
583 a int not null,
584 primary key using hash (a)
585 ) engine ndb;
586
587 insert into t1 values (1),(2);
588
589 create table t2 (
590 a int not null,
591 b int not null,
592 c int not null,
593 primary key using hash (a),
594 key xbc (b,c),
595 constraint fk1 foreign key (b) references t1 (a)
596 ) engine ndb;
597
598 # uses index xbc
599 --disable_warnings
600 show create table t2;
601 --enable_warnings
602
603 insert into t2 (a,b,c) values (1,1,11),(2,2,21),(3,2,22);
604 --error ER_NO_REFERENCED_ROW_2
605 insert into t2 (a,b,c) values (4,3,33);
606 --error ER_ROW_IS_REFERENCED_2
607 delete from t1 where a = 1;
608
609 drop table t2;
610
611 create table t2 (
612 a int not null,
613 b int not null,
614 c int not null,
615 primary key using hash (a),
616 key xbc (b,c),
617 constraint fk1 foreign key (c) references t1 (a)
618 ) engine ndb;
619
620 # creates index fk1 (c)
621 --disable_warnings
622 show create table t2;
623 --enable_warnings
624
625 insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
626 --error ER_NO_REFERENCED_ROW_2
627 insert into t2 (a,c,b) values (4,3,33);
628 --error ER_ROW_IS_REFERENCED_2
629 delete from t1 where a = 1;
630
631 drop table t2;
632
633 # partial hash key does not work
634 # XXX maybe should create index fk1 (b)
635 --error ER_FK_NO_INDEX_CHILD
636 create table t2 (
637 a int not null,
638 b int not null,
639 c int not null,
640 primary key using hash (a),
641 unique key xbc (b,c) using hash,
642 constraint fk1 foreign key (b) references t1 (a)
643 ) engine ndb;
644
645 create table t2 (
646 a int not null,
647 b int not null,
648 c int not null,
649 primary key using hash (a),
650 unique key xbc (b,c) using hash,
651 constraint fk1 foreign key (c) references t1 (a)
652 ) engine ndb;
653
654 # creates index fk1 (c)
655 --disable_warnings
656 show create table t2;
657 --enable_warnings
658
659 insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
660 --error ER_NO_REFERENCED_ROW_2
661 insert into t2 (a,c,b) values (4,3,33);
662 --error ER_ROW_IS_REFERENCED_2
663 delete from t1 where a = 1;
664
665 drop table t2, t1;
666
667 #
668 # failed create table pollutes dict cache
669 #
670 create table t1_parent (pk1 char(255) primary key) charset=latin1, engine = ndb;
671
672 --error ER_CANT_CREATE_TABLE
673 create table t1 (
674 c50 char(255) column_format fixed, c49 char(255) column_format fixed, c48 char(255) column_format fixed, c47 char(255) column_format fixed, c46 char(255) column_format fixed,
675 c45 char(255) column_format fixed, c44 char(255) column_format fixed, c43 char(255) column_format fixed, c42 char(255) column_format fixed, c41 char(255) column_format fixed,
676 c40 char(255) column_format fixed, c39 char(255) column_format fixed, c38 char(255) column_format fixed, c37 char(255) column_format fixed, c36 char(255) column_format fixed,
677 c35 char(255) column_format fixed, c34 char(255) column_format fixed, c33 char(255) column_format fixed, c32 char(255) column_format fixed, c31 char(255) column_format fixed,
678 c30 char(255) column_format fixed, c29 char(255) column_format fixed, c28 char(255) column_format fixed, c27 char(255) column_format fixed, c26 char(255) column_format fixed,
679 c25 char(255) column_format fixed, c24 char(255) column_format fixed, c23 char(255) column_format fixed, c22 char(255) column_format fixed, c21 char(255) column_format fixed,
680 c20 char(255) column_format fixed, c19 char(255) column_format fixed, c18 char(255) column_format fixed, c17 char(255) column_format fixed, c16 char(255) column_format fixed,
681 c15 char(255) column_format fixed, c14 char(255) column_format fixed, c13 char(255) column_format fixed, c12 char(255) column_format fixed, c11 char(255) column_format fixed,
682 c10 char(255) column_format fixed, c9 char(255) column_format fixed, c8 char(255) column_format fixed, c7 char(255) column_format fixed, c6 char(255) column_format fixed,
683 c5 char(255) column_format fixed, c4 char(255) column_format fixed, c3 char(255) column_format fixed, c2 char(255) column_format fixed, c1 char(255) column_format fixed,
684 primary key using hash(c1)
685 ,foreign key(c1) references t1_parent(pk1)
686 ) charset=latin1, engine=ndb;
687 SHOW WARNINGS;
688
689 # Fails here with "Table 'test.t1' doesn't exist"
690 create table t1(a int primary key) engine = ndb;
691 drop table t1, t1_parent;
692
693 --echo # bug#17591531 - ERROR ON DROP INDEX WHEN REFERENCING FOREIGN KEYS
694
695 create table t1 (
696 a int not null,
697 b int not null,
698 unique key t1x1 (b) using hash,
699 primary key using hash (a)
700 ) engine=ndb;
701
702 create table t2 (
703 a int not null,
704 b int not null,
705 c int not null,
706 key (c),
707 key t2x1 (b),
708 primary key using hash (a),
709 constraint t2fk1 foreign key (b) references t1 (b)
710 ) engine=ndb;
711
712 --echo # try to drop indexes
713 --error ER_DROP_INDEX_FK
714 alter table t1 drop index t1x1;
715 --error ER_DROP_INDEX_FK
716 alter table t2 drop index t2x1;
717
718 insert into t1 values (1,10),(2,20),(3,30);
719 insert into t2 values (1,10,100),(2,20,200),(3,30,300);
720 --error ER_NO_REFERENCED_ROW_2
721 insert into t2 values (4,40,400);
722 --error ER_ROW_IS_REFERENCED_2
723 delete from t1 where a=1;
724
725 drop table t2, t1;
726
727 --echo # bug#17232212 - NDB DROP NON-EXISTENT FK SUCCEEDS
728 # succeeds silently before fixes
729
730 create table t1 (
731 a int primary key
732 ) engine=ndb;
733
734 --echo # algorithm=inplace
735 # innodb gives error 1091
736
737 create table t2 (
738 a int primary key,
739 b int,
740 c int,
741 key xb (b),
742 key xc (c),
743 constraint fkb foreign key (b) references t1 (a),
744 constraint fkc foreign key (c) references t1 (a)
745 ) engine=ndb;
746
747 --error ER_CANT_DROP_FIELD_OR_KEY
748 alter table t2
749 drop foreign key nosuchfk,
750 algorithm=inplace;
751
752 --disable_warnings
753 show create table t2;
754 --enable_warnings
755
756 --error ER_CANT_DROP_FIELD_OR_KEY
757 alter table t2
758 drop foreign key fkb,
759 drop foreign key nosuchfk,
760 drop foreign key fkc,
761 algorithm=inplace;
762
763 --disable_warnings
764 show create table t2;
765 --enable_warnings
766
767 drop table t2;
768
769 --echo # algorithm=copy
770 # innodb gives non-sense errors 1025 / 152
771
772 create table t2 (
773 a int primary key,
774 b int,
775 c int,
776 key xb (b),
777 key xc (c),
778 constraint fkb foreign key (b) references t1 (a),
779 constraint fkc foreign key (c) references t1 (a)
780 ) engine=ndb;
781
782 --error ER_CANT_DROP_FIELD_OR_KEY
783 alter table t2
784 drop foreign key nosuchfk,
785 algorithm=copy;
786
787 --disable_warnings
788 show create table t2;
789 --enable_warnings
790
791 --error ER_CANT_DROP_FIELD_OR_KEY
792 alter table t2
793 drop foreign key fkb,
794 drop foreign key nosuchfk,
795 drop foreign key fkc,
796 algorithm=copy;
797
798 --disable_warnings
799 show create table t2;
800 --enable_warnings
801
802 drop table t2, t1;
803
804 --echo # bug#18662582 - NDB_FK_DISABLE.TEST FAILS TO DROP FOREIGN KEY
805
806 create table t1 (
807 a int, primary key using hash (a)
808 ) engine = ndb;
809
810 create table t2 (
811 a int, primary key using hash (a)
812 ) engine = ndb;
813
814 alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
815
816 create table t3 (
817 a int, primary key using hash (a)
818 ) engine = ndb;
819
820 alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
821
822 --error ER_CANT_DROP_FIELD_OR_KEY
823 alter table t2 algorithm=inplace, drop foreign key nosuchfk;
824
825 alter table t2 algorithm=inplace, drop foreign key fkname2;
826
827 alter table t3 algorithm=inplace, drop foreign key fkname3;
828
829 drop table t1, t2, t3;
830
831 create table t1 (
832 a int, primary key using hash (a)
833 ) engine = ndb;
834
835 create table t2 (
836 a int, primary key using hash (a)
837 ) engine = ndb;
838
839 alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
840
841 create table t3 (
842 a int, primary key using hash (a)
843 ) engine = ndb;
844
845 alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
846
847 --error ER_CANT_DROP_FIELD_OR_KEY
848 alter table t2 algorithm=copy, drop foreign key nosuchfk;
849
850 alter table t2 algorithm=copy, drop foreign key fkname2;
851
852 alter table t3 algorithm=copy, drop foreign key fkname3;
853
854 drop table t1, t2, t3;
855
856 --echo # bug#18824753 - NDB_RESTORE FAILS WITH FKS WITH SAME NAME
857
858 create table t1 (a int primary key) engine=ndb;
859 create table t2 (a int primary key) engine=ndb;
860 create table t3 (a int primary key) engine=ndb;
861 create table t4 (a int primary key) engine=ndb;
862
863 alter table t2
864 add constraint myfk2 foreign key (a) references t1 (a);
865 alter table t4
866 add constraint myfk4 foreign key (a) references t3 (a);
867
868 --error ER_CANNOT_ADD_FOREIGN
869 alter table t4
870 add constraint `11/22/myfk4` foreign key (a) references t3 (a);
871 show warnings;
872
873 --echo # run backup
874 --source include/ndb_backup.inc
875
876 drop table t2, t1;
877 drop table t4, t3;
878
879 --echo # run restore meta
880 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
881
882 --disable_warnings
883 show create table t2;
884 show create table t4;
885 --enable_warnings
886
887 drop table t2, t1;
888 drop table t4, t3;
889 --source suite/ndb/include/backup_restore_cleanup.inc
890
891 --echo # bug18825966 - ADDING FK TO TABLE WITH DATA GIVES INTERNAL ERROR 208
892
893 create table t1 (
894 c0 int,
895 c1 int,
896 primary key using hash (c1)
897 ) engine=ndb;
898
899 create table t2 (
900 c0 int,
901 primary key using hash (c0)
902 ) engine=ndb;
903
904 insert into t1 (c1) values (1);
905 insert into t2 (c0) values (1);
906
907 alter table t2 add constraint foreign key fk (c0) references t1 (c1);
908
909 drop table t2, t1;
910
911 create table t1 (
912 c0 int,
913 c1 int,
914 c2 int,
915 c3 int,
916 c4 int,
917 c5 int,
918 primary key (c2,c5)
919 ) engine=ndb;
920
921 create table t2 (
922 c0 int,
923 c1 int,
924 c2 int,
925 c3 int,
926 c4 int,
927 c5 int,
928 primary key (c1,c4)
929 ) engine=ndb;
930
931 insert into t1 (c2,c5) values (1,10),(2,20),(3,30);
932 insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
933
934 alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
935
936 --error ER_NO_REFERENCED_ROW_2
937 insert into t2 (c1,c4) values (4,40);
938
939 drop table t2, t1;
940
941 create table t1 (
942 c0 int,
943 c1 int,
944 c2 int not null,
945 c3 int,
946 c4 int,
947 c5 int not null,
948 primary key (c0),
949 unique key using hash (c2,c5)
950 ) engine=ndb;
951
952 create table t2 (
953 c0 int,
954 c1 int,
955 c2 int,
956 c3 int,
957 c4 int,
958 c5 int,
959 primary key (c1,c4)
960 ) engine=ndb;
961
962 insert into t1 (c0,c2,c5) values (1,1,10),(2,2,20),(3,3,30);
963 insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
964
965 alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
966
967 --error ER_NO_REFERENCED_ROW_2
968 insert into t2 (c1,c4) values (4,40);
969
970 drop table t2, t1;
971
972 --echo #
973 --echo # BUG#21308928 : CANNOT ADD UNIQUE CONSTRAINT ON SET OF FIELDS WITH EXISTING FOREIGN KEY
974 --echo #
975
976 --echo #Create the tables
977 create table t1 (
978 a int not null primary key
979 ) engine ndb;
980
981 create table t2 (
982 a int primary key,
983 b int not null,
984 c int,
985 constraint fk1 foreign key (a) references t1(a),
986 constraint fk2 foreign key (b) references t1(a),
987 constraint fk3 foreign key (c) references t1(a)
988 ) engine ndb;
989
990 --echo #Add a unique constraint to the foreign key column
991 alter table t2 add constraint uniquet2a unique (a);
992 alter table t2 add constraint uniquet2b unique (b);
993 alter table t2 add constraint uniquet2c unique (c);
994
995 --disable_warnings
996 show create table t2;
997 --enable_warnings
998
999 --echo #Drop tables
1000 drop table t2, t1;
1001
1002 --echo #
1003 --echo # Bug#27930382 : FOREIGN KEY CONSTRAINT VIOLATIONS INCORRECTLY REPORTED
1004 --echo
1005
1006 # Foreign key constraint which self referrence its own table,
1007 # incorrectly reported constraint violation. Root cause was
1008 # that the key constraints were checked before the (unique) indexes
1009 # had been updated:
1010 #
1011 # (Test case based on the API test 'testFK')
1012
1013
1014 create table t1 (
1015 kol1 int primary key,
1016 kol2 int not null,
1017 kol3 int not null,
1018 kol4 int not null,
1019 kol5 int not null,
1020 unique key uq1(kol2),
1021 foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
1022 ) engine = ndb;
1023
1024 insert into t1 values (1,1,1,1,1);
1025 delete from t1 where kol1=1;
1026 drop table t1;
1027
1028 create table t1 (
1029 kol1 int primary key,
1030 kol2 int not null,
1031 kol3 int not null,
1032 kol4 int not null,
1033 kol5 int not null,
1034 foreign key fk1(kol3) references t1(kol1) on update restrict on delete restrict
1035 ) engine = ndb;
1036
1037 insert into t1 values (1,1,1,1,1);
1038 delete from t1 where kol1=1;
1039 drop table t1;
1040
1041 #
1042 # There were also a race condition due to local backup replicas
1043 # being read when verifying that there were no references to
1044 # a deleted row. This allowed the read to race the delete of the
1045 # index row, such that the deleted row appered to still being referred.
1046 # FIX: Always read primary replicas when verifying a constraint.
1047 create table t1 (
1048 kol1 int primary key,
1049 kol2 int not null,
1050 kol3 int not null,
1051 kol4 int not null,
1052 kol5 int not null,
1053 unique key uq1(kol2),
1054 unique key uq2(kol3),
1055 foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
1056 ) engine = ndb;
1057
1058 insert into t1 values (1,1,1,1,1);
1059 delete from t1 where kol1=1;
1060
1061 insert into t1 values (2,1,1,1,1);
1062 delete from t1 where kol1=2;
1063
1064 insert into t1 values (3,1,1,1,1);
1065 delete from t1 where kol1=3;
1066
1067 insert into t1 values (4,1,1,1,1);
1068 delete from t1 where kol1=4;
1069
1070
1071 insert into t1 values (1,0,0,0,0);
1072 insert into t1 values (2,1,1,1,1);
1073 insert into t1 values (3,2,2,2,2);
1074 insert into t1 values (4,3,3,3,3);
1075 delete from t1;
1076
1077 drop table t1;
1078
1079 # Allow DISK_RECORDS to increase from 0 to one page per DBDICT(1) and DBTC(3) on each node(2).
1080 select SUM(used) - @used_dr <= 8 from ndbinfo.resources where resource_name='DISK_RECORDS';
1081
1082 # Check no fk related schema object records left behind.
1083 select ROUND(SUM(used) - @used_fk) from ndbinfo.ndb$pools
1084 where (pool_name like '%foreign%' or pool_name like '%trigger%');
1085
1086 --remove_file $NDB_TOOLS_OUTPUT