# bug#16285826 create table t1 ( a1 int primary key ) engine ndb; insert into t1 (a1) values (1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 ( a2 int primary key auto_increment, b2 int not null, key xb2 (b2), constraint fkb2a1 foreign key (b2) references t1 (a1) ) engine ndb; # TEST: basic meta # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 0 # run backup # drop tables drop table t2, t1; # restore meta # restore data # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 0 # TEST: basic data insert into t2 (b2) select x.a1 from t1 x,t1 y; insert into t2 (b2) values (10); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 81 # run backup # drop tables drop table t2, t1; # restore meta # restore data # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 81 insert into t2 (b2) values (10); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # TEST: child part key drop table t2; create table t2 ( a2 int primary key auto_increment, b2 int not null, c2 int not null, key xb2c2 (b2,c2), constraint fkb2a1 foreign key (b2) references t1 (a1) ) engine ndb; insert into t2 (b2,c2) select x.a1,99 from t1 x,t1 y; insert into t2 (b2,c2) values (10,99); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 81 # run backup # drop tables drop table t2, t1; # restore meta # restore data # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2a1' # show counts select count(*) from t1; count(*) 9 select count(*) from t2; count(*) 81 insert into t2 (b2,c2) values (10,99); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # TEST: column order drop table t2,t1; create table t1 ( a1 int primary key, b1 int not null, c1 int not null, unique key (b1,c1) using hash ) engine ndb; insert into t1 (a1,b1,c1) values (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62); create table t2 ( a2 int primary key auto_increment, b2 int not null, c2 int not null, key xb2c2 (b2,c2), constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1) ) engine ndb; insert into t2 (b2,c2) select x.c1,x.b1 from t1 x,t1 y; insert into t2 (b2,c2) values (61,62); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`) ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2c1b1' # show counts select count(*) from t1; count(*) 6 select count(*) from t2; count(*) 36 # run backup # drop tables drop table t2, t1; # restore meta # restore data # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`) ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2c1b1' # show counts select count(*) from t1; count(*) 6 select count(*) from t2; count(*) 36 insert into t2 (b2,c2) values (61,62); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # TEST: disable indexes # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`) ) ENGINE=ndbcluster AUTO_INCREMENT=514 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2c1b1' # show counts select count(*) from t1; count(*) 6 select count(*) from t2; count(*) 36 # run backup # drop tables drop table t2, t1; # restore meta (disable indexes) # restore data (disable indexes) # show meta select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name # show counts # TEST: rebuild indexes meta drop table t2, t1; create table t1 ( a1 int primary key, b1 int not null, c1 int not null, unique key (b1,c1) using hash ) engine ndb; create table t2 ( a2 int primary key auto_increment, b2 int not null, c2 int not null, key xb2c2 (b2,c2), constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1) ) engine ndb; # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2c1b1' # show counts select count(*) from t1; count(*) 0 select count(*) from t2; count(*) 0 # run backup # drop tables drop table t2, t1; # restore meta (disable indexes) # restore data (disable indexes) # rebuild indexes # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2c1b1' # show counts select count(*) from t1; count(*) 0 select count(*) from t2; count(*) 0 # TEST: rebuild indexes data alter table t2 algorithm=inplace, drop foreign key fkb2c2c1b1; alter table t2 algorithm=inplace, add constraint fkb2c2b1c1 foreign key (b2,c2) references t1 (b1,c1); insert into t1 (a1,b1,c1) values (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62); insert into t2 (b2,c2) select x.b1,x.c1 from t1 x,t1 y; insert into t2 (b2,c2) values (62,61); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`b1`, `c1`) ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2b1c1' # show counts select count(*) from t1; count(*) 6 select count(*) from t2; count(*) 36 # run backup # drop tables drop table t2, t1; # restore meta (disable indexes) # restore data (disable indexes) # rebuild indexes # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int NOT NULL AUTO_INCREMENT, `b2` int NOT NULL, `c2` int NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`b1`, `c1`) ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fkb2c2b1c1' # show counts select count(*) from t1; count(*) 6 select count(*) from t2; count(*) 36 insert into t2 (b2,c2) values (62,61); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION) drop table t2, t1; # # BUG#18560951 NDB_RESTORE INCLUDES FK FOR ALL DATABASES WHEN USING --INCLUDE-* OR --EXCLUDE # # create database db1 with tables t1,t2 create database db1; create table db1.t1 ( a1 int primary key ) engine ndb; create table db1.t2 ( a1 int, foreign key fk1(a1) references t1(a1) ) engine ndb; # create database db2 with table tb1 create database db2; create table db2.t1 ( a1 int primary key ) engine ndb; # take backup and drop the tables drop table db1.t2, db1.t1, db2.t1; # now try restoring only the database db2 # verify the tables present show create table db2.t1; Table Create Table t1 CREATE TABLE `t1` ( `a1` int NOT NULL, PRIMARY KEY (`a1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci drop table db2.t1; # restore particular table and check for fk problem # verify the tables present show create table db1.t1; Table Create Table t1 CREATE TABLE `t1` ( `a1` int NOT NULL, PRIMARY KEY (`a1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci show create table db2.t1; Table Create Table t1 CREATE TABLE `t1` ( `a1` int NOT NULL, PRIMARY KEY (`a1`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci drop table db1.t1, db2.t1; # verify that restoring table with FK whose parent doesn't exist, fails # cleanup drop database db1; drop database db2; # Bug#26974491 FIXES DATA RESTORE WITH DISABLE INDEXES create database db3; use db3; create table t4 ( id int primary key auto_increment ) engine=ndb; create table t5 ( id int primary key auto_increment, val int, constraint fkt3t2 foreign key (val) references t4 (id) )engine=ndb; insert into t4 values (1), (2), (3), (4), (5); insert into t5 values (1,1), (2,2), (3,3), (4,4), (5,5); # take backup and drop the tables drop table db3.t5, db3.t4; # Create more tables before restore in order to verify that ndb_restore restores foreign keys # appropriately even though the table ids of parent and child tables which make up the name # of the foreign key in the format, //fk_name are different # in backup files than the table ids restored. create table db3.t6 ( id int primary key auto_increment ) engine=ndb; create table db3.t7 ( id int primary key auto_increment ) engine=ndb; create table db3.t8 ( id int primary key auto_increment ) engine=ndb; create table db3.t9 ( id int primary key auto_increment ) engine=ndb; # Use case #1: Meta restore done with --disable-indexes (recommended) # Drop the tables to retore them again with a different use case drop table db3.t5, db3.t4; # Use case #2: Separate --disable-indexes step after metadata restore # Drop the tables to retore them again with a different use case drop table db3.t5, db3.t4; # Use case #3: Data restore with --disable-indexes # Drop the tables to retore them again with a different use case drop table db3.t5, db3.t4; # Use case #4: Data restore with --disable-indexes and Data restore with --rebuild-indexes # Drop the tables to retore them again with a different use case drop table db3.t5, db3.t4; # Use case #5: Both metadata and data restore with both --disable-indexes and --rebuild-indexes # cleanup drop table db3.t6, db3.t7, db3.t8, db3.t9; drop database db3; # # Bug#36345882 Fix column ordering in Dbdict::buildFK_prepare # use test; create table t1 ( b1 int as (b) stored not null, c1 int as (c) stored not null, a1 int as (a) stored not null, b2 int as (b) stored not null, c2 int as (c) stored not null, a2 int as (a) stored not null, a int default 0, b int default 0, c int default 0, primary key pk1 (a1,b1,c1) using hash, unique key uk2 (a2,b2,c2) using hash ) engine=ndbcluster; create table t2 ( a int default 0, b int default 0, c int default 0, d int default 0, e int default 0, f int default 0, b1 int as (b) stored not null, c1 int as (c) stored not null, a1 int as (a) stored not null, b2 int as (b) stored not null, c2 int as (c) stored not null, a2 int as (a) stored not null, b3 int as (b) stored not null, c3 int as (c) stored not null, a3 int as (a) stored not null, primary key pk1 (a1,b1,c1) using hash, unique key uk2 (a2,b2,c2) using hash, key ok3 (a3,b3,c3), key ok3def (c3,b3,a3,d,e,f), constraint fk11 foreign key (c1,b1,a1) references t1 (c1,b1,a1), constraint fk21 foreign key (c2,b2,a2) references t1 (c1,b1,a1), constraint fk31 foreign key (c3,b3,a3) references t1 (c1,b1,a1), constraint fk12 foreign key (c1,b1,a1) references t1 (c2,b2,a2), constraint fk22 foreign key (c2,b2,a2) references t1 (c2,b2,a2), constraint fk32 foreign key (c3,b3,a3) references t1 (c2,b2,a2) ) engine=ndbcluster; alter table t2 drop index ok3; alter table t2 drop index fk12; alter table t2 drop index fk22; -- t1 -- Version: Any Fragment type: HashMapPartition K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 9 Number of primary keys: 3 Length of frm data: XXX Max Rows: 0 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 PartitionCount: 8 FragmentCount: 8 PartitionBalance: FOR_RP_BY_LDM ExtraRowGciBits: 0 ExtraRowAuthorBits: 0 TableStatus: Retrieved Table options: readbackup HashMap: DEFAULT-HASHMAP-3840-8 -- Attributes -- b1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY c1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY a1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY b2 Int NOT NULL AT=FIXED ST=MEMORY c2 Int NOT NULL AT=FIXED ST=MEMORY a2 Int NOT NULL AT=FIXED ST=MEMORY a Int NULL AT=FIXED ST=MEMORY DEFAULT 0 b Int NULL AT=FIXED ST=MEMORY DEFAULT 0 c Int NULL AT=FIXED ST=MEMORY DEFAULT 0 -- Indexes -- PRIMARY KEY(b1, c1, a1) - UniqueHashIndex uk2$unique(b2, c2, a2) - UniqueHashIndex -- t2 -- Version: Any Fragment type: HashMapPartition K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 15 Number of primary keys: 3 Length of frm data: XXX Max Rows: 0 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 PartitionCount: 8 FragmentCount: 8 PartitionBalance: FOR_RP_BY_LDM ExtraRowGciBits: 0 ExtraRowAuthorBits: 0 TableStatus: Retrieved Table options: readbackup HashMap: DEFAULT-HASHMAP-3840-8 -- Attributes -- a Int NULL AT=FIXED ST=MEMORY DEFAULT 0 b Int NULL AT=FIXED ST=MEMORY DEFAULT 0 c Int NULL AT=FIXED ST=MEMORY DEFAULT 0 d Int NULL AT=FIXED ST=MEMORY DEFAULT 0 e Int NULL AT=FIXED ST=MEMORY DEFAULT 0 f Int NULL AT=FIXED ST=MEMORY DEFAULT 0 b1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY c1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY a1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY b2 Int NOT NULL AT=FIXED ST=MEMORY c2 Int NOT NULL AT=FIXED ST=MEMORY a2 Int NOT NULL AT=FIXED ST=MEMORY b3 Int NOT NULL AT=FIXED ST=MEMORY c3 Int NOT NULL AT=FIXED ST=MEMORY a3 Int NOT NULL AT=FIXED ST=MEMORY -- Indexes -- PRIMARY KEY(b1, c1, a1) - UniqueHashIndex uk2$unique(b2, c2, a2) - UniqueHashIndex ok3def(c3, b3, a3, d, e, f) - OrderedIndex -- ForeignKeys -- XX/XX/fk11 PRIMARY KEY (c1, b1, a1) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction XX/XX/fk21 uk2$unique (c2, b2, a2) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction XX/XX/fk31 ok3def (c3, b3, a3) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction XX/XX/fk12 PRIMARY KEY (c1, b1, a1) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction XX/XX/fk22 uk2$unique (c2, b2, a2) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction XX/XX/fk32 ok3def (c3, b3, a3) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction insert into t1 (a, b, c) values (11, 12, 13); insert into t2 (a, b, c) values (11, 12, 13); insert into t2 (a,b,c) values (101, 102, 103); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk11` FOREIGN KEY (`c1`,`b1`,`a1`) REFERENCES `t1` (`c1`,`b1`,`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int DEFAULT '0', `b` int DEFAULT '0', `c` int DEFAULT '0', `d` int DEFAULT '0', `e` int DEFAULT '0', `f` int DEFAULT '0', `b1` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c1` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a1` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, `b2` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c2` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a2` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, `b3` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c3` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a3` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, PRIMARY KEY (`a1`,`b1`,`c1`) USING HASH, UNIQUE KEY `uk2` (`a2`,`b2`,`c2`) USING HASH, KEY `ok3def` (`c3`,`b3`,`a3`,`d`,`e`,`f`), CONSTRAINT `fk11` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk12` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c2`, `b2`, `a2`), CONSTRAINT `fk21` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk22` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c2`, `b2`, `a2`), CONSTRAINT `fk31` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk32` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c2`, `b2`, `a2`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fk11' 'ForeignKey' 'XX/XX/fk12' 'ForeignKey' 'XX/XX/fk21' 'ForeignKey' 'XX/XX/fk22' 'ForeignKey' 'XX/XX/fk31' 'ForeignKey' 'XX/XX/fk32' # show counts select count(*) from t1; count(*) 1 select count(*) from t2; count(*) 1 # run backup # drop tables drop table t2, t1; # restore meta (disable indexes) # restore data (disable indexes) # rebuild indexes # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int DEFAULT '0', `b` int DEFAULT '0', `c` int DEFAULT '0', `d` int DEFAULT '0', `e` int DEFAULT '0', `f` int DEFAULT '0', `b1` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c1` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a1` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, `b2` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c2` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a2` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, `b3` int GENERATED ALWAYS AS (`b`) STORED NOT NULL, `c3` int GENERATED ALWAYS AS (`c`) STORED NOT NULL, `a3` int GENERATED ALWAYS AS (`a`) STORED NOT NULL, PRIMARY KEY (`a1`,`b1`,`c1`) USING HASH, UNIQUE KEY `uk2` (`a2`,`b2`,`c2`) USING HASH, KEY `ok3def` (`c3`,`b3`,`a3`,`d`,`e`,`f`), CONSTRAINT `fk11` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk12` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c2`, `b2`, `a2`), CONSTRAINT `fk21` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk22` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c2`, `b2`, `a2`), CONSTRAINT `fk31` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c1`, `b1`, `a1`), CONSTRAINT `fk32` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c2`, `b2`, `a2`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select type,name from ndb_show_tables_results where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger'''); type name 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX' 'ForeignKey' 'XX/XX/fk11' 'ForeignKey' 'XX/XX/fk12' 'ForeignKey' 'XX/XX/fk21' 'ForeignKey' 'XX/XX/fk22' 'ForeignKey' 'XX/XX/fk31' 'ForeignKey' 'XX/XX/fk32' # show counts select count(*) from t1; count(*) 1 select count(*) from t2; count(*) 1 select * from t1; b1 c1 a1 b2 c2 a2 a b c 12 13 11 12 13 11 11 12 13 select * from t2; a b c d e f b1 c1 a1 b2 c2 a2 b3 c3 a3 11 12 13 0 0 0 12 13 11 12 13 11 12 13 11 drop table t2,t1; Show effect of --skip-fk-checks ndb_restore option create table parent ( a int primary key, b int) engine=ndb; create table child ( x int primary key, b int, c int, foreign key(b) references parent(a)) engine=ndb; insert into parent values (1,1), (2,2), (3,3), (4,4); insert into child values (1,1,1), (2,2,2), (3,3,3), (4,4,4); Go to the dark side of MySQL set foreign_key_checks=0; insert into child values (5,5,5); delete from parent where a=4; set foreign_key_checks=1; select * from parent order by a; a b 1 1 2 2 3 3 select * from child order by x; x b c 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 # take backup and drop the tables drop table child; drop table parent; # Now try restoring # Restore schema, with indexes disabled # Restore data - should succeed # Rebuild indexes + constraints - will fail # Show that we can get round this by skipping the whole child table # Disable so that we can try again # Try again with skip-fk-checks Check data select * from parent order by a; a b 1 1 2 2 3 3 select * from child order by x; x b c 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 Show constraint active insert into child values (10,10,10); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from parent where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) Try adding duplicate FK - will fail due to inconsistencies ALTER TABLE child ADD FOREIGN KEY(b) references parent(a); ERROR HY000: Cannot add foreign key constraint Fix data insert into parent values (4,4), (5,5); select * from parent order by a; a b 1 1 2 2 3 3 4 4 5 5 select * from child order by x; x b c 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 Try adding duplicate FK again ALTER TABLE child ADD FOREIGN KEY(b) references parent(a); Cleanup drop table child; drop table parent; Show that --restore-meta with --skip-fk-checks behaves the same as --restore-meta select * from parent; a b select * from child; x b c insert into parent values (1,1); insert into child values (1,1,1); Show constraint active insert into child values (10,10,10); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from parent where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) Cleanup delete from child; delete from parent; drop table child; drop table parent;