Server IP : 104.21.38.3 / Your IP : 172.70.147.201 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/ndb/r/ |
Upload File : |
drop table if exists t2, t1; # 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), foreign key fkb2a1 (b2) references t1 (a1) ) engine ndb; # TEST: basic meta # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 (Unknown error code) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2` (`b2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=97 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 (Unknown error code) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # 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), foreign key fkb2a1 (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 (Unknown error code) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=97 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 (Unknown error code) delete from t1 where a1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # 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), foreign key fkb2c2c1b1 (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 (Unknown error code) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=65 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 (Unknown error code) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # TEST: disable indexes # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=66 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 ndb_show_tables completed..... 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), foreign key fkb2c2c1b1 (b2,c2) references t1 (c1,b1) ) engine ndb; # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 foreign key fkb2c2b1c1 (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 (Unknown error code) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) # show meta show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a2` int(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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(11) NOT NULL AUTO_INCREMENT, `b2` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`a2`), KEY `xb2c2` (`b2`,`c2`), CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster AUTO_INCREMENT=65 DEFAULT CHARSET=latin1 ndb_show_tables completed..... 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 (Unknown error code) delete from t1 where b1=61 and c1=62; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) drop table t2, t1;