diff options
Diffstat (limited to 'mysql-test')
67 files changed, 423 insertions, 1503 deletions
diff --git a/mysql-test/include/have_innodb.combinations b/mysql-test/include/have_innodb.combinations index f1131c448f3..7282d848e9f 100644 --- a/mysql-test/include/have_innodb.combinations +++ b/mysql-test/include/have_innodb.combinations @@ -12,8 +12,6 @@ innodb-buffer-page innodb-buffer-page-lru innodb-sys-columns innodb-sys-fields -innodb-sys-foreign -innodb-sys-foreign-cols innodb-sys-indexes innodb-sys-tables innodb-sys-virtual @@ -32,8 +30,6 @@ innodb-buffer-page innodb-buffer-page-lru innodb-sys-columns innodb-sys-fields -innodb-sys-foreign -innodb-sys-foreign-cols innodb-sys-indexes innodb-sys-tables innodb-sys-virtual diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index 802c41af05d..b827a8e2adf 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -959,7 +959,7 @@ drop table if exists t1,t2; create table t1(a int not null, b int not null, primary key (a, b)); create table t2(a int not null, b int not null, c int not null, primary key (a), foreign key fk_bug26104 (b,c) references t1(a)); -ERROR 42000: Incorrect foreign key definition for 'fk_bug26104': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'fk_bug26104' drop table t1; create table t1(f1 int,f2 int); insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3); diff --git a/mysql-test/main/foreign_key.result b/mysql-test/main/foreign_key.result index f0c6ca9eddd..3f915f82ae5 100644 --- a/mysql-test/main/foreign_key.result +++ b/mysql-test/main/foreign_key.result @@ -132,6 +132,34 @@ create table t1 (id int primary key); create table t2 (id int references t1(id)) select id from t1; flush tables; drop table t2, t1; +create database D; +create table D.T1(id int primary key); +create table t2(id int primary key, f1 int references D.T1(id)); +select * from D.T1; +id +drop table t2; +flush tables; +drop table D.T1; +drop database D; +# Check duplicate id +create or replace table t1 (id int primary key) engine innodb; +create or replace table t2 (id2 int, +constraint c foreign key (id2) references t1 (id), +constraint c foreign key (id2) references t1 (id)); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'c' +create or replace table t2 (id2 int, constraint c foreign key (id2) references t1 (id)); +alter table t2 add constraint c foreign key (id2) references t1 (id); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'c' +create or replace table t3 (id2 int, constraint c foreign key (id2) references t1 (id)); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'c' +create or replace table t3 (id2 int, constraint C foreign key (id2) references t1 (id)); +create or replace table t3 (id2 int); +alter table t2 add constraint c foreign key (id2) references t1 (id); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'c' +alter table t2 add constraint fk_t3 foreign key (id2) references t1 (id); +alter table t3 add foreign key (id2) references t1 (id); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'fk_t3' +drop tables t3, t2, t1; # Check rename column, lock tables create or replace table t1 (id int primary key); create or replace table t2 (id int primary key); @@ -312,6 +340,13 @@ ERROR HY000: Cannot drop index 'fk': needed in a foreign key constraint alter table t2 drop foreign key fk; drop tables t1, t2; # Check self-references +create or replace table t1 (f1 integer primary key references t1(f1)); +ERROR 42000: Incorrect foreign key definition for 'f1' +create or replace table t1 (f1 integer primary key, foreign key (f1) references t1(f1)); +ERROR 42000: Incorrect foreign key definition for 'f1' +create or replace table t1 (f1 integer primary key); +alter table t1 add constraint c1 foreign key (f1) references t1(f1); +ERROR 42000: Incorrect foreign key definition for 'c1' create or replace table t1 (id int primary key, id2 int references t1 (id)); flush tables t1; show create table t1; diff --git a/mysql-test/main/foreign_key.test b/mysql-test/main/foreign_key.test index 64e00b402d6..437d93e6bdb 100644 --- a/mysql-test/main/foreign_key.test +++ b/mysql-test/main/foreign_key.test @@ -169,6 +169,36 @@ create table t2 (id int references t1(id)) select id from t1; flush tables; drop table t2, t1; +create database D; +create table D.T1(id int primary key); +create table t2(id int primary key, f1 int references D.T1(id)); +select * from D.T1; +drop table t2; +flush tables; +drop table D.T1; +drop database D; + +--echo # Check duplicate id +create or replace table t1 (id int primary key) engine innodb; +--error ER_DUP_CONSTRAINT_NAME +create or replace table t2 (id2 int, + constraint c foreign key (id2) references t1 (id), + constraint c foreign key (id2) references t1 (id)); + +create or replace table t2 (id2 int, constraint c foreign key (id2) references t1 (id)); +--error ER_DUP_CONSTRAINT_NAME +alter table t2 add constraint c foreign key (id2) references t1 (id); +--error ER_DUP_CONSTRAINT_NAME +create or replace table t3 (id2 int, constraint c foreign key (id2) references t1 (id)); +create or replace table t3 (id2 int, constraint C foreign key (id2) references t1 (id)); +create or replace table t3 (id2 int); +--error ER_DUP_CONSTRAINT_NAME +alter table t2 add constraint c foreign key (id2) references t1 (id); +alter table t2 add constraint fk_t3 foreign key (id2) references t1 (id); +--error ER_DUP_CONSTRAINT_NAME +alter table t3 add foreign key (id2) references t1 (id); +drop tables t3, t2, t1; + --echo # Check rename column, lock tables create or replace table t1 (id int primary key); create or replace table t2 (id int primary key); @@ -306,6 +336,14 @@ alter table t2 drop foreign key fk; drop tables t1, t2; --echo # Check self-references +--error ER_WRONG_FK_DEF +create or replace table t1 (f1 integer primary key references t1(f1)); +--error ER_WRONG_FK_DEF +create or replace table t1 (f1 integer primary key, foreign key (f1) references t1(f1)); +create or replace table t1 (f1 integer primary key); +--error ER_WRONG_FK_DEF +alter table t1 add constraint c1 foreign key (f1) references t1(f1); + create or replace table t1 (id int primary key, id2 int references t1 (id)); flush tables t1; show create table t1; diff --git a/mysql-test/main/information_schema_all_engines-master.opt b/mysql-test/main/information_schema_all_engines-master.opt index 7ba5aa5b8b3..4c67a5559f5 100644 --- a/mysql-test/main/information_schema_all_engines-master.opt +++ b/mysql-test/main/information_schema_all_engines-master.opt @@ -9,8 +9,6 @@ --loose-innodb-lock-waits --loose-innodb-sys-columns --loose-innodb-sys-fields ---loose-innodb-sys-foreign ---loose-innodb-sys-foreign-cols --loose-innodb-sys-tables --loose-innodb-sys-tablestats --loose-innodb-mutexes diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result index 46a50716c08..50b4956f547 100644 --- a/mysql-test/main/information_schema_all_engines.result +++ b/mysql-test/main/information_schema_all_engines.result @@ -32,8 +32,6 @@ INNODB_METRICS INNODB_MUTEXES INNODB_SYS_COLUMNS INNODB_SYS_FIELDS -INNODB_SYS_FOREIGN -INNODB_SYS_FOREIGN_COLS INNODB_SYS_INDEXES INNODB_SYS_TABLES INNODB_SYS_TABLESTATS @@ -112,8 +110,6 @@ INNODB_METRICS NAME INNODB_MUTEXES NAME INNODB_SYS_COLUMNS TABLE_ID INNODB_SYS_FIELDS INDEX_ID -INNODB_SYS_FOREIGN ID -INNODB_SYS_FOREIGN_COLS ID INNODB_SYS_INDEXES INDEX_ID INNODB_SYS_TABLES TABLE_ID INNODB_SYS_TABLESTATS TABLE_ID @@ -192,8 +188,6 @@ INNODB_METRICS NAME INNODB_MUTEXES NAME INNODB_SYS_COLUMNS TABLE_ID INNODB_SYS_FIELDS INDEX_ID -INNODB_SYS_FOREIGN ID -INNODB_SYS_FOREIGN_COLS ID INNODB_SYS_INDEXES INDEX_ID INNODB_SYS_TABLES TABLE_ID INNODB_SYS_TABLESTATS TABLE_ID @@ -277,8 +271,6 @@ INNODB_METRICS information_schema.INNODB_METRICS 1 INNODB_MUTEXES information_schema.INNODB_MUTEXES 1 INNODB_SYS_COLUMNS information_schema.INNODB_SYS_COLUMNS 1 INNODB_SYS_FIELDS information_schema.INNODB_SYS_FIELDS 1 -INNODB_SYS_FOREIGN information_schema.INNODB_SYS_FOREIGN 1 -INNODB_SYS_FOREIGN_COLS information_schema.INNODB_SYS_FOREIGN_COLS 1 INNODB_SYS_INDEXES information_schema.INNODB_SYS_INDEXES 1 INNODB_SYS_TABLES information_schema.INNODB_SYS_TABLES 1 INNODB_SYS_TABLESTATS information_schema.INNODB_SYS_TABLESTATS 1 @@ -347,8 +339,6 @@ Database: information_schema | INNODB_MUTEXES | | INNODB_SYS_COLUMNS | | INNODB_SYS_FIELDS | -| INNODB_SYS_FOREIGN | -| INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_TABLESTATS | @@ -417,8 +407,6 @@ Database: INFORMATION_SCHEMA | INNODB_MUTEXES | | INNODB_SYS_COLUMNS | | INNODB_SYS_FIELDS | -| INNODB_SYS_FOREIGN | -| INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_TABLESTATS | @@ -459,5 +447,5 @@ Wildcard: inf_rmation_schema | information_schema | SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 65 +information_schema 63 mysql 31 diff --git a/mysql-test/suite/innodb/include/innodb_dict.inc b/mysql-test/suite/innodb/include/innodb_dict.inc index 1e05181272d..fa0c03a835b 100644 --- a/mysql-test/suite/innodb/include/innodb_dict.inc +++ b/mysql-test/suite/innodb/include/innodb_dict.inc @@ -5,5 +5,3 @@ INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; diff --git a/mysql-test/suite/innodb/r/add_constraint.result b/mysql-test/suite/innodb/r/add_constraint.result deleted file mode 100644 index 9d894e530c3..00000000000 --- a/mysql-test/suite/innodb/r/add_constraint.result +++ /dev/null @@ -1,13 +0,0 @@ -# -# Bug #20762798 FK DDL: CRASH IN DICT_FOREIGN_REMOVE_FROM_CACHE -# -create table t1(a int, b int, key(a),key(b))engine=innodb; -create table t2(a int, b int, key(a),key(b))engine=innodb; -alter table t2 add constraint b foreign key (b) references t1(a); -alter table t1 add constraint b1 foreign key (b) references t2(a); -alter table t2 add constraint b1 foreign key (b) references t1(a); -ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -alter table t2 drop foreign key b; -alter table t1 drop foreign key b1; -drop table t2; -drop table t1; diff --git a/mysql-test/suite/innodb/r/alter_foreign_crash.result b/mysql-test/suite/innodb/r/alter_foreign_crash.result index 824b4b091a7..7c20ba83c84 100644 --- a/mysql-test/suite/innodb/r/alter_foreign_crash.result +++ b/mysql-test/suite/innodb/r/alter_foreign_crash.result @@ -23,6 +23,5 @@ parent alter table parent row_format=dynamic; Warnings: Warning 1105 Reference hint to non-existent table `bug.child` skipped -Warning 1088 failed to load FOREIGN KEY constraints drop table parent; drop database bug; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index d3b2acb4509..8460709e510 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -6,7 +6,7 @@ create table t1 (f1 int primary key) engine=InnoDB; create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1), constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; -ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR HY000: Duplicate FOREIGN KEY constraint name 'c1' create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1)) engine=innodb; alter table t2 add constraint c1 foreign key (f1) references t1(f1); @@ -180,19 +180,19 @@ CREATE DATABASE best default character set latin1; CREATE TABLE t3 (a INT PRIMARY KEY, CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b), -FOREIGN KEY t2_ibfk_1 (a) REFERENCES test.t1(a)) ENGINE=InnoDB; +FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB; RENAME TABLE best.t2 TO test.t2; -ERROR 42S01: Table 't2' already exists -SHOW CREATE TABLE best.t2; +SHOW CREATE TABLE test.t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL, `b` text DEFAULT NULL, PRIMARY KEY (`a`), FULLTEXT KEY `b` (`b`), - CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`) + CONSTRAINT `fk_t2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP DATABASE best; +DROP TABLE t2; # # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs # @@ -215,8 +215,8 @@ DROP TABLE t3,t1; # MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N # or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN # -CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB; -ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a); +CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a), c INT) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (c) REFERENCES t1 (a); SET SESSION FOREIGN_KEY_CHECKS = OFF; ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL; ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY; @@ -237,6 +237,8 @@ SET SESSION FOREIGN_KEY_CHECKS = OFF; ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); SET SESSION FOREIGN_KEY_CHECKS = ON; ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); +Warnings: +Warning 1088 failed to load FOREIGN KEY constraints ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); DROP TABLE t1; CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY) @@ -362,14 +364,14 @@ update t1 set data=10 where pk+1>10; show status like '%opened_tab%'; Variable_name Value Opened_table_definitions 5 -Opened_tables 4 +Opened_tables 5 flush tables; flush status; update t2 set t1_pk=11 where t1_pk+1>10; show status like '%opened_tab%'; Variable_name Value -Opened_table_definitions 4 -Opened_tables 4 +Opened_table_definitions 5 +Opened_tables 5 flush tables; flush status; lock tables t1 write; @@ -396,7 +398,7 @@ foo() show status like '%opened_tab%'; Variable_name Value Opened_table_definitions 6 -Opened_tables 5 +Opened_tables 6 drop function foo; drop table t2, t1; # Start of 10.2 tests @@ -728,7 +730,9 @@ t2 CREATE TABLE `t2` ( `f1` int(11) NOT NULL, `f2` int(11) NOT NULL, `f3` int(11) NOT NULL, - PRIMARY KEY (`f3`) + PRIMARY KEY (`f3`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON DELETE CASCADE, + CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB; ERROR 42S01: Table 't2' already exists @@ -736,7 +740,7 @@ DROP TABLE t2, t1; # End of 10.2 tests CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; -ERROR 42S02: Table 'test.x' doesn't exist +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") # End of 10.4 tests # # MDEV-20729 Fix REFERENCES constraint in column definition @@ -809,3 +813,97 @@ create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") drop table t1; # End of 10.5 tests +# +# Bug #20762798 FK DDL: CRASH IN DICT_FOREIGN_REMOVE_FROM_CACHE +# +create table t1(a int, b int, key(a),key(b))engine=innodb; +create table t2(a int, b int, key(a),key(b))engine=innodb; +alter table t2 add constraint b foreign key (b) references t1(a); +alter table t1 add constraint b1 foreign key (b) references t2(a); +alter table t2 add constraint b1 foreign key (b) references t1(a); +alter table t2 drop foreign key b; +alter table t1 drop foreign key b1; +drop table t2; +drop table t1; +set global innodb_file_per_table = 1; +drop table if exists b; +drop database if exists bug_fk; +create database bug_fk; +use bug_fk; +CREATE TABLE b ( +b int unsigned NOT NULL, +d1 datetime NOT NULL, +PRIMARY KEY (b,d1) +) ENGINE=InnoDB; +CREATE TABLE c ( +b int unsigned NOT NULL, +d1 datetime NOT NULL, +d2 datetime NOT NULL, +PRIMARY KEY (b,d1), +CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; +insert into b values (1, '1980-01-01 00:00:00'); +insert into c values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +delete from b; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +update c set b= 2; +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +show warnings; +Level Code Message +Error 1452 Cannot add or update a child row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +set foreign_key_checks = 0; +DROP TABLE IF EXISTS b; +show create table c; +Table Create Table +c CREATE TABLE `c` ( + `b` int(10) unsigned NOT NULL, + `d1` datetime NOT NULL, + `d2` datetime NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +CREATE TABLE b ( +b bigint unsigned NOT NULL, +d1 date NOT NULL, +PRIMARY KEY (b,d1) +) ENGINE=InnoDB; +show warnings; +Level Code Message +DROP TABLE IF EXISTS d; +Warnings: +Note 1051 Unknown table 'bug_fk.d' +CREATE TABLE d ( +b bigint unsigned NOT NULL, +d1 date NOT NULL, +PRIMARY KEY (b,d1), +CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; +show warnings; +Level Code Message +set foreign_key_checks = 1; +show create table c; +Table Create Table +c CREATE TABLE `c` ( + `b` int(10) unsigned NOT NULL, + `d1` datetime NOT NULL, + `d2` datetime NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table d; +Table Create Table +d CREATE TABLE `d` ( + `b` bigint(20) unsigned NOT NULL, + `d1` date NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `bd_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +update c set b= 2; +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +insert into c values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +insert into b values (2, '1980-01-01 00:00:00'); +insert into c values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`bug_fk`.`c`, CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)) +insert into d values (2, '1980-01-01 00:00:00'); +drop database bug_fk; diff --git a/mysql-test/suite/innodb/r/information_schema_grants.result b/mysql-test/suite/innodb/r/information_schema_grants.result index 622faa2920a..6da95db244c 100644 --- a/mysql-test/suite/innodb/r/information_schema_grants.result +++ b/mysql-test/suite/innodb/r/information_schema_grants.result @@ -48,10 +48,6 @@ create sql security invoker view i_sys_datafiles as select * from information_sc create sql security definer view d_sys_datafiles as select * from information_schema.innodb_sys_datafiles; create sql security invoker view i_sys_fields as select * from information_schema.innodb_sys_fields; create sql security definer view d_sys_fields as select * from information_schema.innodb_sys_fields; -create sql security invoker view i_sys_foreign as select * from information_schema.innodb_sys_foreign; -create sql security definer view d_sys_foreign as select * from information_schema.innodb_sys_foreign; -create sql security invoker view i_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols; -create sql security definer view d_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols; create sql security invoker view i_sys_indexes as select * from information_schema.innodb_sys_indexes; create sql security definer view d_sys_indexes as select * from information_schema.innodb_sys_indexes; create sql security invoker view i_sys_semaphore_waits as select * from information_schema.innodb_sys_semaphore_waits; @@ -219,20 +215,6 @@ ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) select count(*) > -1 from d_sys_fields; count(*) > -1 1 -select count(*) > -1 from information_schema.innodb_sys_foreign; -ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation -select count(*) > -1 from i_sys_foreign; -ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation -select count(*) > -1 from d_sys_foreign; -count(*) > -1 -1 -select count(*) > -1 from information_schema.innodb_sys_foreign_cols; -ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation -select count(*) > -1 from i_sys_foreign_cols; -ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation -select count(*) > -1 from d_sys_foreign_cols; -count(*) > -1 -1 select count(*) > -1 from information_schema.innodb_sys_indexes; ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation select count(*) > -1 from i_sys_indexes; diff --git a/mysql-test/suite/innodb/r/innodb-alter-debug.result b/mysql-test/suite/innodb/r/innodb-alter-debug.result index 4644c124a45..a1ba9cb4876 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-debug.result +++ b/mysql-test/suite/innodb/r/innodb-alter-debug.result @@ -1,30 +1,3 @@ -SET NAMES utf8; -CREATE TABLE ① ( -c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) -ENGINE = InnoDB; -CREATE TABLE t1ć (c1 INT PRIMARY KEY, c2 INT, INDEX(c2), -CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES ①(c2)) -ENGINE=InnoDB; -INSERT INTO ① SET c1 = 1; -SET @saved_debug_dbug = @@SESSION.debug_dbug; -SET DEBUG_DBUG = '+d,ib_drop_foreign_error'; -ALTER TABLE t1ć DROP FOREIGN KEY t1c2, RENAME TO ②; -ERROR HY000: The table 't1ć' is full -SET DEBUG_DBUG = @saved_debug_dbug; -SET DEBUG_DBUG = '+d,ib_rename_column_error'; -ALTER TABLE ① CHANGE c2 š INT; -ERROR HY000: The table '①' is full -SET DEBUG_DBUG = @saved_debug_dbug; -SHOW CREATE TABLE t1ć; -Table Create Table -t1ć CREATE TABLE `t1ć` ( - `c1` int(11) NOT NULL, - `c2` int(11) DEFAULT NULL, - PRIMARY KEY (`c1`), - KEY `c2` (`c2`), - CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `①` (`c2`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t1ć, ①; # # Bug #21364096 THE BOGUS DUPLICATE KEY ERROR IN ONLINE DDL # WITH INCORRECT KEY NAME diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index 520d4430173..984f66b108d 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -13,14 +13,6 @@ CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3), CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2), CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2)) ENGINE=InnoDB; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i -WHERE FOR_NAME LIKE 'test/t%'; -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -33,11 +25,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -69,11 +56,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1; ALTER TABLE t1 CHANGE c1 c1 INT FIRST; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN @@ -88,11 +70,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C2 c3 INT; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i @@ -106,11 +83,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c3 C INT; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i @@ -124,17 +96,7 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 C -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 C 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 Cöŀumň_TWO 0 -test/t1c3 c3 c2 0 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -147,11 +109,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 Cöŀumň_TWO -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 Cöŀumň_TWO 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT; ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1' ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3; @@ -246,11 +203,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 DROP INDEX c2; ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint ALTER TABLE t1 DROP INDEX c4; @@ -304,11 +256,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB; ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3; ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint @@ -340,11 +287,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1c DROP FOREIGN KEY t1C3; SHOW CREATE TABLE t1c; Table Create Table @@ -368,10 +310,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2; SHOW CREATE TABLE t1c; Table Create Table @@ -393,9 +331,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i @@ -408,9 +343,6 @@ SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS CREATE TABLE t1o LIKE t1; ALTER TABLE t1 ADD FULLTEXT INDEX (ct), CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, @@ -426,9 +358,6 @@ NAME POS MTYPE PRTYPE LEN SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS SHOW CREATE TABLE tt; Table Create Table tt CREATE TABLE `tt` ( @@ -623,13 +552,11 @@ t1o CREATE TABLE `t1o` ( `cu` text DEFAULT NULL, PRIMARY KEY (`foo_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign; +DROP TABLE t1c, t1p, sys_tables, sys_indexes; CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1o'; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o'; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -642,9 +569,6 @@ SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 foo_id -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), ADD FULLTEXT INDEX(ct), CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL; @@ -670,10 +594,7 @@ NAME POS NAME PRIMARY 0 FTS_DOC_ID FTS_DOC_ID_INDEX 0 FTS_DOC_ID ct 0 ct -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; +DROP TABLE tt, t1o, sys_tables, sys_indexes; CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB; DROP INDEX t ON t; SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name @@ -766,11 +687,6 @@ INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON I.TABLE_ID=T.TABLE_ID WHERE T.NAME='test/t1' AND I.NAME='PRIMARY'; NAME c5 -SELECT C.REF_COL_NAME, C.FOR_COL_NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS C INNER JOIN -INFORMATION_SCHEMA.INNODB_SYS_FOREIGN F ON C.ID=F.ID -WHERE F.FOR_NAME='test/t2'; -REF_COL_NAME FOR_COL_NAME -c5 c6 DROP TABLE t2, t1; # virtual columns case too CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB; diff --git a/mysql-test/suite/innodb/r/innodb-fk-virtual.result b/mysql-test/suite/innodb/r/innodb-fk-virtual.result index cdc4189e623..c71e85b9dfe 100644 --- a/mysql-test/suite/innodb/r/innodb-fk-virtual.result +++ b/mysql-test/suite/innodb/r/innodb-fk-virtual.result @@ -74,7 +74,7 @@ select * from b; cola v_cola p_cola c_cola 10 2 2 12 insert into b(cola, v_cola, p_cola) values (10,1,1); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`) ON DELETE NO ACTION ON UPDATE NO ACTION) delete from a; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`)) select * from a; diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result index 4f1cea06bd0..cf1c7822a52 100644 --- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result +++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result @@ -1,25 +1,3 @@ -CREATE TABLE t1 ( -id int(11) NOT NULL PRIMARY KEY, -a int(11) NOT NULL, -b int(11) NOT NULL, -c int not null, -CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -CREATE TABLE t2 ( -id int(11) NOT NULL PRIMARY KEY, -a int(11) NOT NULL, -b int(11) NOT NULL, -c int not null, -CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), -CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -show warnings; -Level Code Message -Warning 121 Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test`.`test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). -Error 1005 Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -Warning 1022 Can't write; duplicate key in table 't2' -drop table t1; create table t1(a int) engine=innodb; create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=innodb; ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") @@ -41,19 +19,16 @@ drop table t1; create table t1(a int not null primary key, b int) engine=innodb; create table t2(a int, b int, constraint a foreign key a (a) references t1(a), constraint a foreign key a (a) references t1(b)) engine=innodb; -ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR HY000: Duplicate FOREIGN KEY constraint name 'a' show warnings; Level Code Message -Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t2` +Error 1826 Duplicate FOREIGN KEY constraint name 'a' create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb; alter table t2 add constraint b foreign key (b) references t2(b); -ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42000: Incorrect foreign key definition for 'b' show warnings; Level Code Message -Warning 150 Alter table `test`.`t2` with foreign key `b` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. -Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t2` +Error 1239 Incorrect foreign key definition for 'b' drop table t2, t1; create table t1 (f1 integer primary key) engine=innodb; alter table t1 add constraint c1 foreign key (f1) references t11(f1); @@ -90,26 +65,24 @@ Warning 1215 Cannot add foreign key constraint for `t1` drop table t1; create table t1(a int not null primary key, b int, key(b)) engine=innodb; alter table t1 add foreign key(a,b) references t1(a); -ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'foreign key without name' show warnings; Level Code Message -Error 1239 Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +Error 1239 Incorrect foreign key definition for 'foreign key without name' drop table t1; create table t1(a int not null primary key, b int, key(b)) engine=innodb; alter table t1 add foreign key(a) references t1(a,b); -ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'foreign key without name' show warnings; Level Code Message -Error 1239 Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +Error 1239 Incorrect foreign key definition for 'foreign key without name' drop table t1; create table t1 (f1 integer not null primary key) engine=innodb; alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null; -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42000: Incorrect foreign key definition for 'c1' show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key `c1` constraint failed. You have defined a SET NULL condition but column 'f1' is defined as NOT NULL. -Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t1` +Error 1239 Incorrect foreign key definition for 'c1' create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb; ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index 8408e54fe0a..0ce40383ce5 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -30,7 +30,7 @@ drop table t1; create table t1 (f1 int primary key) engine=innodb; # restart delete from t1 where f1 = 29; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fk_29`, CONSTRAINT `pc29` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fk_29`, CONSTRAINT `pc29` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE NO ACTION ON UPDATE NO ACTION) select * from fk_29; f1 29 @@ -95,7 +95,7 @@ CREATE TABLE `kg_test2`.`person2` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, PRIMARY KEY (`Id`), -CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) +CONSTRAINT `fk_person_group2` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; show create table `kg_test2`.`person2`; Table Create Table @@ -103,7 +103,7 @@ person2 CREATE TABLE `person2` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, PRIMARY KEY (`Id`), - CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) + CONSTRAINT `fk_person_group2` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 SHOW WARNINGS; Level Code Message diff --git a/mysql-test/suite/innodb/r/innodb-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result deleted file mode 100644 index efa227e4035..00000000000 --- a/mysql-test/suite/innodb/r/innodb-fkcheck.result +++ /dev/null @@ -1,89 +0,0 @@ -set global innodb_file_per_table = 1; -drop table if exists b; -drop database if exists bug_fk; -create database bug_fk; -use bug_fk; -CREATE TABLE b ( -b int unsigned NOT NULL, -d1 datetime NOT NULL, -PRIMARY KEY (b,d1) -) ENGINE=InnoDB; -CREATE TABLE c ( -b int unsigned NOT NULL, -d1 datetime NOT NULL, -d2 datetime NOT NULL, -PRIMARY KEY (b,d1), -CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) -) ENGINE=InnoDB; -show warnings; -Level Code Message -set foreign_key_checks = 0; -DROP TABLE IF EXISTS b; -show create table c; -Table Create Table -c CREATE TABLE `c` ( - `b` int(10) unsigned NOT NULL, - `d1` datetime NOT NULL, - `d2` datetime NOT NULL, - PRIMARY KEY (`b`,`d1`), - CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -CREATE TABLE b ( -b bigint unsigned NOT NULL, -d1 date NOT NULL, -PRIMARY KEY (b,d1) -) ENGINE=InnoDB; -ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") -show warnings; -Level Code Message -Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `b` -DROP TABLE IF EXISTS d; -Warnings: -Note 1051 Unknown table 'bug_fk.d' -CREATE TABLE d ( -b bigint unsigned NOT NULL, -d1 date NOT NULL, -PRIMARY KEY (b,d1), -CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) -) ENGINE=InnoDB; -show warnings; -Level Code Message -set foreign_key_checks = 1; -show create table c; -Table Create Table -c CREATE TABLE `c` ( - `b` int(10) unsigned NOT NULL, - `d1` datetime NOT NULL, - `d2` datetime NOT NULL, - PRIMARY KEY (`b`,`d1`), - CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -show create table d; -Table Create Table -d CREATE TABLE `d` ( - `b` bigint(20) unsigned NOT NULL, - `d1` date NOT NULL, - PRIMARY KEY (`b`,`d1`), - CONSTRAINT `bd_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -CREATE TABLE b ( -b bigint unsigned NOT NULL, -d1 date NOT NULL, -PRIMARY KEY (b,d1) -) ENGINE=InnoDB; -ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") -show warnings; -Level Code Message -Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `b` -set foreign_key_checks=0; -drop table c; -drop table d; -create table b(id int) engine=innodb; -show warnings; -Level Code Message -b.frm -b.ibd -drop table if exists b; -drop database if exists bug_fk; diff --git a/mysql-test/suite/innodb/r/innodb-index-online-fk.result b/mysql-test/suite/innodb/r/innodb-index-online-fk.result index ecb17e3c7cc..c0288c7ea7f 100644 --- a/mysql-test/suite/innodb/r/innodb-index-online-fk.result +++ b/mysql-test/suite/innodb/r/innodb-index-online-fk.result @@ -12,12 +12,6 @@ SET foreign_key_checks = 0; ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; @@ -52,21 +46,6 @@ REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a ALGORITHM = INPLACE; ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -test/fk_10 test/child test/parent 2 5 -test/fk_2 test/child test/parent 2 5 -test/fk_3 test/child test/parent 2 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 -test/fk_10 a1 a 0 -test/fk_10 a2 b 1 -test/fk_2 a1 a 0 -test/fk_2 a2 b 1 -test/fk_3 a1 a 0 -test/fk_3 a2 b 1 SET foreign_key_checks = 1; INSERT INTO child VALUES(5,4); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE) @@ -96,24 +75,6 @@ ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; SET DEBUG_DBUG = @saved_debug_dbug; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -test/fk_10 test/child test/parent 2 5 -test/fk_2 test/child test/parent 2 5 -test/fk_3 test/child test/parent 2 5 -test/fk_4 test/child test/parent 2 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 -test/fk_10 a1 a 0 -test/fk_10 a2 b 1 -test/fk_2 a1 a 0 -test/fk_2 a2 b 1 -test/fk_3 a1 a 0 -test/fk_3 a2 b 1 -test/fk_4 a1 a 0 -test/fk_4 a2 b 1 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; name name test/child a1 @@ -121,8 +82,6 @@ test/child a2 SELECT NAME FROM information_schema.INNODB_SYS_TABLES; NAME SYS_DATAFILES -SYS_FOREIGN -SYS_FOREIGN_COLS SYS_TABLESPACES SYS_VIRTUAL mysql/innodb_index_stats @@ -148,24 +107,6 @@ SET DEBUG_DBUG = @saved_debug_dbug; SHOW ERRORS; Level Code Message Error 1821 Failed to add the foreign key constaint. Missing index for constraint 'fk_40' in the foreign table '#child' -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -test/fk_10 test/child test/parent 2 5 -test/fk_2 test/child test/parent 2 5 -test/fk_3 test/child test/parent 2 5 -test/fk_4 test/child test/parent 2 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 -test/fk_10 a1 a 0 -test/fk_10 a2 b 1 -test/fk_2 a1 a 0 -test/fk_2 a2 b 1 -test/fk_3 a1 a 0 -test/fk_3 a2 b 1 -test/fk_4 a1 a 0 -test/fk_4 a2 b 1 SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx'; ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, @@ -181,33 +122,9 @@ REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42' SET DEBUG_DBUG = @saved_debug_dbug; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -test/fk_10 test/child test/parent 2 5 -test/fk_2 test/child test/parent 2 5 -test/fk_3 test/child test/parent 2 5 -test/fk_4 test/child test/parent 2 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 -test/fk_10 a1 a 0 -test/fk_10 a2 b 1 -test/fk_2 a1 a 0 -test/fk_2 a2 b 1 -test/fk_3 a1 a 0 -test/fk_3 a2 b 1 -test/fk_4 a1 a 0 -test/fk_4 a2 b 1 -SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; -ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2) -REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE, -ALGORITHM = INPLACE; -ERROR HY000: Failed to add the foreign key constraint 'test/fk_43' to system tables -SET DEBUG_DBUG = @saved_debug_dbug; SHOW ERRORS; Level Code Message -Error 1823 Failed to add the foreign key constraint 'test/fk_43' to system tables +Error 1825 Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42' DROP TABLE `#child`; DROP TABLE `#parent`; SET foreign_key_checks = 0; @@ -216,29 +133,6 @@ ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -test/fk_10 test/child test/parent 2 5 -test/fk_2 test/child test/parent 2 5 -test/fk_3 test/child test/parent 2 5 -test/fk_4 test/child test/parent 2 5 -test/fk_5 test/child test/parent 1 6 -test/fk_6 test/child test/parent 2 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 -test/fk_10 a1 a 0 -test/fk_10 a2 b 1 -test/fk_2 a1 a 0 -test/fk_2 a2 b 1 -test/fk_3 a1 a 0 -test/fk_3 a2 b 1 -test/fk_4 a1 a 0 -test/fk_4 a2 b 1 -test/fk_5 a2 b 0 -test/fk_6 a1 a 0 -test/fk_6 a2 b 1 DROP TABLE child; DROP TABLE parent; CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; @@ -261,12 +155,6 @@ child CREATE TABLE `child` ( KEY `fk_4` (`a2`), CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_4 test/child test/parent 1 5 -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_4 a2 b 0 SET foreign_key_checks = 1; DROP TABLE child; DROP TABLE parent; @@ -295,17 +183,6 @@ INSERT INTO parent VALUES(10,20),(20,30); CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB; CREATE INDEX tb ON child(a2); SET foreign_key_checks = 0; -SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; -ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, -ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) -ON DELETE SET NULL ON UPDATE CASCADE, -ALGORITHM = INPLACE; -ERROR HY000: Failed to add the foreign key constraint 'test/fk_1' to system tables -SET DEBUG_DBUG = @saved_debug_dbug; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; name name test/child a1 @@ -313,8 +190,6 @@ test/child a2 SELECT NAME FROM information_schema.INNODB_SYS_TABLES; NAME SYS_DATAFILES -SYS_FOREIGN -SYS_FOREIGN_COLS SYS_TABLESPACES SYS_VIRTUAL mysql/innodb_index_stats @@ -328,12 +203,6 @@ ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; Warnings: Warning 1280 Name 'idx' ignored for PRIMARY key. -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; name name test/child a2 @@ -341,8 +210,6 @@ test/child a3 SELECT NAME FROM information_schema.INNODB_SYS_TABLES; NAME SYS_DATAFILES -SYS_FOREIGN -SYS_FOREIGN_COLS SYS_TABLESPACES SYS_VIRTUAL mysql/innodb_index_stats @@ -367,12 +234,6 @@ ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; Warnings: Warning 1280 Name 'idx' ignored for PRIMARY key. -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a2 b 0 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; name name test/child a1 @@ -380,8 +241,6 @@ test/child a2 SELECT NAME FROM information_schema.INNODB_SYS_TABLES; NAME SYS_DATAFILES -SYS_FOREIGN -SYS_FOREIGN_COLS SYS_TABLESPACES SYS_VIRTUAL mysql/innodb_index_stats @@ -404,12 +263,6 @@ ALTER TABLE child CHANGE a1 a3 INT, ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_1 test/child test/parent 1 6 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_1 a3 b 0 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; name name test/child a2 @@ -417,8 +270,6 @@ test/child a3 SELECT NAME FROM information_schema.INNODB_SYS_TABLES; NAME SYS_DATAFILES -SYS_FOREIGN -SYS_FOREIGN_COLS SYS_TABLESPACES SYS_VIRTUAL mysql/innodb_index_stats @@ -467,14 +318,6 @@ child CREATE TABLE `child` ( CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_a test/child test/parent 1 6 -test/fk_b test/child test/parent 1 0 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_a a2_new b 0 -test/fk_b a1_new a 0 ALTER TABLE child ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b), ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a), @@ -492,14 +335,6 @@ child CREATE TABLE `child` ( CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_a test/child test/parent 1 6 -test/fk_b test/child test/parent 1 0 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_a a2_new b 0 -test/fk_b a1_new a 0 ALTER TABLE child ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b), ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a), @@ -520,20 +355,6 @@ child CREATE TABLE `child` ( CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`), CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_a test/child test/parent 1 6 -test/fk_b test/child test/parent 1 0 -test/fk_new_1 test/child test/parent 1 0 -test/fk_new_2 test/child test/parent 1 0 -test/fk_new_3 test/child test/parent 1 0 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_a a2_new b 0 -test/fk_b a1_new a 0 -test/fk_new_1 a1_new b 0 -test/fk_new_2 a2_new a 0 -test/fk_new_3 a3 a 0 DROP TABLE child; CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB; CREATE INDEX tb ON child(a2); @@ -551,10 +372,6 @@ child CREATE TABLE `child` ( `a3` int(11) DEFAULT NULL, KEY `tb` (`a2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE child ADD PRIMARY KEY idx (a1), ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b), ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a), @@ -575,16 +392,6 @@ child CREATE TABLE `child` ( CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`), CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * from information_schema.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/fk_new_1 test/child test/parent 1 0 -test/fk_new_2 test/child test/parent 1 0 -test/fk_new_3 test/child test/parent 1 0 -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/fk_new_1 a1 b 0 -test/fk_new_2 a2 a 0 -test/fk_new_3 a3 a 0 SET foreign_key_checks = 1; DROP TABLE child; DROP TABLE parent; @@ -606,15 +413,5 @@ CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB; CREATE INDEX idx ON t3(a); ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a); ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a); -ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL; -ERROR HY000: Failed to add the foreign key constraint 'test/e' to system tables -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/e test/t2 test/t3 1 0 -test/fw test/t2 test/t3 1 0 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/e d a 0 -test/fw c a 0 DROP TABLE t2; DROP TABLE t3; diff --git a/mysql-test/suite/innodb/r/innodb-index-online.result b/mysql-test/suite/innodb/r/innodb-index-online.result index 8eebece46b5..659ad4984d4 100644 --- a/mysql-test/suite/innodb/r/innodb-index-online.result +++ b/mysql-test/suite/innodb/r/innodb-index-online.result @@ -72,7 +72,7 @@ ERROR 23000: Duplicate entry '4' for key 'c2' connection default; DELETE FROM t1 WHERE c1 = 7; connection con1; -ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t1(c2), ALGORITHM = INPLACE; +ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES t1(c2), ALGORITHM = INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY ALTER TABLE t1 ADD UNIQUE INDEX(c2), LOCK = EXCLUSIVE, ALGORITHM = INPLACE; DROP INDEX c2 ON t1; diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index 2a23990dbac..484323a980e 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -508,10 +508,10 @@ t4 CREATE TABLE `t4` ( CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t3 add constraint dc foreign key (a) references t1(a); -ERROR HY000: Can't create table `test`.`t3` (errno: 121 "Duplicate key on write or update") +ERROR HY000: Duplicate FOREIGN KEY constraint name 'dc' SET FOREIGN_KEY_CHECKS=0; alter table t3 add constraint dc foreign key (a) references t1(a); -ERROR HY000: Failed to add the foreign key constraint 'test/dc' to system tables +ERROR HY000: Duplicate FOREIGN KEY constraint name 'dc' SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; show create table t3; Table Create Table diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result index 11fc1ae8cf9..0da3edb0245 100644 --- a/mysql-test/suite/innodb/r/innodb-system-table-view.result +++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result @@ -7,45 +7,31 @@ WHERE name = 'mysql/innodb_index_stats'; SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id; TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE -11 SYS_FOREIGN 0 7 0 Redundant 0 System -12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System -13 SYS_TABLESPACES 0 6 0 Redundant 0 System -14 SYS_DATAFILES 0 5 0 Redundant 0 System -15 SYS_VIRTUAL 0 6 0 Redundant 0 System -18 mysql/transaction_registry 33 8 3 Dynamic 0 Single +11 SYS_TABLESPACES 0 6 0 Redundant 0 System +12 SYS_DATAFILES 0 5 0 Redundant 0 System +13 SYS_VIRTUAL 0 6 0 Redundant 0 System +16 mysql/transaction_registry 33 8 3 Dynamic 0 Single SELECT table_id,pos,mtype,prtype,len,name FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id, pos; table_id pos mtype prtype len name -11 0 1 524292 0 ID -11 1 1 524292 0 FOR_NAME -11 2 1 524292 0 REF_NAME -11 3 6 0 4 N_COLS -12 0 1 524292 0 ID -12 1 6 0 4 POS -12 2 1 524292 0 FOR_COL_NAME -12 3 1 524292 0 REF_COL_NAME -13 0 6 0 4 SPACE -13 1 1 524292 0 NAME -13 2 6 0 4 FLAGS -14 0 6 0 4 SPACE -14 1 1 524292 0 PATH -15 0 6 0 8 TABLE_ID -15 1 6 0 4 POS -15 2 6 0 4 BASE_POS -18 0 6 1800 8 transaction_id -18 1 6 1800 8 commit_id -18 2 3 526087 7 begin_timestamp -18 3 3 526087 7 commit_timestamp -18 4 6 1022 1 isolation_level +11 0 6 0 4 SPACE +11 1 1 524292 0 NAME +11 2 6 0 4 FLAGS +12 0 6 0 4 SPACE +12 1 1 524292 0 PATH +13 0 6 0 8 TABLE_ID +13 1 6 0 4 POS +13 2 6 0 4 BASE_POS +16 0 6 1800 8 transaction_id +16 1 6 1800 8 commit_id +16 2 3 526087 7 begin_timestamp +16 3 3 526087 7 commit_timestamp +16 4 6 1022 1 isolation_level SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id; INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE MERGE_THRESHOLD -# ID_IND # 3 1 # # 50 -# FOR_IND # 0 1 # # 50 -# REF_IND # 0 1 # # 50 -# ID_IND # 3 2 # # 50 # SYS_TABLESPACES_SPACE # 3 1 # # 50 # SYS_DATAFILES_SPACE # 3 1 # # 50 # BASE_IDX # 3 3 # # 50 @@ -57,25 +43,16 @@ SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name') ORDER BY index_id, pos; index_id pos name -11 0 ID -12 0 FOR_NAME -13 0 REF_NAME -14 0 ID -14 1 POS -15 0 SPACE -16 0 SPACE -17 0 TABLE_ID -17 1 POS -17 2 BASE_POS -20 0 transaction_id -21 0 commit_id -22 0 begin_timestamp -23 0 commit_timestamp -23 1 transaction_id -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS +11 0 SPACE +12 0 SPACE +13 0 TABLE_ID +13 1 POS +13 2 BASE_POS +16 0 transaction_id +17 0 commit_id +18 0 begin_timestamp +19 0 commit_timestamp +19 1 transaction_id CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb; CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb; CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2; @@ -95,7 +72,7 @@ test/t_dynamic DEFAULT DEFAULT MYSQLD_DATADIR/test/t_dynamic.ibd DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic; SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; count(*) -8 +6 CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, @@ -103,12 +80,6 @@ INDEX par_ind (parent_id), CONSTRAINT constraint_test FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/constraint_test test/child test/parent 1 1 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/constraint_test parent_id id 0 INSERT INTO parent VALUES(1); InnoDB 0 transactions not purged SELECT name, num_rows, ref_count @@ -120,8 +91,6 @@ SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name NOT LIKE 'sys/%'; NAME FLAG N_COLS SYS_DATAFILES 0 5 -SYS_FOREIGN 0 7 -SYS_FOREIGN_COLS 0 7 SYS_TABLESPACES 0 6 SYS_VIRTUAL 0 6 mysql/innodb_index_stats 33 11 @@ -161,13 +130,6 @@ INDEX par_ind (parent_id), CONSTRAINT constraint_test FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid) ON DELETE CASCADE) ENGINE=INNODB; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -ID FOR_NAME REF_NAME N_COLS TYPE -test/constraint_test test/child test/parent 2 1 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -ID FOR_COL_NAME REF_COL_NAME POS -test/constraint_test id id 0 -test/constraint_test parent_id newid 1 INSERT INTO parent VALUES(1, 9); SELECT * FROM parent WHERE id IN (SELECT id FROM parent); id newid diff --git a/mysql-test/suite/innodb/r/innodb-truncate.result b/mysql-test/suite/innodb/r/innodb-truncate.result index 0d358327fe3..a41a3ea04e5 100644 --- a/mysql-test/suite/innodb/r/innodb-truncate.result +++ b/mysql-test/suite/innodb/r/innodb-truncate.result @@ -83,8 +83,6 @@ SET FOREIGN_KEY_CHECKS= OFF; CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS= ON; CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; -CREATE TABLE t3 (a INT) ENGINE=InnoDB; -ERROR HY000: Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed") ALTER TABLE t1 RENAME TO t3; ERROR 42S02: Table 'test.t3' doesn't exist ALTER TABLE t1 FORCE; diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index 08ee9688603..cdf8a8f6222 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -20,14 +20,6 @@ CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3), CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2), CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2)) ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i -WHERE FOR_NAME LIKE 'test/t%'; -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -40,11 +32,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -66,8 +53,6 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -93,17 +78,10 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1; ALTER TABLE t1 CHANGE c1 c1 INT FIRST; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -129,16 +107,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c2 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c2 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C2 c3 INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -164,16 +135,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c3 C INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -199,16 +163,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 C -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 C 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -222,11 +179,6 @@ t1p.ibd ### files in MYSQL_TMP_DIR/alt_dir/test t1.ibd t1c.ibd -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 Cöŀumň_TWO 0 -test/t1c3 c3 c2 0 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -239,18 +191,11 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 Cöŀumň_TWO -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 Cöŀumň_TWO 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT; ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1' ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -297,8 +242,6 @@ ALTER TABLE t3 CHANGE c3 `1234567890123456789012345678901234567890123456789012345678901234` INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -334,8 +277,6 @@ ALTER TABLE t3 CHANGE `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -354,8 +295,6 @@ ALTER TABLE t3 CHANGE c3 INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -376,8 +315,6 @@ ERROR HY000: Invalid utf8mb4 character string: '\xF0\x9F\x98\xB2' ALTER TABLE t3 RENAME TO t2; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -413,8 +350,6 @@ NAME NAME test/t1 test/t1 ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -440,11 +375,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1 DROP INDEX c2; ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint ALTER TABLE t1 DROP INDEX c4; @@ -488,8 +418,6 @@ t1c CREATE TABLE `t1c` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -513,11 +441,6 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3; @@ -528,8 +451,6 @@ SET foreign_key_checks=0; ALTER TABLE t1c DROP INDEX C3; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -568,16 +489,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 -test/t1c3 c3 c2 0 ALTER TABLE t1c DROP FOREIGN KEY t1C3; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -614,15 +528,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS -test/t1c2 c2 c3 0 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -657,14 +565,9 @@ INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 c2 0 c3 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT; ### files in MYSQL_DATA_DIR/test db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -690,9 +593,6 @@ SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 c1 -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS CREATE TABLE t1o LIKE t1; ALTER TABLE t1 ADD FULLTEXT INDEX (ct), CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, @@ -714,8 +614,6 @@ FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -750,9 +648,6 @@ NAME POS MTYPE PRTYPE LEN SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS SHOW CREATE TABLE tt; Table Create Table tt CREATE TABLE `tt` ( @@ -809,8 +704,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -871,8 +764,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -927,8 +818,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -984,8 +873,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1050,8 +937,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1119,8 +1004,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1185,8 +1068,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1243,8 +1124,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1299,8 +1178,6 @@ FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1355,8 +1232,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1417,8 +1292,6 @@ FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1454,13 +1327,11 @@ t1o CREATE TABLE `t1o` ( `ct` text DEFAULT NULL, PRIMARY KEY (`foo_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign; +DROP TABLE t1c, t1p, sys_tables, sys_indexes; CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1o'; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o'; SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -1472,9 +1343,6 @@ SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME PRIMARY 0 foo_id -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id); ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.isl @@ -1489,8 +1357,6 @@ FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1538,8 +1404,6 @@ FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd db.opt -sys_foreign.frm -sys_foreign.ibd sys_indexes.frm sys_indexes.ibd sys_tables.frm @@ -1579,13 +1443,10 @@ NAME POS NAME PRIMARY 0 FTS_DOC_ID FTS_DOC_ID_INDEX 0 FTS_DOC_ID ct 0 ct -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; -ID FOR_COL_NAME REF_COL_NAME POS # # Cleanup # -DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; +DROP TABLE tt, t1o, sys_tables, sys_indexes; ### files in MYSQL_DATA_DIR/test db.opt ### files in MYSQL_TMP_DIR/alt_dir/test diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index e77e82d57de..898ec2218a6 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1555,7 +1555,7 @@ t2 CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb; -ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 't1_id_fk' create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb; show create table t2; Table Create Table @@ -2531,9 +2531,11 @@ disconnect b; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; create table t1(a char(10) primary key, b varchar(20)) engine = innodb; -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") set foreign_key_checks=1; -drop table t2; +insert into t1 values (1, 1); +insert into t2 values (1, 1); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)) +drop tables t2, t1; set foreign_key_checks=0; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; @@ -2541,25 +2543,11 @@ ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint set foreign_key_checks=1; drop table t1; set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; -create table t1(a varchar(10) primary key) engine = innodb; -alter table t1 modify column a int; -set foreign_key_checks=1; -drop table t2,t1; -set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; alter table t1 convert to character set utf8; set foreign_key_checks=1; drop table t2,t1; -call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition."); -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; -create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; -rename table t3 to t1; -ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150 "Foreign key constraint is incorrectly formed") -set foreign_key_checks=1; -drop table t2,t3; create table t1(a int primary key) row_format=redundant engine=innodb; create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb; create table t3(a int primary key) row_format=compact engine=innodb; diff --git a/mysql-test/suite/innodb/r/innodb_bug12902967.result b/mysql-test/suite/innodb/r/innodb_bug12902967.result deleted file mode 100644 index ddb2e12f26a..00000000000 --- a/mysql-test/suite/innodb/r/innodb_bug12902967.result +++ /dev/null @@ -1,6 +0,0 @@ -call mtr.add_suppression("In ALTER TABLE .* has or is referenced in foreign key constraints which are not compatible with the new table definition."); -# restart -create table t1 (f1 integer primary key) engine innodb; -alter table t1 add constraint c1 foreign key (f1) references t1(f1); -ERROR HY000: Error on rename of '#sql-alter' to './test/t1' (errno: 150 "Foreign key constraint is incorrectly formed") -drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug21704.result b/mysql-test/suite/innodb/r/innodb_bug21704.result index c0bc3af2f20..cc5c7a35a16 100644 --- a/mysql-test/suite/innodb/r/innodb_bug21704.result +++ b/mysql-test/suite/innodb/r/innodb_bug21704.result @@ -76,14 +76,6 @@ t3 CREATE TABLE `t3` ( KEY `b` (`g`), CONSTRAINT `fk2` FOREIGN KEY (`g`) REFERENCES `t3` (`f`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT -SELECT f.*, c.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS c -INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FOREIGN f -ON c.ID=f.ID -WHERE FOR_NAME LIKE 'test/t%'; -ID FOR_NAME REF_NAME N_COLS TYPE ID FOR_COL_NAME REF_COL_NAME POS -test/fk1 test/t2 test/t1 1 0 test/fk1 z e 0 -test/fk2 test/t3 test/t3 1 0 test/fk2 g f 0 DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug57255.result b/mysql-test/suite/innodb/r/innodb_bug57255.result index d61a0d42ba3..818fe32fd06 100644 --- a/mysql-test/suite/innodb/r/innodb_bug57255.result +++ b/mysql-test/suite/innodb/r/innodb_bug57255.result @@ -8,3 +8,4 @@ DELETE FROM A where id = 1; DROP TABLE C; DROP TABLE B; DROP TABLE A; +flush tables; diff --git a/mysql-test/suite/innodb/r/innodb_bug60049.result b/mysql-test/suite/innodb/r/innodb_bug60049.result index 47b02dedd01..223080db0a1 100644 --- a/mysql-test/suite/innodb/r/innodb_bug60049.result +++ b/mysql-test/suite/innodb/r/innodb_bug60049.result @@ -5,5 +5,5 @@ SELECT @@innodb_fast_shutdown; @@innodb_fast_shutdown 0 Last record of ID_IND root page (9): -18080000180500c0000000000000000c5359535f464f524549474e5f434f4c53 +4143455315080000180500c1000000000000000c5359535f4441544146494c45 # restart diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index aa9ab6c176f..7fce1ab0ae7 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2109,10 +2109,10 @@ DROP TABLE t1, t2; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'c2' CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'c2' CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION, CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; @@ -2120,10 +2120,10 @@ ALTER TABLE t2 DROP FOREIGN KEY c2; DROP TABLE t2; CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'foreign key without name' CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match +ERROR 42000: Incorrect foreign key definition for 'f1' CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION, CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION, @@ -3200,7 +3200,7 @@ Table Create Table t2 CREATE TABLE `t2` ( `fk` int(11) DEFAULT NULL, KEY `x` (`fk`), - CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`) + CONSTRAINT `x` FOREIGN KEY (`FK`) REFERENCES `t1` (`PK`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2, t1; # diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index 9ab72408274..4d1d86ea994 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -370,14 +370,6 @@ select * from information_schema.innodb_sys_fields; INDEX_ID NAME POS Warnings: Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_sys_fields but the InnoDB storage engine is not installed -select * from information_schema.innodb_sys_foreign; -ID FOR_NAME REF_NAME N_COLS TYPE -Warnings: -Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_sys_foreign but the InnoDB storage engine is not installed -select * from information_schema.innodb_sys_foreign_cols; -ID FOR_COL_NAME REF_COL_NAME POS -Warnings: -Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_sys_foreign_cols but the InnoDB storage engine is not installed select * from information_schema.innodb_sys_tablespaces; SPACE NAME FLAG ROW_FORMAT PAGE_SIZE ZIP_PAGE_SIZE FS_BLOCK_SIZE FILE_SIZE ALLOCATED_SIZE Warnings: diff --git a/mysql-test/suite/innodb/r/instant_alter_index_rename.result b/mysql-test/suite/innodb/r/instant_alter_index_rename.result index 988aaf80d60..d4fa3a66111 100644 --- a/mysql-test/suite/innodb/r/instant_alter_index_rename.result +++ b/mysql-test/suite/innodb/r/instant_alter_index_rename.result @@ -181,7 +181,7 @@ drop table rename_column_and_index; create table t1 (f1 int, f2 int, f3 int); create table t2 (f2 int primary key); alter table t1 add foreign key f (f2) references t2(f2); -alter table t1 add foreign key (f2) references t1(f2), add key (f3), add key (f1); +alter table t1 add foreign key (f1) references t1(f2), add key (f3), add key (f1); drop tables t1, t2; # # MDEV-21669 InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB diff --git a/mysql-test/suite/innodb/r/table_flags,32k,debug.rdiff b/mysql-test/suite/innodb/r/table_flags,32k,debug.rdiff index 7e851cf5634..e06c8197a1b 100644 --- a/mysql-test/suite/innodb/r/table_flags,32k,debug.rdiff +++ b/mysql-test/suite/innodb/r/table_flags,32k,debug.rdiff @@ -1,6 +1,6 @@ ---- suite/innodb/r/table_flags.result -+++ suite/innodb/r/table_flags,32k,debug.reject -@@ -5,96 +5,98 @@ +--- ./table_flags.result 2020-07-28 12:33:31.937242985 +0300 ++++ ./table_flags,32k,debug.reject 2020-07-28 14:51:37.080663807 +0300 +@@ -6,6 +6,8 @@ SET innodb_strict_mode=OFF; CREATE TABLE tz(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; @@ -9,121 +9,9 @@ SET innodb_strict_mode=ON; CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9; - SYS_TABLES clustered index root page (8): - N_RECS=10; LEVEL=0; INDEX_ID=0x0000000000000001 --header=0x01000003016e (NAME=0x696e66696d756d00) --header=0x00002815008d (NAME='SYS_DATAFILES', -+header=0x0100000301bf (NAME=0x696e66696d756d00) -+header=0x0000301500de (NAME='SYS_DATAFILES', - DB_TRX_ID=0x000000000000, +@@ -87,7 +89,7 @@ DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000e, -+ ID=0x000000000000000f, - N_COLS=0x00000002, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x0000101500d5 (NAME='SYS_FOREIGN', -+header=0x000018150126 (NAME='SYS_FOREIGN', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000b, -+ ID=0x000000000000000c, - N_COLS=0x00000004, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000018150122 (NAME='SYS_FOREIGN_COLS', -+header=0x000020150173 (NAME='SYS_FOREIGN_COLS', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000c, -+ ID=0x000000000000000d, - N_COLS=0x00000004, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x0400201501b8 (NAME='SYS_TABLESPACES', -+header=0x040028150209 (NAME='SYS_TABLESPACES', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000d, -+ ID=0x000000000000000e, - N_COLS=0x00000003, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000030150244 (NAME='SYS_VIRTUAL', -+header=0x000038150251 (NAME='SYS_VIRTUAL', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000f, -+ ID=0x0000000000000010, - N_COLS=0x00000003, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000040150288 (NAME='test/tc', -+header=0x000040150295 (NAME='test/tc', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000011, -+ ID=0x0000000000000012, - N_COLS=0x80000001, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000002) --header=0x000048150310 (NAME='test/td', -+header=0x00004815031d (NAME='test/td', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000012, -+ ID=0x0000000000000013, - N_COLS=0x80000001, - TYPE=0x00000021, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000003) --header=0x000058150200 (NAME='test/tp', -+header=0x00005815008d (NAME='test/tp', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000014, -+ ID=0x0000000000000015, - N_COLS=0x80000001, - TYPE=0x000009a1, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000005) --header=0x0000381502cc (NAME='test/tr', -+header=0x0000101502d9 (NAME='test/tr', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000010, -+ ID=0x0000000000000011, - N_COLS=0x00000001, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, -@@ -104,9 +106,9 @@ - header=0x000050150074 (NAME='test/tz', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000013, -+ ID=0x0000000000000014, + ID=0x0000000000000011, N_COLS=0x80000001, - TYPE=0x00000023, + TYPE=0x00000021, diff --git a/mysql-test/suite/innodb/r/table_flags,64k,debug.rdiff b/mysql-test/suite/innodb/r/table_flags,64k,debug.rdiff index da52f17fa68..a1b13f38b2b 100644 --- a/mysql-test/suite/innodb/r/table_flags,64k,debug.rdiff +++ b/mysql-test/suite/innodb/r/table_flags,64k,debug.rdiff @@ -1,6 +1,6 @@ ---- suite/innodb/r/table_flags.result -+++ suite/innodb/r/table_flags,64k,debug.reject -@@ -5,96 +5,98 @@ +--- ./table_flags.result 2020-07-28 12:33:31.937242985 +0300 ++++ ./table_flags,64k,debug.reject 2020-07-28 14:51:55.346069234 +0300 +@@ -6,6 +6,8 @@ SET innodb_strict_mode=OFF; CREATE TABLE tz(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; @@ -9,121 +9,9 @@ SET innodb_strict_mode=ON; CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9; - SYS_TABLES clustered index root page (8): - N_RECS=10; LEVEL=0; INDEX_ID=0x0000000000000001 --header=0x01000003016e (NAME=0x696e66696d756d00) --header=0x00002815008d (NAME='SYS_DATAFILES', -+header=0x0100000301bf (NAME=0x696e66696d756d00) -+header=0x0000301500de (NAME='SYS_DATAFILES', - DB_TRX_ID=0x000000000000, +@@ -87,7 +89,7 @@ DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000e, -+ ID=0x000000000000000f, - N_COLS=0x00000002, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x0000101500d5 (NAME='SYS_FOREIGN', -+header=0x000018150126 (NAME='SYS_FOREIGN', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000b, -+ ID=0x000000000000000c, - N_COLS=0x00000004, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000018150122 (NAME='SYS_FOREIGN_COLS', -+header=0x000020150173 (NAME='SYS_FOREIGN_COLS', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000c, -+ ID=0x000000000000000d, - N_COLS=0x00000004, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x0400201501b8 (NAME='SYS_TABLESPACES', -+header=0x040028150209 (NAME='SYS_TABLESPACES', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000d, -+ ID=0x000000000000000e, - N_COLS=0x00000003, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000030150244 (NAME='SYS_VIRTUAL', -+header=0x000038150251 (NAME='SYS_VIRTUAL', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x000000000000000f, -+ ID=0x0000000000000010, - N_COLS=0x00000003, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) --header=0x000040150288 (NAME='test/tc', -+header=0x000040150295 (NAME='test/tc', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000011, -+ ID=0x0000000000000012, - N_COLS=0x80000001, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000002) --header=0x000048150310 (NAME='test/td', -+header=0x00004815031d (NAME='test/td', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000012, -+ ID=0x0000000000000013, - N_COLS=0x80000001, - TYPE=0x00000021, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000003) --header=0x000058150200 (NAME='test/tp', -+header=0x00005815008d (NAME='test/tp', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000014, -+ ID=0x0000000000000015, - N_COLS=0x80000001, - TYPE=0x000009a1, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000050, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000005) --header=0x0000381502cc (NAME='test/tr', -+header=0x0000101502d9 (NAME='test/tr', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000010, -+ ID=0x0000000000000011, - N_COLS=0x00000001, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, -@@ -104,9 +106,9 @@ - header=0x000050150074 (NAME='test/tz', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, -- ID=0x0000000000000013, -+ ID=0x0000000000000014, + ID=0x0000000000000011, N_COLS=0x80000001, - TYPE=0x00000023, + TYPE=0x00000021, diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result index 82935944027..af55b68d5f2 100644 --- a/mysql-test/suite/innodb/r/table_flags.result +++ b/mysql-test/suite/innodb/r/table_flags.result @@ -10,39 +10,29 @@ SET innodb_strict_mode=ON; CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9; SYS_TABLES clustered index root page (8): -N_RECS=10; LEVEL=0; INDEX_ID=0x0000000000000001 -header=0x01000003016e (NAME=0x696e66696d756d00) -header=0x00002815008d (NAME='SYS_DATAFILES', +N_RECS=8; LEVEL=0; INDEX_ID=0x0000000000000001 +header=0x0100000300d9 (NAME=0x696e66696d756d00) +header=0x00001815008d (NAME='SYS_DATAFILES', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, - ID=0x000000000000000e, + ID=0x000000000000000c, N_COLS=0x00000002, TYPE=0x00000001, MIX_ID=0x0000000000000000, MIX_LEN=0x00000040, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000000) -header=0x0000101500d5 (NAME='SYS_FOREIGN', +header=0x000010150123 (NAME='SYS_TABLESPACES', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, ID=0x000000000000000b, - N_COLS=0x00000004, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) -header=0x000018150122 (NAME='SYS_FOREIGN_COLS', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, - ID=0x000000000000000c, - N_COLS=0x00000004, + N_COLS=0x00000003, TYPE=0x00000001, MIX_ID=0x0000000000000000, MIX_LEN=0x00000040, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000000) -header=0x0400201501b8 (NAME='SYS_TABLESPACES', +header=0x0000201501af (NAME='SYS_VIRTUAL', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, ID=0x000000000000000d, @@ -52,67 +42,57 @@ header=0x0400201501b8 (NAME='SYS_TABLESPACES', MIX_LEN=0x00000040, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000000) -header=0x000030150244 (NAME='SYS_VIRTUAL', +header=0x0400301501f3 (NAME='test/tc', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, ID=0x000000000000000f, - N_COLS=0x00000003, - TYPE=0x00000001, - MIX_ID=0x0000000000000000, - MIX_LEN=0x00000040, - CLUSTER_NAME=NULL(0 bytes), - SPACE=0x00000000) -header=0x000040150288 (NAME='test/tc', - DB_TRX_ID=0x000000000000, - DB_ROLL_PTR=0x80000000000000, - ID=0x0000000000000011, N_COLS=0x80000001, TYPE=0x00000001, MIX_ID=0x0000000000000000, MIX_LEN=0x00000050, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000002) -header=0x000048150310 (NAME='test/td', +header=0x00003815027b (NAME='test/td', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, - ID=0x0000000000000012, + ID=0x0000000000000010, N_COLS=0x80000001, TYPE=0x00000021, MIX_ID=0x0000000000000000, MIX_LEN=0x00000050, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000003) -header=0x000058150200 (NAME='test/tp', +header=0x00004815016b (NAME='test/tp', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, - ID=0x0000000000000014, + ID=0x0000000000000012, N_COLS=0x80000001, TYPE=0x000009a1, MIX_ID=0x0000000000000000, MIX_LEN=0x00000050, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000005) -header=0x0000381502cc (NAME='test/tr', +header=0x000028150237 (NAME='test/tr', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, - ID=0x0000000000000010, + ID=0x000000000000000e, N_COLS=0x00000001, TYPE=0x00000001, MIX_ID=0x0000000000000000, MIX_LEN=0x00000050, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000001) -header=0x000050150074 (NAME='test/tz', +header=0x000040150074 (NAME='test/tz', DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, - ID=0x0000000000000013, + ID=0x0000000000000011, N_COLS=0x80000001, TYPE=0x00000023, MIX_ID=0x0000000000000000, MIX_LEN=0x00000050, CLUSTER_NAME=NULL(0 bytes), SPACE=0x00000004) -header=0x070008030000 (NAME=0x73757072656d756d00) +header=0x050008030000 (NAME=0x73757072656d756d00) # restart: with restart_parameters SHOW CREATE TABLE tr; ERROR 42S02: Table 'test.tr' doesn't exist in engine diff --git a/mysql-test/suite/innodb/r/truncate_foreign.result b/mysql-test/suite/innodb/r/truncate_foreign.result index d391436dec1..19edeacd892 100644 --- a/mysql-test/suite/innodb/r/truncate_foreign.result +++ b/mysql-test/suite/innodb/r/truncate_foreign.result @@ -45,14 +45,17 @@ connection default; SET DEBUG_SYNC='now WAIT_FOR fk'; SET foreign_key_checks=0; TRUNCATE TABLE parent; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction SET DEBUG_SYNC='now SIGNAL go'; connection dml; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_child` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON UPDATE CASCADE) SELECT * FROM parent; a +3 +5 SELECT * FROM child; a 3 +5 disconnect dml; connection default; SET DEBUG_SYNC = RESET; diff --git a/mysql-test/suite/innodb/t/add_constraint.test b/mysql-test/suite/innodb/t/add_constraint.test deleted file mode 100644 index f43f2977020..00000000000 --- a/mysql-test/suite/innodb/t/add_constraint.test +++ /dev/null @@ -1,20 +0,0 @@ ---source include/have_innodb.inc - ---echo # ---echo # Bug #20762798 FK DDL: CRASH IN DICT_FOREIGN_REMOVE_FROM_CACHE ---echo # - -create table t1(a int, b int, key(a),key(b))engine=innodb; -create table t2(a int, b int, key(a),key(b))engine=innodb; - -alter table t2 add constraint b foreign key (b) references t1(a); -alter table t1 add constraint b1 foreign key (b) references t2(a); - ---error ER_CANT_CREATE_TABLE -alter table t2 add constraint b1 foreign key (b) references t1(a); - -alter table t2 drop foreign key b; -alter table t1 drop foreign key b1; - -drop table t2; -drop table t1; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index e5582a76d3e..fe05ce63ee7 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -8,7 +8,7 @@ --echo # create table t1 (f1 int primary key) engine=InnoDB; ---error ER_CANT_CREATE_TABLE +--error ER_DUP_CONSTRAINT_NAME create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1), constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; @@ -159,12 +159,11 @@ CREATE DATABASE best default character set latin1; CREATE TABLE t3 (a INT PRIMARY KEY, CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b), -FOREIGN KEY t2_ibfk_1 (a) REFERENCES test.t1(a)) ENGINE=InnoDB; ---replace_regex /Table '.*t2'/Table 't2'/ ---error ER_TABLE_EXISTS_ERROR +FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB; RENAME TABLE best.t2 TO test.t2; -SHOW CREATE TABLE best.t2; +SHOW CREATE TABLE test.t2; DROP DATABASE best; +DROP TABLE t2; --echo # --echo # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs @@ -202,8 +201,8 @@ DROP TABLE t3,t1; --echo # MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N --echo # or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN --echo # -CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB; -ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a); +CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a), c INT) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (c) REFERENCES t1 (a); SET SESSION FOREIGN_KEY_CHECKS = OFF; ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL; ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY; @@ -724,7 +723,7 @@ DROP TABLE t2, t1; # MDEV-21792 Server aborts upon attempt to create foreign key on spatial field # Fail to create foreign key for spatial fields ---error ER_NO_SUCH_TABLE +--error ER_CANT_CREATE_TABLE CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; @@ -789,3 +788,116 @@ drop table t1; --echo # End of 10.5 tests --source include/wait_until_count_sessions.inc + +--echo # +--echo # Bug #20762798 FK DDL: CRASH IN DICT_FOREIGN_REMOVE_FROM_CACHE +--echo # + +create table t1(a int, b int, key(a),key(b))engine=innodb; +create table t2(a int, b int, key(a),key(b))engine=innodb; + +alter table t2 add constraint b foreign key (b) references t1(a); +alter table t1 add constraint b1 foreign key (b) references t2(a); + +alter table t2 add constraint b1 foreign key (b) references t1(a); + +alter table t2 drop foreign key b; +alter table t1 drop foreign key b1; + +drop table t2; +drop table t1; + +# +# MDEV-10083: Orphan ibd file when playing with foreign keys +# +--disable_query_log +SET @start_global_fpt = @@global.innodb_file_per_table; +SET @start_global_fkc = @@global.foreign_key_checks; +--enable_query_log + +set global innodb_file_per_table = 1; + +--disable_warnings +drop table if exists b; +drop database if exists bug_fk; +--enable_warnings + +let $MYSQLD_DATADIR = `select @@datadir`; + +create database bug_fk; +use bug_fk; + +CREATE TABLE b ( + b int unsigned NOT NULL, + d1 datetime NOT NULL, + PRIMARY KEY (b,d1) +) ENGINE=InnoDB; + +CREATE TABLE c ( + b int unsigned NOT NULL, + d1 datetime NOT NULL, + d2 datetime NOT NULL, + PRIMARY KEY (b,d1), + CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; + +insert into b values (1, '1980-01-01 00:00:00'); +insert into c values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +--error ER_ROW_IS_REFERENCED_2 +delete from b; +--error ER_NO_REFERENCED_ROW_2 +update c set b= 2; + +show warnings; + +set foreign_key_checks = 0; + +DROP TABLE IF EXISTS b; + +show create table c; + +# +# Note that column b has different type in parent table +# +CREATE TABLE b ( + b bigint unsigned NOT NULL, + d1 date NOT NULL, + PRIMARY KEY (b,d1) +) ENGINE=InnoDB; + +show warnings; + +DROP TABLE IF EXISTS d; + +CREATE TABLE d ( + b bigint unsigned NOT NULL, + d1 date NOT NULL, + PRIMARY KEY (b,d1), + CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; + +show warnings; + +set foreign_key_checks = 1; + +show create table c; +show create table d; + +--error ER_NO_REFERENCED_ROW_2 +update c set b= 2; +--error ER_NO_REFERENCED_ROW_2 +insert into c values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +insert into b values (2, '1980-01-01 00:00:00'); +--error ER_NO_REFERENCED_ROW_2 +insert into c values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:00'); +insert into d values (2, '1980-01-01 00:00:00'); + +# +# Cleanup +# +--disable_query_log +SET @@global.innodb_file_per_table = @start_global_fpt; +SET @@global.foreign_key_checks = @start_global_fkc; +--enable_query_log + +drop database bug_fk; diff --git a/mysql-test/suite/innodb/t/information_schema_grants.opt b/mysql-test/suite/innodb/t/information_schema_grants.opt index 38c5e3cf1cd..93fbafe22f0 100644 --- a/mysql-test/suite/innodb/t/information_schema_grants.opt +++ b/mysql-test/suite/innodb/t/information_schema_grants.opt @@ -22,8 +22,6 @@ --enable-plugin-innodb-sys-indexes --enable-plugin-innodb-sys-columns --enable-plugin-innodb-sys-fields ---enable-plugin-innodb-sys-foreign ---enable-plugin-innodb-sys-foreign-cols --enable-plugin-innodb-sys-tablespaces --enable-plugin-innodb-sys-datafiles --enable-plugin-innodb-sys-virtual diff --git a/mysql-test/suite/innodb/t/information_schema_grants.test b/mysql-test/suite/innodb/t/information_schema_grants.test index 72982b3ec1c..1322606536a 100644 --- a/mysql-test/suite/innodb/t/information_schema_grants.test +++ b/mysql-test/suite/innodb/t/information_schema_grants.test @@ -76,12 +76,6 @@ create sql security definer view d_sys_datafiles as select * from information_sc create sql security invoker view i_sys_fields as select * from information_schema.innodb_sys_fields; create sql security definer view d_sys_fields as select * from information_schema.innodb_sys_fields; -create sql security invoker view i_sys_foreign as select * from information_schema.innodb_sys_foreign; -create sql security definer view d_sys_foreign as select * from information_schema.innodb_sys_foreign; - -create sql security invoker view i_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols; -create sql security definer view d_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols; - create sql security invoker view i_sys_indexes as select * from information_schema.innodb_sys_indexes; create sql security definer view d_sys_indexes as select * from information_schema.innodb_sys_indexes; @@ -237,18 +231,6 @@ select count(*) > -1 from i_sys_fields; select count(*) > -1 from d_sys_fields; --error ER_SPECIFIC_ACCESS_DENIED_ERROR -select count(*) > -1 from information_schema.innodb_sys_foreign; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -select count(*) > -1 from i_sys_foreign; -select count(*) > -1 from d_sys_foreign; - ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -select count(*) > -1 from information_schema.innodb_sys_foreign_cols; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -select count(*) > -1 from i_sys_foreign_cols; -select count(*) > -1 from d_sys_foreign_cols; - ---error ER_SPECIFIC_ACCESS_DENIED_ERROR select count(*) > -1 from information_schema.innodb_sys_indexes; --error ER_SPECIFIC_ACCESS_DENIED_ERROR select count(*) > -1 from i_sys_indexes; diff --git a/mysql-test/suite/innodb/t/innodb-alter-debug.test b/mysql-test/suite/innodb/t/innodb-alter-debug.test index 7fbbb3159ee..c3eb9dfa4ff 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-debug.test +++ b/mysql-test/suite/innodb/t/innodb-alter-debug.test @@ -4,33 +4,6 @@ --source include/count_sessions.inc -SET NAMES utf8; - -CREATE TABLE ① ( - c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) -ENGINE = InnoDB; - -CREATE TABLE t1ć (c1 INT PRIMARY KEY, c2 INT, INDEX(c2), - CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES ①(c2)) -ENGINE=InnoDB; - -INSERT INTO ① SET c1 = 1; - -SET @saved_debug_dbug = @@SESSION.debug_dbug; -SET DEBUG_DBUG = '+d,ib_drop_foreign_error'; ---error ER_RECORD_FILE_FULL -ALTER TABLE t1ć DROP FOREIGN KEY t1c2, RENAME TO ②; -SET DEBUG_DBUG = @saved_debug_dbug; - -SET DEBUG_DBUG = '+d,ib_rename_column_error'; ---error ER_RECORD_FILE_FULL -ALTER TABLE ① CHANGE c2 š INT; -SET DEBUG_DBUG = @saved_debug_dbug; - -SHOW CREATE TABLE t1ć; - -DROP TABLE t1ć, ①; - --echo # --echo # Bug #21364096 THE BOGUS DUPLICATE KEY ERROR IN ONLINE DDL --echo # WITH INCORRECT KEY NAME diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index f72935ebc3c..002589ec412 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -22,13 +22,6 @@ CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3), CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2)) ENGINE=InnoDB; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i -WHERE FOR_NAME LIKE 'test/t%'; - -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; - -- source suite/innodb/include/innodb_dict.inc SHOW CREATE TABLE t1; @@ -53,9 +46,6 @@ ALTER TABLE t1 CHANGE c3 C INT; ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; - -- source suite/innodb/include/innodb_dict.inc -- error ER_BAD_FIELD_ERROR @@ -360,7 +350,7 @@ DROP INDEX ct, ALGORITHM=INPLACE; SHOW CREATE TABLE t1o; -DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign; +DROP TABLE t1c, t1p, sys_tables, sys_indexes; # Check the internal schemata of tt, t1o. @@ -368,8 +358,6 @@ CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1o'; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o'; -- source suite/innodb/include/innodb_dict.inc @@ -393,7 +381,7 @@ INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; -- source suite/innodb/include/innodb_dict.inc -DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; +DROP TABLE tt, t1o, sys_tables, sys_indexes; CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB; DROP INDEX t ON t; @@ -455,10 +443,6 @@ SELECT F.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS F INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON I.TABLE_ID=T.TABLE_ID WHERE T.NAME='test/t1' AND I.NAME='PRIMARY'; -SELECT C.REF_COL_NAME, C.FOR_COL_NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS C INNER JOIN - INFORMATION_SCHEMA.INNODB_SYS_FOREIGN F ON C.ID=F.ID - WHERE F.FOR_NAME='test/t2'; - DROP TABLE t2, t1; --echo # virtual columns case too CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB; diff --git a/mysql-test/suite/innodb/t/innodb-fk-warnings.test b/mysql-test/suite/innodb/t/innodb-fk-warnings.test index b0034f80345..9f3214eb5bc 100644 --- a/mysql-test/suite/innodb/t/innodb-fk-warnings.test +++ b/mysql-test/suite/innodb/t/innodb-fk-warnings.test @@ -1,35 +1,6 @@ --source include/have_innodb.inc # -# MDEV-8524: Improve error messaging when there is duplicate key or foreign key names -# -CREATE TABLE t1 ( - id int(11) NOT NULL PRIMARY KEY, - a int(11) NOT NULL, - b int(11) NOT NULL, - c int not null, - CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -# -# Below create table fails because constraint name test -# is reserved for above table. -# ---error ER_CANT_CREATE_TABLE -CREATE TABLE t2 ( - id int(11) NOT NULL PRIMARY KEY, - a int(11) NOT NULL, - b int(11) NOT NULL, - c int not null, - CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), - CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -show warnings; - -drop table t1; - -# # MDEV-6697: Improve foreign keys warnings/errors # @@ -49,12 +20,12 @@ show warnings; drop table t1; create table t1(a int not null primary key, b int) engine=innodb; ---error ER_CANT_CREATE_TABLE +--error ER_DUP_CONSTRAINT_NAME create table t2(a int, b int, constraint a foreign key a (a) references t1(a), constraint a foreign key a (a) references t1(b)) engine=innodb; show warnings; create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb; ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_FK_DEF alter table t2 add constraint b foreign key (b) references t2(b); show warnings; drop table t2, t1; @@ -115,7 +86,7 @@ drop table t1; # ON UPDATE/DELETE SET NULL on NOT NULL column # create table t1 (f1 integer not null primary key) engine=innodb; ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_FK_DEF alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null; show warnings; --error ER_CANT_CREATE_TABLE diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test index 09ec0727b9b..6157a429ff4 100644 --- a/mysql-test/suite/innodb/t/innodb-fk.test +++ b/mysql-test/suite/innodb/t/innodb-fk.test @@ -155,7 +155,7 @@ CREATE TABLE `kg_test2`.`person2` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, PRIMARY KEY (`Id`), -CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) +CONSTRAINT `fk_person_group2` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; show create table `kg_test2`.`person2`; diff --git a/mysql-test/suite/innodb/t/innodb-fkcheck.test b/mysql-test/suite/innodb/t/innodb-fkcheck.test deleted file mode 100644 index 4657edc4d65..00000000000 --- a/mysql-test/suite/innodb/t/innodb-fkcheck.test +++ /dev/null @@ -1,116 +0,0 @@ ---source include/have_innodb.inc ---source include/default_charset.inc - -# -# MDEV-10083: Orphan ibd file when playing with foreign keys -# ---disable_query_log -SET @start_global_fpt = @@global.innodb_file_per_table; -SET @start_global_fkc = @@global.foreign_key_checks; ---enable_query_log - -set global innodb_file_per_table = 1; - ---disable_warnings -drop table if exists b; -drop database if exists bug_fk; ---enable_warnings - -let $MYSQLD_DATADIR = `select @@datadir`; - -create database bug_fk; -use bug_fk; - -CREATE TABLE b ( - b int unsigned NOT NULL, - d1 datetime NOT NULL, - PRIMARY KEY (b,d1) -) ENGINE=InnoDB; - -CREATE TABLE c ( - b int unsigned NOT NULL, - d1 datetime NOT NULL, - d2 datetime NOT NULL, - PRIMARY KEY (b,d1), - CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) -) ENGINE=InnoDB; - -show warnings; - -set foreign_key_checks = 0; - -DROP TABLE IF EXISTS b; - -show create table c; - -# -# Note that column b has different type in parent table -# ---error 1005 -CREATE TABLE b ( - b bigint unsigned NOT NULL, - d1 date NOT NULL, - PRIMARY KEY (b,d1) -) ENGINE=InnoDB; - -show warnings; - -DROP TABLE IF EXISTS d; - -CREATE TABLE d ( - b bigint unsigned NOT NULL, - d1 date NOT NULL, - PRIMARY KEY (b,d1), - CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) -) ENGINE=InnoDB; - -show warnings; - -set foreign_key_checks = 1; - -show create table c; -show create table d; - -# -# Table c column b used on foreign key has different type -# compared referenced column b in table b, but this -# create still produced b.ibd file. This is because -# we row_drop_table_for_mysql was called and referenced -# table is not allowed to be dropped even in case -# when actual create is not successfull. -# ---error 1005 -CREATE TABLE b ( - b bigint unsigned NOT NULL, - d1 date NOT NULL, - PRIMARY KEY (b,d1) -) ENGINE=InnoDB; - -show warnings; - ---list_files $MYSQLD_DATADIR/bug_fk b* - -set foreign_key_checks=0; - -drop table c; -drop table d; - ---list_files $MYSQLD_DATADIR/bug_fk b* - -create table b(id int) engine=innodb; -show warnings; - ---list_files $MYSQLD_DATADIR/bug_fk b* - -# -# Cleanup -# ---disable_query_log -SET @@global.innodb_file_per_table = @start_global_fpt; -SET @@global.foreign_key_checks = @start_global_fkc; ---enable_query_log - ---disable_warnings -drop table if exists b; -drop database if exists bug_fk; ---enable_warnings diff --git a/mysql-test/suite/innodb/t/innodb-index-online-fk.opt b/mysql-test/suite/innodb/t/innodb-index-online-fk.opt index 345d5529dce..24ad32542fe 100644 --- a/mysql-test/suite/innodb/t/innodb-index-online-fk.opt +++ b/mysql-test/suite/innodb/t/innodb-index-online-fk.opt @@ -1,4 +1,2 @@ --loose-innodb-sys-tables --loose-innodb-sys-columns ---loose-innodb-sys-foreign ---loose-innodb-sys-foreign-cols diff --git a/mysql-test/suite/innodb/t/innodb-index-online-fk.test b/mysql-test/suite/innodb/t/innodb-index-online-fk.test index 5c8954064ce..e80fb507f68 100644 --- a/mysql-test/suite/innodb/t/innodb-index-online-fk.test +++ b/mysql-test/suite/innodb/t/innodb-index-online-fk.test @@ -24,10 +24,6 @@ ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - # duplicated foreign key name --error ER_DUP_CONSTRAINT_NAME ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2) @@ -69,10 +65,6 @@ ALGORITHM = INPLACE; ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - SET foreign_key_checks = 1; --error ER_NO_REFERENCED_ROW_2 @@ -99,10 +91,6 @@ REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; SET DEBUG_DBUG = @saved_debug_dbug; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; SELECT NAME FROM information_schema.INNODB_SYS_TABLES; @@ -137,10 +125,6 @@ SET DEBUG_DBUG = @saved_debug_dbug; SHOW ERRORS; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - # This is to test the scenario no index on referenced table, # alter table should fail SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx'; @@ -161,19 +145,6 @@ REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; SET DEBUG_DBUG = @saved_debug_dbug; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - -# This is to test the scenario cannot add fk to the system table, -# alter table should fail -SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; ---error ER_FK_FAIL_ADD_SYSTEM -ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2) -REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE, -ALGORITHM = INPLACE; -SET DEBUG_DBUG = @saved_debug_dbug; - SHOW ERRORS; DROP TABLE `#child`; @@ -188,10 +159,6 @@ ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2) REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - DROP TABLE child; DROP TABLE parent; @@ -220,10 +187,6 @@ ALGORITHM = INPLACE; SHOW CREATE TABLE child; -SELECT * FROM information_schema.INNODB_SYS_FOREIGN; - -SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; - SET foreign_key_checks = 1; DROP TABLE child; @@ -274,18 +237,8 @@ CREATE INDEX tb ON child(a2); SET foreign_key_checks = 0; -# Let's rebuild the table and add the FK, make the add FK failed. +# Let's rebuild the table and add the FK -SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; ---error ER_FK_FAIL_ADD_SYSTEM -ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, -ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) -ON DELETE SET NULL ON UPDATE CASCADE, -ALGORITHM = INPLACE; -SET DEBUG_DBUG = @saved_debug_dbug; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; SELECT NAME FROM information_schema.INNODB_SYS_TABLES; @@ -297,8 +250,6 @@ ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; SELECT NAME FROM information_schema.INNODB_SYS_TABLES; @@ -314,8 +265,6 @@ ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * from information_schema.INNODB_SYS_FOREIGN; -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; SELECT NAME FROM information_schema.INNODB_SYS_TABLES; @@ -330,8 +279,6 @@ ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; -SELECT * from information_schema.INNODB_SYS_FOREIGN; -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name; SELECT NAME FROM information_schema.INNODB_SYS_TABLES; @@ -371,9 +318,6 @@ ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT; SHOW CREATE TABLE child; -SELECT * from information_schema.INNODB_SYS_FOREIGN; -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; - # The third add FK will fail --error ER_FK_NO_INDEX_PARENT ALTER TABLE child @@ -385,10 +329,6 @@ ALGORITHM = INPLACE; # It should still have only 2 FKs SHOW CREATE TABLE child; -SELECT * from information_schema.INNODB_SYS_FOREIGN; - -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; - #Now let's make it successful ALTER TABLE child ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b), @@ -399,10 +339,6 @@ ALGORITHM = INPLACE; # It should still have 5 FKs SHOW CREATE TABLE child; -SELECT * from information_schema.INNODB_SYS_FOREIGN; - -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; - DROP TABLE child; CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB; CREATE INDEX tb ON child(a2); @@ -418,10 +354,6 @@ ALGORITHM = INPLACE; # It should still have no FKs, no PRIMARY SHOW CREATE TABLE child; -SELECT * from information_schema.INNODB_SYS_FOREIGN; - -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; - # make it successful ALTER TABLE child ADD PRIMARY KEY idx (a1), ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b), @@ -432,10 +364,6 @@ ALGORITHM = INPLACE; # It should have 3 FKs, a new PRIMARY SHOW CREATE TABLE child; -SELECT * from information_schema.INNODB_SYS_FOREIGN; - -SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; - SET foreign_key_checks = 1; DROP TABLE child; @@ -473,12 +401,6 @@ ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a); ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a); ---error ER_FK_FAIL_ADD_SYSTEM -ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL; - -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - DROP TABLE t2; DROP TABLE t3; diff --git a/mysql-test/suite/innodb/t/innodb-index-online.test b/mysql-test/suite/innodb/t/innodb-index-online.test index 5e21fa896a4..17bb3aa3163 100644 --- a/mysql-test/suite/innodb/t/innodb-index-online.test +++ b/mysql-test/suite/innodb/t/innodb-index-online.test @@ -84,7 +84,7 @@ DELETE FROM t1 WHERE c1 = 7; connection con1; # ADD FOREIGN KEY is not supported in-place --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t1(c2), ALGORITHM = INPLACE; +ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES t1(c2), ALGORITHM = INPLACE; # The previous DEBUG_SYNC should be ignored, because an exclusive lock # has been requested and the online log is not being allocated. ALTER TABLE t1 ADD UNIQUE INDEX(c2), LOCK = EXCLUSIVE, ALGORITHM = INPLACE; diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 810671c54c8..b0fb0769c43 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -221,10 +221,10 @@ show create table t4; # Embedded server doesn't chdir to data directory --replace_result $MYSQLD_DATADIR ./ master-data/ '' # a foreign key 'test/dc' already exists ---error ER_CANT_CREATE_TABLE +--error ER_DUP_CONSTRAINT_NAME alter table t3 add constraint dc foreign key (a) references t1(a); SET FOREIGN_KEY_CHECKS=0; ---error ER_FK_FAIL_ADD_SYSTEM +--error ER_DUP_CONSTRAINT_NAME alter table t3 add constraint dc foreign key (a) references t1(a); SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; show create table t3; diff --git a/mysql-test/suite/innodb/t/innodb-system-table-view.opt b/mysql-test/suite/innodb/t/innodb-system-table-view.opt index 4d6858cbe0b..371d5d7a333 100644 --- a/mysql-test/suite/innodb/t/innodb-system-table-view.opt +++ b/mysql-test/suite/innodb/t/innodb-system-table-view.opt @@ -6,7 +6,5 @@ --innodb-sys-columns --innodb-sys-indexes --innodb-sys-fields ---innodb-sys-foreign ---innodb-sys-foreign-cols --character_set_server="latin1" --collation_server="latin1_swedish_ci" diff --git a/mysql-test/suite/innodb/t/innodb-system-table-view.test b/mysql-test/suite/innodb/t/innodb-system-table-view.test index 4f5111eafbc..e817fd4f672 100644 --- a/mysql-test/suite/innodb/t/innodb-system-table-view.test +++ b/mysql-test/suite/innodb/t/innodb-system-table-view.test @@ -33,11 +33,6 @@ SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name') ORDER BY index_id, pos; ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb; CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb; CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2; @@ -51,8 +46,6 @@ DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic; SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; # Create a foreign key constraint, and verify the information -# in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN and -# INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; @@ -62,12 +55,6 @@ CREATE TABLE child (id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB; ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; - ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - # Insert a row in the table "parent", and see whether that reflected in # INNODB_SYS_TABLESTATS INSERT INTO parent VALUES(1); @@ -118,12 +105,6 @@ CREATE TABLE child (id INT, parent_id INT, FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid) ON DELETE CASCADE) ENGINE=INNODB; ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; - ---sorted_result -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; - INSERT INTO parent VALUES(1, 9); # Nested query will open the table handle twice diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test index 78e3862a909..5413b36b956 100644 --- a/mysql-test/suite/innodb/t/innodb-truncate.test +++ b/mysql-test/suite/innodb/t/innodb-truncate.test @@ -83,8 +83,6 @@ SET FOREIGN_KEY_CHECKS= OFF; CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS= ON; CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t3 (a INT) ENGINE=InnoDB; --replace_result $datadir ./ --error ER_NO_SUCH_TABLE ALTER TABLE t1 RENAME TO t3; diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test index 1a32b94c140..3c0e1761ecf 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test @@ -38,13 +38,6 @@ eval CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3), CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2)) ENGINE=InnoDB $data_directory_clause; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i -WHERE FOR_NAME LIKE 'test/t%'; - -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; - -- source suite/innodb/include/innodb_dict.inc --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR @@ -126,9 +119,6 @@ ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; --cat_file $MYSQL_DATA_DIR.files.txt --remove_file $MYSQL_DATA_DIR.files.txt -SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i -INNER JOIN sys_foreign sf ON i.ID = sf.ID; - -- source suite/innodb/include/innodb_dict.inc -- error ER_BAD_FIELD_ERROR @@ -638,7 +628,7 @@ DROP INDEX ct, LOCK=NONE; --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR SHOW CREATE TABLE t1o; -DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign; +DROP TABLE t1c, t1p, sys_tables, sys_indexes; # Check the internal schemata of tt, t1o. @@ -646,8 +636,6 @@ CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1o'; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; -CREATE TABLE sys_foreign SELECT i.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o'; -- source suite/innodb/include/innodb_dict.inc @@ -693,7 +681,7 @@ INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; --echo # Cleanup --echo # -DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; +DROP TABLE tt, t1o, sys_tables, sys_indexes; --echo ### files in MYSQL_DATA_DIR/test --list_files_write_file $MYSQL_DATA_DIR.files.txt $MYSQL_DATA_DIR/test diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 95145bd8eeb..d15d43ef19c 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1598,12 +1598,12 @@ disconnect b; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; -# Embedded server doesn't chdir to data directory ---replace_result $MYSQLTEST_VARDIR . master-data/ '' ---error ER_CANT_CREATE_TABLE create table t1(a char(10) primary key, b varchar(20)) engine = innodb; set foreign_key_checks=1; -drop table t2; +insert into t1 values (1, 1); +--error ER_NO_REFERENCED_ROW_2 +insert into t2 values (1, 1); +drop tables t2, t1; # test that FKs between different charsets are not accepted in CREATE even # when f_k_c is 0 @@ -1617,15 +1617,6 @@ create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innod set foreign_key_checks=1; drop table t1; -# test that invalid datatype conversions with ALTER are not allowed - -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; -create table t1(a varchar(10) primary key) engine = innodb; -alter table t1 modify column a int; -set foreign_key_checks=1; -drop table t2,t1; - # test that charset conversions with ALTER are allowed when f_k_c is 0 set foreign_key_checks=0; @@ -1635,19 +1626,6 @@ alter table t1 convert to character set utf8; set foreign_key_checks=1; drop table t2,t1; -# test that RENAME does not allow invalid charsets when f_k_c is 0 - -call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition."); -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; -create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; -# Embedded server doesn't chdir to data directory ---replace_result $MYSQLD_DATADIR ./ master-data/ '' --- error 1025 -rename table t3 to t1; -set foreign_key_checks=1; -drop table t2,t3; - # test that foreign key errors are reported correctly (Bug #15550) create table t1(a int primary key) row_format=redundant engine=innodb; diff --git a/mysql-test/suite/innodb/t/innodb_bug12902967.test b/mysql-test/suite/innodb/t/innodb_bug12902967.test deleted file mode 100644 index 5bd32cdf627..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug12902967.test +++ /dev/null @@ -1,25 +0,0 @@ -# Bug 12902967: Creating self referencing fk on same index unhandled, -# confusing error -# -# Creating a self referencing foreign key on the same -# column/index is an unhandled exception, it should throw a sensible -# error but instead implies that your data dictionary may now be out -# of sync: - ---source include/have_innodb.inc ---source include/not_embedded.inc - -call mtr.add_suppression("In ALTER TABLE .* has or is referenced in foreign key constraints which are not compatible with the new table definition."); - -let error_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; ---source include/restart_mysqld.inc - -create table t1 (f1 integer primary key) engine innodb; - -# The below statement should produce error message in error log. -# This error message should mention problem with foreign keys -# rather than with data dictionary. ---replace_regex /'\.\/test\/#sql-alter-[0-9a-f_\-]*'/'#sql-alter'/ ---error ER_ERROR_ON_RENAME -alter table t1 add constraint c1 foreign key (f1) references t1(f1); -drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug21704.test b/mysql-test/suite/innodb/t/innodb_bug21704.test index 82e7c81d0e4..588bab03bc8 100644 --- a/mysql-test/suite/innodb/t/innodb_bug21704.test +++ b/mysql-test/suite/innodb/t/innodb_bug21704.test @@ -65,12 +65,6 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SHOW CREATE TABLE t3; -SELECT f.*, c.* -FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS c -INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FOREIGN f -ON c.ID=f.ID -WHERE FOR_NAME LIKE 'test/t%'; - DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug57255.test b/mysql-test/suite/innodb/t/innodb_bug57255.test index cf7982a6ddf..0b0d3506344 100644 --- a/mysql-test/suite/innodb/t/innodb_bug57255.test +++ b/mysql-test/suite/innodb/t/innodb_bug57255.test @@ -36,3 +36,5 @@ DELETE FROM A where id = 1; DROP TABLE C; DROP TABLE B; DROP TABLE A; + +flush tables; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt index 9f30d81ef9c..87d83110b19 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt @@ -15,8 +15,6 @@ --loose-innodb_sys_indexes --loose-innodb_sys_columns --loose-innodb_sys_fields ---loose-innodb_sys_foreign ---loose-innodb_sys_foreign_cols --loose-innodb_changed_pages --loose-innodb_rseg --loose-innodb_undo_logs diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.test b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test index 15b3bf4f561..7a6ab7a9e4e 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema_tables.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test @@ -35,10 +35,6 @@ SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS; --error 0,1109 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS; --error 0,1109 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; ---error 0,1109 -SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; ---error 0,1109 SELECT * FROM INFORMATION_SCHEMA.INNODB_RSEG; --error 0,1109 SELECT * FROM INFORMATION_SCHEMA.INNODB_UNDO_LOGS; diff --git a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.opt b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.opt index 513791a0a88..4a1d3c0a9f2 100644 --- a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.opt +++ b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.opt @@ -23,8 +23,6 @@ --loose-innodb_sys_indexes --loose-innodb_sys_columns --loose-innodb_sys_fields ---loose-innodb_sys_foreign ---loose-innodb_sys_foreign_cols --loose-innodb_sys_tablespaces --loose-innodb_sys_datafiles --loose-innodb_changed_pages @@ -53,8 +51,6 @@ --loose-innodb_sys_indexes --loose-innodb_sys_columns --loose-innodb_sys_fields ---loose-innodb_sys_foreign ---loose-innodb_sys_foreign_cols --loose-innodb_sys_tablespaces --loose-innodb_sys_datafiles --loose-innodb_changed_pages diff --git a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test index a5d5d3fe34e..f466e320682 100644 --- a/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test +++ b/mysql-test/suite/innodb/t/innodb_skip_innodb_is_tables.test @@ -25,8 +25,6 @@ select * from information_schema.innodb_sys_tablestats; select * from information_schema.innodb_sys_indexes; select * from information_schema.innodb_sys_columns; select * from information_schema.innodb_sys_fields; -select * from information_schema.innodb_sys_foreign; -select * from information_schema.innodb_sys_foreign_cols; select * from information_schema.innodb_sys_tablespaces; select * from information_schema.innodb_sys_datafiles; --error 0,1109 diff --git a/mysql-test/suite/innodb/t/instant_alter_index_rename.test b/mysql-test/suite/innodb/t/instant_alter_index_rename.test index 3f5234b46f6..051ac02c27f 100644 --- a/mysql-test/suite/innodb/t/instant_alter_index_rename.test +++ b/mysql-test/suite/innodb/t/instant_alter_index_rename.test @@ -192,7 +192,7 @@ drop table rename_column_and_index; create table t1 (f1 int, f2 int, f3 int); create table t2 (f2 int primary key); alter table t1 add foreign key f (f2) references t2(f2); -alter table t1 add foreign key (f2) references t1(f2), add key (f3), add key (f1); +alter table t1 add foreign key (f1) references t1(f2), add key (f3), add key (f1); drop tables t1, t2; --echo # diff --git a/mysql-test/suite/innodb/t/truncate_foreign.test b/mysql-test/suite/innodb/t/truncate_foreign.test index d9d647e69f0..31a29291a64 100644 --- a/mysql-test/suite/innodb/t/truncate_foreign.test +++ b/mysql-test/suite/innodb/t/truncate_foreign.test @@ -53,11 +53,12 @@ send INSERT INTO child SET a=5; connection default; SET DEBUG_SYNC='now WAIT_FOR fk'; SET foreign_key_checks=0; +# NB: child now prelocks parent +--error ER_LOCK_WAIT_TIMEOUT TRUNCATE TABLE parent; SET DEBUG_SYNC='now SIGNAL go'; connection dml; ---error ER_NO_REFERENCED_ROW_2 reap; SELECT * FROM parent; SELECT * FROM child; diff --git a/mysql-test/suite/innodb/t/undo_truncate.test b/mysql-test/suite/innodb/t/undo_truncate.test index b4c8e46150b..6fd08947a1f 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.test +++ b/mysql-test/suite/innodb/t/undo_truncate.test @@ -84,7 +84,7 @@ drop PROCEDURE populate_t2; # Truncation will normally not occur with innodb_page_size=64k, # and occasionally not with innodb_page_size=32k, # because the undo log will not grow enough. -if (`select @@innodb_page_size IN (4096,8192,16384)`) +if (`select @@innodb_page_size IN (8192,16384)`) { let $wait_condition = (SELECT variable_value!=@trunc_start FROM information_schema.global_status @@ -109,7 +109,7 @@ EOF if ($size1 == $size2) { # This fails for innodb_page_size=64k, occasionally also for 32k. - if (`select @@innodb_page_size IN (4096,8192,16384)`) + if (`select @@innodb_page_size IN (8192,16384)`) { echo Truncation did not happen: $size1; } diff --git a/mysql-test/suite/innodb_i_s/innodb_sys_foreign.result b/mysql-test/suite/innodb_i_s/innodb_sys_foreign.result deleted file mode 100644 index 8dc7cbb2c53..00000000000 --- a/mysql-test/suite/innodb_i_s/innodb_sys_foreign.result +++ /dev/null @@ -1,9 +0,0 @@ -SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; -Table Create Table -INNODB_SYS_FOREIGN CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN` ( - `ID` varchar(193) NOT NULL DEFAULT '', - `FOR_NAME` varchar(193) NOT NULL DEFAULT '', - `REF_NAME` varchar(193) NOT NULL DEFAULT '', - `N_COLS` int(11) unsigned NOT NULL DEFAULT 0, - `TYPE` int(11) unsigned NOT NULL DEFAULT 0 -) ENGINE=MEMORY DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/innodb_i_s/innodb_sys_foreign.test b/mysql-test/suite/innodb_i_s/innodb_sys_foreign.test deleted file mode 100644 index 694a773392e..00000000000 --- a/mysql-test/suite/innodb_i_s/innodb_sys_foreign.test +++ /dev/null @@ -1,3 +0,0 @@ ---source include/have_innodb.inc - -SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; diff --git a/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.result b/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.result deleted file mode 100644 index 8f1134fb36d..00000000000 --- a/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.result +++ /dev/null @@ -1,8 +0,0 @@ -SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; -Table Create Table -INNODB_SYS_FOREIGN_COLS CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN_COLS` ( - `ID` varchar(193) NOT NULL DEFAULT '', - `FOR_COL_NAME` varchar(64) NOT NULL DEFAULT '', - `REF_COL_NAME` varchar(64) NOT NULL DEFAULT '', - `POS` int(11) unsigned NOT NULL DEFAULT 0 -) ENGINE=MEMORY DEFAULT CHARSET=utf8 diff --git a/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.test b/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.test deleted file mode 100644 index 6afccfc212f..00000000000 --- a/mysql-test/suite/innodb_i_s/innodb_sys_foreign_cols.test +++ /dev/null @@ -1,3 +0,0 @@ ---source include/have_innodb.inc - -SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; |