dbs/mysql-server
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
mysql-server/mysql-test/t/foreign_key.test
Ho Sy Tan 4a107fd10e 2025-03-05 14:31:08
2025-03-05 14:31:37 +07:00

5096 lines
180 KiB
Text
Raw Permalink Blame History

#
# Test syntax of foreign keys
#
--disable_query_log
call mtr.add_suppression(" In RENAME TABLE table `test`.`parent` is referenced in foreign key constraints which are not compatible with the new table definition.");
--enable_query_log
SET @saved_binlog_format= @@SESSION.binlog_format;
#
# Bug#34455 (Ambiguous foreign keys syntax is accepted)
#
--disable_warnings
drop table if exists t_34455;
--enable_warnings
# 2 match clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) match full match partial);
# match after on delete, illegal
--error ER_PARSE_ERROR
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) on delete set default match full);
# match after on update, illegal
--error ER_PARSE_ERROR
create table t_34455 (
a int not null,
foreign key (a) references t3 (a) on update set default match full);
# 2 on delete clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
a int not null,
foreign key (a) references t3 (a)
on delete set default on delete set default);
# 2 on update clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
a int not null,
foreign key (a) references t3 (a)
on update set default on update set default);
create table t_34455 (a int not null);
# 2 match clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
add foreign key (a) references t3 (a) match full match partial);
# match after on delete, illegal
--error ER_PARSE_ERROR
alter table t_34455
add foreign key (a) references t3 (a) on delete set default match full);
# match after on update, illegal
--error ER_PARSE_ERROR
alter table t_34455
add foreign key (a) references t3 (a) on update set default match full);
# 2 on delete clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
add foreign key (a) references t3 (a)
on delete set default on delete set default);
# 2 on update clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
add foreign key (a) references t3 (a)
on update set default on update set default);
drop table t_34455;
--echo #
--echo # WL#6929: Move FOREIGN KEY constraints to the global data dictionary
--echo #
--echo # Extra coverage of @@foreign_key_checks
--echo #
SET @@foreign_key_checks= 0;
CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a));
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a);
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a));
DROP TABLE t1;
DROP TABLE t2;
SET @@foreign_key_checks= 1;
--error ER_FK_CANNOT_OPEN_PARENT
CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a));
# Works, but no FK created
CREATE TABLE t1(a INT PRIMARY KEY, b INT REFERENCES non(a));
SHOW CREATE TABLE t1;
--error ER_FK_CANNOT_OPEN_PARENT
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a);
DROP TABLE t1;
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a));
--error ER_FK_CANNOT_DROP_PARENT
DROP TABLE t1;
DROP TABLE t2, t1;
SET @@foreign_key_checks= DEFAULT;
--echo # Test coverage of identifier length related to foreign keys.
--echo #
CREATE TABLE t1(a INT PRIMARY KEY);
# Exactly 64 chars
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
ALTER TABLE t2 ADD CONSTRAINT
name567890123456789012345678901234567890123456789012345678901234
FOREIGN KEY
name567890123456789012345678901234567890123456789012345678901234
(b) REFERENCES t1(a);
SHOW CREATE TABLE t2;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 't1';
DROP TABLE t2;
# 65 chars - too long
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
--error ER_TOO_LONG_IDENT
ALTER TABLE t2 ADD FOREIGN KEY
name5678901234567890123456789012345678901234567890123456789012345
(b) REFERENCES t1(a);
--error ER_TOO_LONG_IDENT
ALTER TABLE t2 ADD CONSTRAINT
name5678901234567890123456789012345678901234567890123456789012345
FOREIGN KEY (b) REFERENCES t1(a);
DROP TABLE t2;
# 65 chars - too long, now with pre-existing index on b
CREATE TABLE t2(a INT PRIMARY KEY, b INT UNIQUE);
--error ER_TOO_LONG_IDENT
ALTER TABLE t2 ADD FOREIGN KEY
name5678901234567890123456789012345678901234567890123456789012345
(b) REFERENCES t1(a);
--error ER_TOO_LONG_IDENT
ALTER TABLE t2 ADD CONSTRAINT
name5678901234567890123456789012345678901234567890123456789012345
FOREIGN KEY (b) REFERENCES t1(a);
DROP TABLE t2;
DROP TABLE t1;
--error ER_TOO_LONG_IDENT
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a));
--error ER_WRONG_TABLE_NAME
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a));
--error ER_WRONG_COLUMN_NAME
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345));
SET @@foreign_key_checks= 0;
--error ER_TOO_LONG_IDENT
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a));
--error ER_WRONG_TABLE_NAME
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a));
--error ER_WRONG_COLUMN_NAME
CREATE TABLE t1(a INT PRIMARY KEY, b INT,
FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345));
SET @@foreign_key_checks= DEFAULT;
--echo #
--echo # Bug#24666169: I_S.TABLE_CONSTRAINTS.CONSTRAINT_NAME IS NOT UPDATED
--echo # AFTER RENAME TABLE
--echo #
SET @@foreign_key_checks= 1;
--echo #
--echo # Tests for FK name behavior.
CREATE TABLE t1(c1 INT PRIMARY KEY);
CREATE TABLE t2(c1 INT, FOREIGN KEY (c1) REFERENCES t1(c1));
ALTER TABLE t2 RENAME TO t3;
SHOW CREATE TABLE t3;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t3 VALUES(1);
ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE;
SHOW CREATE TABLE t4;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t4 VALUES(1);
# TODO: COPY does not work properly, see Bug#25467454
ALTER TABLE t4 RENAME TO t5; #, ALGORITHM= COPY;
SHOW CREATE TABLE t5;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t5 VALUES(1);
RENAME TABLE t5 to t6;
SHOW CREATE TABLE t6;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t6 VALUES(1);
DROP TABLE t6, t1;
--echo #
--echo # Tests of FK name generation
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a) REFERENCES t1(a));
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Add FK
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Remove first FK and add a new FK.
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Rename table in different ways.
ALTER TABLE t2 RENAME TO t3;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
# COPY does not work properly, see Bug#25467454
ALTER TABLE t4 RENAME TO t5; #, ALGORITHM= COPY;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't5' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't5' ORDER BY constraint_name;
RENAME TABLE t5 TO t6;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
--echo # Simulate dump+restore and test rename
DROP TABLE t6;
CREATE TABLE `t6` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `b` (`b`),
KEY `a` (`a`),
CONSTRAINT `t6_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
CONSTRAINT `t6_ibfk_3` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't6' ORDER BY constraint_name;
RENAME TABLE t6 TO t2;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Remove all FKs and add one back
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_2, DROP FOREIGN KEY t2_ibfk_3;
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Add a foreign key with close to generated name
ALTER TABLE t2 ADD CONSTRAINT t3_ibfk_2 FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--echo # Then rename so that the given name now matches a generated name
RENAME TABLE t2 TO t3;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't3' ORDER BY constraint_name;
--echo # Finally rename it again. The given name is now seen as generated and renamed.
RENAME TABLE t3 TO t4;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't4' ORDER BY constraint_name;
DROP TABLE t4;
--echo # Make a foreign key with given name matching a generated name
CREATE TABLE t2(a INT, b INT);
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_1 FOREIGN KEY(a) REFERENCES t1(a);
# Then add a new FK with generated name
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
DROP TABLE t2;
--echo # Test FK name case sensitivity
CREATE TABLE t2(a INT, b INT);
ALTER TABLE t2 ADD CONSTRAINT FK FOREIGN KEY(a) REFERENCES t1(a);
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't2' ORDER BY constraint_name;
--error ER_DUP_KEYNAME
ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY FK;
--echo # Name matching generated name, but different case.
ALTER TABLE t2 ADD CONSTRAINT T2_IBFK_1 FOREIGN KEY(a) REFERENCES t1(a);
--error ER_FK_DUP_NAME
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY T2_IBFK_1;
DROP TABLE t2;
--echo # Check long FK generated names due to long table names.
CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1(a));
--error ER_TOO_LONG_IDENT
RENAME TABLE t2 TO t123456789012345678901234567890123456789012345678901234567;
RENAME TABLE t2 TO t12345678901234567890123456789012345678901234567890123456;
SELECT constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't12345678901234567890123456789012345678901234567890123456'
ORDER BY constraint_name;
SELECT constraint_name FROM information_schema.table_constraints
WHERE table_name = 't12345678901234567890123456789012345678901234567890123456'
ORDER BY constraint_name;
DROP TABLE t12345678901234567890123456789012345678901234567890123456;
--error ER_TOO_LONG_IDENT
CREATE TABLE t123456789012345678901234567890123456789012345678901234567(
a INT, FOREIGN KEY (a) REFERENCES t1(a));
CREATE TABLE t123456789012345678901234567890123456789012345678901234567890123(
a INT, CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1(a));
DROP TABLE t123456789012345678901234567890123456789012345678901234567890123;
DROP TABLE t1;
--echo # FK Referencing virtual column
CREATE TABLE t1(a INT PRIMARY KEY,
b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE t2(a INT, FOREIGN KEY (a) REFERENCES t1(b));
CREATE TABLE t2(a INT);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(b);
DROP TABLE t1, t2;
--echo # FK on generated stored column
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE);
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a));
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
DROP TABLE t3;
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE CASCADE;
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON DELETE SET NULL;
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE SET NULL;
--echo # FK on Base column of generated stored column.
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a));
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a);
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
DROP TABLE t3;
--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE CASCADE);
--error ER_CANNOT_ADD_FOREIGN
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE CASCADE;
--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON DELETE SET NULL);
--error ER_CANNOT_ADD_FOREIGN
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON DELETE SET NULL;
--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE,
FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE SET NULL);
--error ER_CANNOT_ADD_FOREIGN
ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE SET NULL;
DROP TABLE t2, t1;
--echo # FK on virtual column not supported.
CREATE TABLE t1(a INT PRIMARY KEY);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE,
FOREIGN KEY(b) REFERENCES t1(a));
CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE t2 ADD FOREIGN KEY (b) REFERENCES t1(a);
DROP TABLE t2;
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(b) REFERENCES t1(a));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE t2 MODIFY COLUMN b INT GENERATED ALWAYS AS (a+1) VIRTUAL;
DROP TABLE t2, t1;
--echo # Trying to drop columns used in multi-column FKs.
SET restrict_fk_on_non_standard_key=OFF;
CREATE TABLE t1(a INT PRIMARY KEY, b INT, INDEX(a, b));
CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a, b) REFERENCES t1(a, b));
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE t2 DROP COLUMN a;
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE t2 DROP COLUMN b;
DROP TABLE t2;
--echo # Use explicitly named index to check where index name is != column name.
CREATE TABLE t2(a INT, b INT, INDEX idx(a, b),
FOREIGN KEY(a, b) REFERENCES t1(a, b));
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE t2 DROP COLUMN a;
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE t2 DROP COLUMN b;
DROP TABLE t2, t1;
SET restrict_fk_on_non_standard_key=ON;
--echo # Index with prefix cannot be used for supporting FK.
CREATE TABLE t1 (PK VARCHAR(100) PRIMARY KEY);
CREATE TABLE t2 (FK VARCHAR(100), FOREIGN KEY(FK) REFERENCES t1 (PK), KEY(FK));
--error ER_DROP_INDEX_FK
ALTER TABLE t2 DROP INDEX FK, ADD INDEX FK2(FK(10));
DROP TABLE t2, t1;
--echo # Bug#25817660: Combination of virtual index, foreign key and trigger
--echo # result in assert failure
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0;
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT * FROM t1;
SELECT * FROM t2;
--echo # The same test but with server restart before UPDATE.
--echo # Triggers different code path in InnoDB which was not
--echo # covered by original fix for the bug.
--source include/restart_mysqld.inc
UPDATE t1 SET fld1= 3;
SELECT * FROM t1;
SELECT * FROM t2;
DROP TABLE t2;
DROP TABLE t1;
--echo #
--echo # Bug#20021917: WORK AROUND FOR CHARSET CONVERSION WITH FKS CAN
--echo # RESULT IN WRONG DATA
--echo #
CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), fname VARCHAR(100), FOREIGN KEY (fname) REFERENCES t1 (name) ON UPDATE CASCADE ON DELETE CASCADE);
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;
SET foreign_key_checks= OFF;
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;
SET foreign_key_checks= ON;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
INSERT INTO t1(name) VALUES ('test1');
INSERT INTO t2(name, fname) VALUES ('test1', 'test1');
UPDATE t1 SET name=CONCAT('St', UNHEX('C3A5') ,'le') WHERE name = 'test1';
--echo # Should not get any rows here
SELECT t1.name, t2.fname FROM t1, t2 WHERE t1.name <> t2.fname;
DROP TABLE t2;
DROP TABLE t1;
--echo #
--echo # WL#6049: Meta-data locking for FOREIGN KEY tables
--echo #
--echo # Test case to check MDL on tables related by FK constraints.
--echo #
--enable_connect_log
CREATE TABLE grandparent (gpf1 INT PRIMARY KEY, gpf2 INT);
INSERT INTO grandparent VALUES (1,10), (2,20);
CREATE TABLE parent (
pf1 INT PRIMARY KEY, pf2 INT, sleep_dummy INT,
CONSTRAINT pc1 FOREIGN KEY (pf2) REFERENCES grandparent (gpf1)
ON DELETE NO ACTION ON UPDATE NO ACTION);
INSERT INTO parent VALUES (1,1,0), (2,2,0);
CREATE TABLE child (
cf1 INT PRIMARY KEY, cf2 INT,
CONSTRAINT cc1 FOREIGN KEY (cf2) REFERENCES parent (pf1)
ON DELETE NO ACTION ON UPDATE NO ACTION);
INSERT INTO child VALUES (1,1), (2,2);
--connect (con_A,localhost,root,,test)
--let $conA=`SELECT CONNECTION_ID()`
SET @@session.lock_wait_timeout= 1;
--send UPDATE parent SET pf2= 2, sleep_dummy= SLEEP(2)
--connection default
--replace_result $conA <conA>
--eval set @conA_id = $conA
let $wait_condition=
SELECT COUNT(*) = 1
FROM information_schema.processlist
WHERE id = @conA_id AND state LIKE 'user sleep';
--echo # Waiting for connection A to start udpate
--source include/wait_condition.inc
SET @@session.lock_wait_timeout= 1;
--echo # Updates not invoving the FK related column should succeed
UPDATE grandparent SET gpf2= 4;
UPDATE grandparent SET gpf2= 100 * gpf1;
--echo # DDL on child will have conflicting locks.
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD COLUMN (i INT);
SET @@session.lock_wait_timeout= DEFAULT;
--connection con_A
--reap
--disconnect con_A
--source include/wait_until_disconnected.inc
--connection default
--disable_connect_log
DROP TABLE child;
DROP TABLE parent;
DROP TABLE grandparent;
--echo #
--echo # Systemic test coverage for metadata locks related to foreign
--echo # keys acquired by various DDL statements.
--echo #
--echo # Also provides coverage for data-dictionary cache invalidation
--echo # and cases when we fail to acquire necessary locks.
--enable_connect_log
SET @old_lock_wait_timeout= @@lock_wait_timeout;
connect (con1, localhost, root,,);
connect (con2, localhost, root,,);
SET @old_lock_wait_timeout= @@lock_wait_timeout;
connection default;
--echo #
--echo # 1) CREATE TABLE
--echo #
--echo # 1.1) CREATE TABLE must acquire X lock on parent table
--echo # (if table doesn't exist).
CREATE TABLE parent (pk INT PRIMARY KEY);
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "CREATE TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
--echo # 1.2) CREATE TABLE for existing table should not acquire X lock
--echo # parent table
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
COMMIT;
connection default;
DROP TABLE child;
--echo # 1.3) CREATE TABLE which acquires lock on parent table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE parent;
--echo # 1.4) CREATE TABLE which adds parent to orphan child must acquire X
--echo # on child table.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send CREATE TABLE parent (pk INT PRIMARY KEY);
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "CREATE TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
--echo # 1.5) CREATE TABLE for existing table must not acquire X lock
--echo # on child table
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY);
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
--echo # 1.6) CREATE TABLE which acquires lock on child table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE parent (pk INT PRIMARY KEY);
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
--echo # 1.7) CREATE TABLE which adds FK should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
CREATE TABLE parent (pk INT PRIMARY KEY);
SELECT * FROM parent;
connection con1;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquired SNRW lock on child table.
--send SELECT * FROM child;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child, parent;
--echo #
--echo # 2) CREATE TABLE ... LIKE
--echo #
--echo # 2.1) CREATE TABLE ... LIKE doesn't copy foreign keys
--echo # so it should not any locks on FK parent tables.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
LOCK TABLES parent WRITE;
connection default;
CREATE TABLE child2 LIKE child;
connection con1;
UNLOCK TABLES;
connection default;
--echo # 2.2) CREATE TABLE LIKE which adds parent to orphan child must
--echo # acquire X on child table.
SET FOREIGN_KEY_CHECKS=0;
DROP TABLES child2, parent;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE parent_source (pk INT PRIMARY KEY);
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send CREATE TABLE parent LIKE parent_source;
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "CREATE TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
--echo # 2.3) CREATE TABLE LIKE for existing table must not acquire X lock
--echo # on child table
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
CREATE TABLE IF NOT EXISTS parent LIKE parent_source;
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
--echo # 2.4) CREATE TABLE LIKE which acquires lock on child table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE IF NOT EXISTS parent LIKE parent_source;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child, parent_source;
--echo #
--echo # 3) CREATE TABLE ... SELECT
--echo #
--echo # 3.1) CREATE TABLE ... SELECT must start by acquiring SU lock on parent
--echo # table (if table doesn't exist).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE source (fk INT);
INSERT INTO source VALUES (NULL);
connection con1;
BEGIN;
SELECT * FROM source FOR UPDATE;
connection default;
SET @saved_binlog_format= @@SESSION.binlog_format;
SET @@SESSION.binlog_format=STATEMENT;
--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT fk FROM source;
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 because of row locks.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "System lock" AND
info LIKE "CREATE TABLE child%";
--source include/wait_condition.inc
--echo # Demonstrate that CREATE TABLE holds SU lock on parent
--echo # which allows DML but not DDL.
INSERT INTO parent VALUES (1);
connection con2;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent ADD COLUMN a INT;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
--echo # Resume CREATE TABLE.
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
SET SESSION binlog_format= @saved_binlog_format;
DROP TABLES child, source;
--echo # 3.2) CREATE TABLE ... SELECT should upgrade SU lock on parent to X
--echo # before commit (i.e eventually X lock should be acquired).
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@SESSION.binlog_format=STATEMENT;
--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "CREATE TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
SET SESSION binlog_format= @saved_binlog_format;
--echo # 3.3) CREATE TABLE ... SELECT for existing table should not acquire
--echo # any locks on parent table.
connection con1;
CREATE TABLE parent2 (pk INT PRIMARY KEY);
LOCK TABLE parent2 WRITE;
connection default;
SET @@SESSION.binlog_format=STATEMENT;
CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent2(pk)) SELECT NULL AS fk;
SET SESSION binlog_format= @saved_binlog_format;
connection con1;
UNLOCK TABLES;
connection default;
DROP TABLE child;
DROP TABLE parent2;
--echo # 3.4) CREATE TABLE ... SELECT which tries to acquire SU lock on parent
--echo # table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
SET @@SESSION.binlog_format=STATEMENT;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;
SET SESSION binlog_format= @saved_binlog_format;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
--echo # 3.5) CREATE TABLE ... SELECT which tries to upgrade to X lock on
--echo # parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
SET @@SESSION.binlog_format=STATEMENT;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk;
SET SESSION binlog_format= @saved_binlog_format;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE parent;
--echo # 3.6) CREATE TABLE ... SELECT which adds parent to orphan child must
--echo # acquire X on child table.
SET FOREIGN_KEY_CHECKS=0;
SET @@SESSION.binlog_format=STATEMENT;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET SESSION binlog_format= @saved_binlog_format;
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk;
connection con1;
--echo # Wait until CREATE TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "CREATE TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap CREATE TABLE
--reap
--echo # 3.7) CREATE TABLE ... SELECT for existing table must not
--echo # acquire X lock on child table
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY) SELECT 1 AS pk;
connection con1;
COMMIT;
connection default;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SET FOREIGN_KEY_CHECKS=1;
--echo # 3.8) CREATE TABLE ... SELECT which acquires lock on child table
--echo # and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
--echo # 3.9) CREATE TABLE ... SELECT which adds FK should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
CREATE TABLE parent (pk INT PRIMARY KEY);
SELECT * FROM parent;
connection con1;
SET @saved_binlog_format= @@SESSION.binlog_format;
SET @@SESSION.binlog_format=STATEMENT;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE) SELECT NULL AS fk;
SET SESSION binlog_format= @saved_binlog_format;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquired SNRW lock on child table.
--send SELECT * FROM child;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child, parent;
--echo #
--echo # 4) DROP TABLES
--echo #
--echo # 4.1) DROP TABLES must acquire X lock on parent table for FKs
--echo # when child is dropped.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send DROP TABLES child;
connection con1;
--echo # Wait until DROP TABLES is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "DROP TABLES child";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap DROP TABLES
--reap
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # 4.2) DROP TABLES which acquires lock on parent table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
DROP TABLES child;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 4.3) DROP TABLES which tries to remove parent table must acquire X
--echo # lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send DROP TABLES parent;
connection con1;
--echo # Wait until DROP TABLES is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "DROP TABLES parent";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap DROP TABLES
--error ER_FK_CANNOT_DROP_PARENT
--reap
--echo # 4.4) DROP TABLES which acquires lock on child table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
DROP TABLES parent;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 4.5) DROP TABLES which deletes child should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
DROP TABLES child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
SELECT * FROM parent;
connection con1;
DROP TABLES child;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and avoid acquiring SNRW lock on child table.
--error ER_NO_SUCH_TABLE
SELECT * FROM child;
connection default;
UNLOCK TABLES;
DROP TABLES parent;
--echo #
--echo # 5) RENAME TABLES
--echo #
--echo # 5.1) RENAME TABLES must acquire X lock on parent table for FKs
--echo # when child is renamed.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send RENAME TABLES child TO child1;
connection con1;
--echo # Wait until RENAME TABLES is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "RENAME TABLES child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap RENAME TABLES
--reap
RENAME TABLES child1 TO child;
--echo # 5.2) RENAME TABLES which acquires lock on parent table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
RENAME TABLES child TO child1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 5.3) RENAME TABLES which tries to rename parent table must acquire X
--echo # lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send RENAME TABLES parent TO parent1;
connection con1;
--echo # Wait until RENAME TABLES is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "RENAME TABLES parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap RENAME TABLES
--reap
RENAME TABLES parent1 TO parent;
--echo # 5.4) RENAME TABLES which acquires lock on child table and fails
--echo # due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
RENAME TABLES parent TO parent1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 5.5) RENAME TABLES which adds parent table for orphan child
--echo # must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send RENAME TABLES parent TO parent1;
connection con1;
--echo # Wait until RENAME TABLES is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "RENAME TABLES parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap RENAME TABLES
--reap
RENAME TABLES parent1 TO parent;
--echo # 5.6) RENAME TABLES which acquires lock on orphan child table
--echo # and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
RENAME TABLES parent TO parent1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
--echo # 5.7) RENAME TABLES on the child table should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
RENAME TABLES child TO child1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquire SNRW lock on new child table name.
--send SELECT * FROM child1;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child1";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child1, parent;
--echo #
--echo # 6) Simple ALTER TABLE ... RENAME
--echo #
--echo # 6.1) ALTER TABLE ... RENAME must acquire X lock on parent table
--echo # for FKs when child is renamed.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child RENAME TO child1;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES child1 TO child;
--echo # 6.2) ALTER TABLE ... RENAME which acquires lock on parent table and
--echo # fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child RENAME TO child1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 6.3) ALTER TABLE ... RENAME which tries to rename parent table must
--echo # acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 6.4) ALTER TABLE ... RENAME which acquires lock on child table and
--echo # fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 6.5) ALTER TABLE ... RENAME which adds parent table for orphan child
--echo # must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 6.6) ALTER TABLE ... RENAME which acquires lock on orphan child table
--echo # and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
--echo # 6.7) ALTER TABLE ... RENAME on the child table should invalidate
--echo # entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child RENAME TO child1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquire SNRW lock on new child table name.
--send SELECT * FROM child1;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child1";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child1, parent;
--echo #
--echo # 7) ALTER TABLE ... INPLACE
--echo #
--echo # 7.1) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE must start by
--echo # acquiring SU lock on parent table.
--echo #
--echo # This test uses debug_sync feature so resides in foreign_key_debug.test
--echo #
--echo # 7.2) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should upgrade SU
--echo # lock on parent to X before commit (i.e eventually X lock should
--echo # be acquired).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET FOREIGN_KEY_CHECKS=0;
--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE child DROP FOREIGN KEY fk;
--echo # 7.3) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to
--echo # acquire SU lock on parent table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
--echo # 7.4) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to
--echo # upgrade to X lock on parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
SET FOREIGN_KEY_CHECKS=0;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 7.5) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should invalidate
--echo # entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquired SNRW lock on child table.
--send SELECT * FROM child;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
--echo # 7.6) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should acquire
--echo # lock on parent to X before commit.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE;
--echo # 7.7) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE which tries to
--echo # upgrade to X lock on parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 7.8) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and avoid acquiring SNRW lock on child table.
SELECT * FROM child;
connection default;
UNLOCK TABLES;
--echo # 7.9) ALTER TABLE ... INPLACE which changes parent key must acquire X
--echo # lock on child table.
DROP TABLES child, parent;
CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
--echo # 7.10) ALTER TABLE ... INPLACE which changes parent key, so tries to
--echo # acquire X lock on child table, but fails due to timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 7.11) ALTER TABLE ... RENAME ... INPLACE must acquire X lock on
--echo # parent table for FKs when child is renamed.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES child1 TO child;
--echo # 7.12) ALTER TABLE ... RENAME ... INPLACE which acquires lock on
--echo # parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 7.13) ALTER TABLE ... RENAME ... INPLACE which tries to rename parent
--echo # table must acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 7.14) ALTER TABLE ... RENAME ... INPLACE which acquires lock on child
--echo # table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 7.15) ALTER TABLE ... RENAME ... INPLACE which adds parent table for
--echo # orphan child must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=INPLACE;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 7.16) ALTER TABLE ... RENAME ... INPLACE which acquires lock on
--echo # orphan child table and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=INPLACE;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
--echo # 7.17) ALTER TABLE ... RENAME ... INPLACE on the child table should
--echo # invalidate entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquire SNRW lock on new child table name.
--send SELECT * FROM child1;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child1";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child1, parent;
--echo #
--echo # 8) ALTER TABLE ... COPY
--echo #
--echo # 8.1) ALTER TABLE ... ADD FOREIGN KEY ... COPY must start by
--echo # acquiring SU lock on parent table.
--echo # 8.1') ALTER TABLE ... ADD FOREIGN KEY ... COPY due to workaround
--echo # must upgrade SU lock on parent table SRO lock.
--echo #
--echo # These tests use debug_sync feature so reside in foreign_key_debug.test
--echo #
--echo # 8.2) ALTER TABLE ... ADD FOREIGN KEY ... COPY should upgrade SU (SRO)
--echo # lock on parent to X before commit (i.e eventually X lock should
--echo # be acquired).
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
ALTER TABLE child DROP FOREIGN KEY fk;
--echo # 8.3) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to
--echo # acquire SU lock on parent table and fails due to lock timeout.
connection con1;
LOCK TABLE parent WRITE;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
UNLOCK TABLES;
--echo # 8.3') ALTER TABLE ... ADD FOREIGN KEY ... COPY which due to workaround
--echo # tries to upgrade SU lock on parent table to SRO and fails due to
--echo # lock timeout.
connection con1;
BEGIN;
DELETE FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.4) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to
--echo # upgrade to X lock on parent table and fails due to lock timeout.
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.5) ALTER TABLE ... ADD FOREIGN KEY ... COPY should invalidate
--echo # entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquired SNRW lock on child table.
--send SELECT * FROM child;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
--echo # 8.6) ALTER TABLE ... DROP FOREIGN KEY ... COPY should acquire
--echo # lock on parent to X before commit.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE;
--echo # 8.7) ALTER TABLE ... DROP FOREIGN KEY ... COPY which tries to
--echo # upgrade to X lock on parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.8) ALTER TABLE ... DROP FOREIGN KEY ... COPY should invalidate entries
--echo # in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and avoid acquiring SNRW lock on child table.
SELECT * FROM child;
connection default;
UNLOCK TABLES;
--echo # 8.9) ALTER TABLE ... COPY which changes parent key must acquire X
--echo # lock on child table.
DROP TABLES child, parent;
CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
--echo # 8.10) ALTER TABLE ... COPY which changes parent key, so tries to
--echo # acquire X lock on child table, but fails due to timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.11) ALTER TABLE ... RENAME ... COPY must acquire X lock on
--echo # parent table for FKs when child is renamed.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
--send ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE child%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES child1 TO child;
--echo # 8.12) ALTER TABLE ... RENAME ... COPY which acquires lock on
--echo # parent table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM parent;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.13) ALTER TABLE ... RENAME ... COPY which tries to rename parent
--echo # table must acquire X lock on child table.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 8.14) ALTER TABLE ... RENAME ... COPY which acquires lock on child
--echo # table and fails due to lock timeout.
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
--echo # 8.15) ALTER TABLE ... RENAME ... COPY which adds parent table for
--echo # orphan child must acquire X lock on this child table.
connection default;
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=COPY;
connection con1;
--echo # Wait until ALTER TABLE is blocked by con1 and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "ALTER TABLE parent%";
--source include/wait_condition.inc
COMMIT;
connection default;
--echo # Reap ALTER TABLE
--reap
RENAME TABLES parent1 TO parent;
--echo # 8.16) ALTER TABLE ... RENAME ... COPY which acquires lock on
--echo # orphan child table and fails due to timeout.
DROP TABLE child;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk));
SET FOREIGN_KEY_CHECKS=1;
connection con1;
BEGIN;
SELECT * FROM child;
connection default;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=COPY;
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection con1;
COMMIT;
connection default;
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE);
--echo # 8.17) ALTER TABLE ... RENAME ... COPY on the child table should
--echo # invalidate entries in TC/TDC and DD caches for the parent table.
SELECT * FROM parent;
connection con1;
ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY;
connection default;
LOCK TABLE parent WRITE;
connection con1;
--echo # The above LOCK TABLE should have noticed new table definition
--echo # and acquire SNRW lock on new child table name.
--send SELECT * FROM child1;
connection default;
--echo # Wait until SELECT is blocked by default connection and then resume it.
let $wait_condition=
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE state = "Waiting for table metadata lock" AND
info LIKE "SELECT * FROM child1";
--source include/wait_condition.inc
UNLOCK TABLES;
connection con1;
--echo # Reap SELECT
--reap
connection default;
DROP TABLES child1, parent;
connection con1;
disconnect con1;
--source include/wait_until_disconnected.inc
connection con2;
disconnect con2;
--source include/wait_until_disconnected.inc
connection default;
--disable_connect_log
--echo #
--echo # Validation of FK and referred column names.
--echo #
--echo # Too long constraint name.
--error ER_TOO_LONG_IDENT
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
CONSTRAINT xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx
FOREIGN KEY (fk_i) REFERENCES x(x));
--echo # Too long column name.
--error ER_WRONG_COLUMN_NAME
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES x(xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx));
--echo # Column name having trailing space.
--error ER_WRONG_COLUMN_NAME
CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES x(`x `));
--echo #
--echo # Test DROP of table with FKs under LOCK TABLES.
--echo #
CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, fk_i INTEGER,
UNIQUE KEY parent_i_key(i),
FOREIGN KEY (fk_i) REFERENCES parent(i));
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER,
FOREIGN KEY (fk_i) REFERENCES parent(i));
SET @@session.foreign_key_checks= 1;
--echo # Drop only parent with FKC == 0.
LOCK TABLE parent WRITE;
--error ER_FK_CANNOT_DROP_PARENT
DROP TABLES parent;
UNLOCK TABLE;
SET @@session.foreign_key_checks= 0;
--echo # Drop only parent.
LOCK TABLE parent WRITE;
DROP TABLES parent;
UNLOCK TABLE;
SET @@session.foreign_key_checks= 1;
CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
UNIQUE KEY parent_i_key(i));
--echo # Drop both child and parent.
LOCK TABLES child WRITE, parent WRITE;
DROP TABLES child, parent;
UNLOCK TABLES;
SET @@session.foreign_key_checks= DEFAULT;
--echo #
--echo # Test ALTER TABLE ... ADD FOREIGN KEY under LOCK TABLES.
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);
LOCK TABLES child WRITE;
--error ER_TABLE_NOT_LOCKED
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
UNLOCK TABLES;
--echo # With parent table locked for read it should be possible to add FK.
LOCK TABLES child WRITE, parent READ;
ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
UNLOCK TABLES;
--echo #
--echo # Test ALTER TABLE ... RENAME with FKs under LOCK TABLES.
--echo #
--echo # Bug 26647340 "LIFT LIMITATION ON ALTER TABLE RENAME + TABLES WITH
--echo # FOREIGN KEYS + LOCK TABLES".
--echo #
--echo # Renaming of tables participating in FKs is allowed.
LOCK TABLES child WRITE;
ALTER TABLE child RENAME TO child1;
UNLOCK TABLES;
LOCK TABLES parent WRITE;
ALTER TABLE parent RENAME TO parent1;
UNLOCK TABLES;
--echo # Check that it doesn't break FK invariants for LOCK TABLES.
LOCK TABLES child1 WRITE, parent1 WRITE;
ALTER TABLE child1 RENAME TO child;
INSERT INTO child VALUES (NULL);
DELETE FROM parent1;
UNLOCK TABLES;
LOCK TABLES child WRITE, parent1 WRITE;
ALTER TABLE parent1 RENAME TO parent;
INSERT INTO child VALUES (NULL);
UNLOCK TABLES;
--echo # It is also allowed to add foreign key along with rename.
ALTER TABLE child DROP FOREIGN KEY fk;
LOCK TABLES child WRITE, parent WRITE;
ALTER TABLE child RENAME TO child1, ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk);
--echo # And FK invariants for LOCK TABLES are preserved too.
INSERT INTO child1 VALUES (NULL);
DELETE FROM parent;
UNLOCK TABLES;
DROP TABLE child1;
--echo # We also allow renames which add parent for previously orphan FKs.
--echo # Provided that we have write lock on these children.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent1(pk) ON DELETE CASCADE);
SET FOREIGN_KEY_CHECKS=1;
LOCK TABLE parent WRITE;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
ALTER TABLE parent RENAME TO parent1;
UNLOCK TABLES;
LOCK TABLE parent WRITE, child READ;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
ALTER TABLE parent RENAME TO parent1;
UNLOCK TABLES;
LOCK TABLE parent WRITE, child WRITE;
ALTER TABLE parent RENAME TO parent1;
--echo # Invariants should be fine
INSERT INTO child VALUES (NULL);
DELETE FROM parent1;
UNLOCK TABLES;
DROP TABLES child, parent1;
--echo #
--echo # Bug #25722221 "RENAME COLUMN DID NOT UPDATE FOREIGN_KEY_COLUMN_USAGE
--echo # FOR FK CONSTRAINT".
--echo # Bug #26659110 "LIFT LIMITATION ON RENAMING PARENT COLUMNS WHICH ARE
--echo # REFERENCED BY FOREIGN KEYS".
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY (fk) REFERENCES t1 (pk));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 CHANGE pk id INT;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
--echo # Renaming of parent columns using COPY algorithm is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN pk TO id;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
--echo # Renaming of parent columns using COPY algorithm is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 CHANGE pk id INT;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
--echo # Renaming of parent columns using COPY algorithm is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN pk TO id;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
--echo # Renaming of parent columns using COPY algorithm is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
DROP TABLES t2, t1;
--echo # Coverage for cases with multiple foreign keys.
CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, fk1 INT, fk2 INT,
UNIQUE (u1), UNIQUE (u2),
FOREIGN KEY (fk1) REFERENCES t1 (u1),
FOREIGN KEY (fk2) REFERENCES t1 (u2));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN u1 TO u3;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t1'
ORDER BY constraint_name;
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, UNIQUE (u1), UNIQUE (u2));
CREATE TABLE t2 (fk1 INT, fk2 INT,
FOREIGN KEY (fk1) REFERENCES t1 (u1),
FOREIGN KEY (fk2) REFERENCES t1 (u2));
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN u1 TO u3;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5;
SELECT constraint_name, table_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema='test' AND table_name='t2'
ORDER BY constraint_name;
DROP TABLES t2, t1;
--echo #
--echo # WL#6049, bug#26654674 "TABLE_CACHE_MANAGER::FREE_TABLE(THD*,
--echo # ENUM_TDC_REMOVE_TABLE_TYPE, TABLE_SHARE*)".
--echo #
CREATE TABLE t1 (u INT NOT NULL, UNIQUE u(u));
--echo # First, check that we correctly handle open HANDLERs during
--echo # parent invalidation.
HANDLER t1 OPEN;
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u));
HANDLER t1 CLOSE;
HANDLER t1 OPEN AS a;
HANDLER t1 OPEN AS b;
CREATE TABLE t3 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u));
HANDLER a CLOSE;
HANDLER b CLOSE;
--echo # Then, check that we correctly handle HANDLERs on child table
--echo # during parent definiton change.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
ALTER TABLE t1 RENAME KEY u TO u1;
HANDLER t2 CLOSE;
HANDLER a CLOSE;
HANDLER b CLOSE;
--echo # Now, let us check what happens when parent is renamed.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
ALTER TABLE t1 RENAME TO t4;
HANDLER t2 CLOSE;
HANDLER a CLOSE;
HANDLER b CLOSE;
--echo # Finally, check what happens when parent table is deleted.
--echo # Do clean-up as side-effect.
HANDLER t2 OPEN;
HANDLER t3 OPEN AS a;
HANDLER t3 OPEN AS b;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t4;
DROP TABLES t2, t3;
SET FOREIGN_KEY_CHECKS=1;
--echo #
--echo # Additional test coverage for foreign keys and prepared statement
--echo # validation.
--echo #
CREATE TABLE t1 (i INT PRIMARY KEY);
CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE);
CREATE TABLE t3 (k INT);
DELIMITER |;
CREATE TRIGGER bi_t3 BEFORE INSERT ON t3 FOR EACH ROW
BEGIN
IF @a = 1234567890 THEN
DELETE FROM t1;
END IF;
END|
DELIMITER ;|
PREPARE stmt FROM 'INSERT INTO t3 VALUES (1)';
EXECUTE stmt;
DROP TABLES t2, t1;
--echo # Statement should succeed even though we won't be able check
--echo # prelocked set element for child table.
EXECUTE stmt;
CREATE TABLE t1 (i INT PRIMARY KEY);
CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE);
EXECUTE stmt;
DROP TABLES t2, t1;
CREATE VIEW t2 AS SELECT 1 AS j;
--echo # Again statement should succeed, even though we have view instead
--echo # of child table.
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t3;
DROP VIEW t2;
--echo #
--echo # Test for bug #27041477 "ASSERTION `HAS_PRELOCKING_LIST ||
--echo # THD->MDL_CONTEXT.OWNS_EQUAL_OR_STRONGER_LOCK".
--echo #
CREATE TABLE t0 (i INT);
CREATE TRIGGER t0_bi BEFORE INSERT ON t0 FOR EACH ROW DELETE FROM t1;
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk) ON UPDATE SET NULL);
LOCK TABLE t1 READ;
--error ER_TABLE_NOT_LOCKED
DELETE FROM t1;
UNLOCK TABLES;
LOCK TABLES t0 WRITE;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
UPDATE t1 SET pk = 10;
UNLOCK TABLES;
DROP TABLES t2, t1, t0;
--echo #
--echo # Additional coverage for bug #25915132 "INPLACE ALTER TABLE WITH
--echo # FOREIGN KEYS CAUSES TABLE DEFINITION MISMATCH".
--echo #
--echo #
--echo # Handling of foreign key names during various RENAME variants.
--echo # We check that table definition is updated correctly and that
--echo # asserts about MDL on foreign key names do not fail.
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT,
CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1 (pk),
CONSTRAINT t2_ibfk_1 FOREIGN KEY (fk2) REFERENCES t1 (pk));
ALTER TABLE t2 ADD FOREIGN KEY (fk3) REFERENCES t1 (pk);
CREATE SCHEMA mysqltest;
SHOW CREATE TABLE t2;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # Simple RENAME TABLE
RENAME TABLE t2 TO t3;
SHOW CREATE TABLE t3;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # Two cross database RENAME TABLE variants
RENAME TABLE t3 TO mysqltest.t3;
SHOW CREATE TABLE mysqltest.t3;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
RENAME TABLE mysqltest.t3 TO t4;
SHOW CREATE TABLE t4;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # Simple ALTER TABLE RENAME variants.
ALTER TABLE t4 RENAME TO t5;
SHOW CREATE TABLE t5;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
ALTER TABLE t5 RENAME TO mysqltest.t5;
SHOW CREATE TABLE mysqltest.t5;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
ALTER TABLE mysqltest.t5 RENAME TO t6;
SHOW CREATE TABLE t6;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # ALTER TABLE INPLACE with RENAME clause.
ALTER TABLE t6 ADD COLUMN i INT, RENAME TO t7, ALGORITHM=INPLACE;
SHOW CREATE TABLE t7;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
ALTER TABLE t7 ADD COLUMN j INT, RENAME TO mysqltest.t7, ALGORITHM=INPLACE;
SHOW CREATE TABLE mysqltest.t7;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
ALTER TABLE mysqltest.t7 ADD COLUMN k INT, RENAME TO t8, ALGORITHM=INPLACE;
SHOW CREATE TABLE t8;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # ALTER TABLE COPY with RENAME clause.
ALTER TABLE t8 ADD COLUMN l INT, RENAME TO t9, ALGORITHM=COPY;
SHOW CREATE TABLE t9;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
ALTER TABLE t9 ADD COLUMN m INT, RENAME TO mysqltest.t9, ALGORITHM=COPY;
SHOW CREATE TABLE mysqltest.t9;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
ALTER TABLE mysqltest.t9 ADD COLUMN n INT, RENAME TO t10, ALGORITHM=COPY;
SHOW CREATE TABLE t10;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
--echo # ALTER TABLE INPLACE + ADD FOREIGN KEY with RENAME clause.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t10 ADD FOREIGN KEY (i) REFERENCES t1 (pk),
ADD CONSTRAINT t10_ibfk_4 FOREIGN KEY (j) REFERENCES t1 (pk),
RENAME TO t11, ALGORITHM=INPLACE;
SHOW CREATE TABLE t11;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
ALTER TABLE t11 ADD FOREIGN KEY (k) REFERENCES test.t1 (pk),
ADD CONSTRAINT t11_ibfk_6 FOREIGN KEY (l) REFERENCES test.t1 (pk),
RENAME TO mysqltest.t11, ALGORITHM=INPLACE;
SHOW CREATE TABLE mysqltest.t11;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
ALTER TABLE mysqltest.t11 ADD FOREIGN KEY (m) REFERENCES test.t1 (pk),
ADD CONSTRAINT t12_ibfk_8 FOREIGN KEY (n) REFERENCES test.t1 (pk),
RENAME TO t12, ALGORITHM=INPLACE;
SHOW CREATE TABLE t12;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
SET FOREIGN_KEY_CHECKS=1;
--echo # ALTER TABLE COPY + ADD FOREIGN KEY with RENAME clause.
ALTER TABLE t12 ADD COLUMN o INT, ADD COLUMN p INT,
ADD FOREIGN KEY (o) REFERENCES t1 (pk),
ADD CONSTRAINT t12_ibfk_10 FOREIGN KEY (p) REFERENCES t1 (pk),
RENAME TO t13, ALGORITHM=COPY;
SHOW CREATE TABLE t13;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
ALTER TABLE t13 ADD COLUMN q INT, ADD COLUMN r INT,
ADD FOREIGN KEY (q) REFERENCES test.t1 (pk),
ADD CONSTRAINT t13_ibfk_12 FOREIGN KEY (r) REFERENCES test.t1 (pk),
RENAME TO mysqltest.t13, ALGORITHM=COPY;
SHOW CREATE TABLE mysqltest.t13;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest';
ALTER TABLE mysqltest.t13 ADD COLUMN s INT, ADD COLUMN t INT,
ADD FOREIGN KEY (s) REFERENCES test.t1 (pk),
ADD CONSTRAINT t13_ibfk_14 FOREIGN KEY (t) REFERENCES test.t1 (pk),
RENAME TO t14, ALGORITHM=COPY;
SHOW CREATE TABLE t14;
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test';
DROP TABLE t14;
--echo #
--echo # Detection of duplicate foreign key names by various forms of ALTER
--echo # TABLE.
--echo #
CREATE TABLE t2 (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk));
CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT, u INT);
INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 1);
--echo #
--echo # ALTER TABLE INPLACE
--echo #
SET FOREIGN_KEY_CHECKS=0;
--echo # Duplicate FK name should be detected before we start addition
--echo # of unique key and report its violation.
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=INPLACE;
--echo # Even for generated names.
CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk));
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=INPLACE;
--echo # There should not be conflicting foreign keys before main phase
--echo # of ALTER INPLACE even if at the end RENAME clause will remove
--echo # conflict.
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=INPLACE;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t5,
ALGORITHM=INPLACE;
--echo # Now let us check that we detect potential conflicts resulting
--echo # from application of RENAME clause, before ALTER INPLACE main
--echo # phase as well.
CREATE TABLE mysqltest.t5 (fk INT,
CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk));
CREATE TABLE t6 (fk INT, CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk));
CREATE TABLE mysqltest.t6 (fk INT,
CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk));
DROP TABLE t4;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=INPLACE;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t8,
ALGORITHM=INPLACE;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t8,
ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
--echo #
--echo # ALTER TABLE COPY
--echo #
--echo # Duplicate FK name should be detected before we start copying
--echo # of rows and report unique/FK constraint violation.
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=COPY;
--echo # Even for generated names.
CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk));
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), ALGORITHM=COPY;
--echo # Unlike in INPLACE case, COPY algorithm creates FKs right
--echo # away in schema targeted by RENAME clause. So it doesn't
--echo # matter if constraint with the same name exists in the
--echo # table's original schema.
SET FOREIGN_KEY_CHECKS=0;
--error ER_DUP_ENTRY
ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=COPY;
SET FOREIGN_KEY_CHECKS=1;
--echo # Updating of generated names still happens at the end of ALTER,
--echo # so there should not be conflicting foreign keys for generated
--echo # names for both old and new table name.
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t5,
ALGORITHM=COPY;
--echo # Check that we detect potential conflicts resulting from
--echo # application of RENAME clause early.
DROP TABLE t4;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t3,
ALGORITHM=COPY;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO t8,
ALGORITHM=COPY;
--error ER_FK_DUP_NAME
ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk),
ADD UNIQUE KEY (u), RENAME TO mysqltest.t8,
ALGORITHM=COPY;
DROP SCHEMA mysqltest;
DROP TABLES t6, t3, t2, t1;
--echo #
--echo # Initial version of the fix triggered asserts in the below statements.
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE T2 (fk INT);
ALTER TABLE T2 ADD FOREIGN KEY (fk) REFERENCES t1 (pk);
RENAME TABLE T2 TO T3;
DROP TABLES T3, t1;
--echo #
--echo # Bug #27821060 "NEWDD FK: DROP TABLES/DATABASE SHOULD CHECK FOR FKS".
--echo #
--echo #
--echo # 1) Attempt to DROP TABLE which serves as parent in FK without dropping
--echo # child should fail with nice error message.
CREATE TABLE t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk));
--error ER_FK_CANNOT_DROP_PARENT
DROP TABLE t1;
--echo #
--echo # 2) However, same should be allowed in FOREIGN_KEY_CHECKS=0 mode.
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=1;
--echo #
--echo # 3) Dropping of parent table along with child table should be allowed.
--echo # Even if parent precedes child in table list.
CREATE TABLE t1 (pk INT PRIMARY KEY);
DROP TABLES t1, t2;
--echo #
--echo # 4) Even if FKs form circular dependencies.
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT);
CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY(fk) REFERENCES t1 (pk));
ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk);
DROP TABLES t1, t2;
--echo #
--echo # 5) Attempt to DROP SCHEMA which will remove parent without
--echo # removing child should fail with nice error message.
CREATE SCHEMA mysqltest;
CREATE TABLE mysqltest.t1 (pk INT PRIMARY KEY);
CREATE TABLE t2 (fk INT, FOREIGN KEY(fk) REFERENCES mysqltest.t1 (pk));
--error ER_FK_CANNOT_DROP_PARENT
DROP SCHEMA mysqltest;
--echo #
--echo # 6) But the same should be allowed in FOREIGN_KEY_CHECKS=0 mode.
SET FOREIGN_KEY_CHECKS=0;
DROP SCHEMA mysqltest;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLE t2;
--echo #
--echo # 7) Also dropping schema which drops both parent and child
--echo # should be OK.
CREATE SCHEMA mysqltest;
USE mysqltest;
CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT);
CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT,
FOREIGN KEY(fk) REFERENCES t1 (pk));
ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk);
USE test;
DROP SCHEMA mysqltest;
--echo #
--echo # Bug#27506922 "DROPPING OF PARENT KEY FOR FOREIGN KEY IS ALLOWED".
--echo #
--echo #
--echo # 1) CREATE TABLE with FK and no parent key.
--echo #
SET restrict_fk_on_non_standard_key=OFF;
CREATE TABLE parent(pk INT PRIMARY KEY, a INT);
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT,
FOREIGN KEY (fk) REFERENCES self(a));
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT,
FOREIGN KEY (fk) REFERENCES self(a));
--echo # Missing parent table case.
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_CANNOT_OPEN_PARENT,
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk));
--echo # We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode.
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk));
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE child;
--echo #
--echo # 2) ALTER TABLE which adds FK without parent key.
--echo #
CREATE TABLE child (fk INT, fk2 INT);
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT);
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE;
--echo # Missing parent table case.
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_CANNOT_OPEN_PARENT,
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY;
--error ER_FK_CANNOT_OPEN_PARENT,
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE;
--echo # We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode.
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY;
ALTER TABLE child ADD FOREIGN KEY (fk2) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE child, self, parent;
--echo #
--echo # 3) ALTER TABLE which drops parent key.
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY, u INT NOT NULL, UNIQUE(u));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(u));
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY u, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY u, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE self (pk INT PRIMARY KEY, u INT NOT NULL, fk INT, UNIQUE(u),
FOREIGN KEY (fk) REFERENCES self(u));
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY u, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY u, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
--echo # Check case which requires additional handling during error-reporting.
--echo # Attempt to drop non-unique parent key.
ALTER TABLE parent DROP KEY u, ADD KEY nu(u);
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE self DROP KEY u, ADD KEY nu(u);
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY nu, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY nu, ALGORITHM=COPY;
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLES self, child, parent;
--echo #
--echo # 4) CREATE, RENAME and ALTER TABLE RENAME which create new
--echo # parent for previously orphan child table.
--echo #
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a));
SET FOREIGN_KEY_CHECKS = 1;
# We get more generic error message here because SE check kicks in
# before SQL-layer check.
--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE parent1 (pk INT PRIMARY KEY, a INT);
--error ER_ERROR_ON_RENAME
RENAME TABLE parent1 TO parent;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
RENAME TABLE parent1 TO parent;
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 RENAME TO parent;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 RENAME TO parent;
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 RENAME TO parent;
SET FOREIGN_KEY_CHECKS = 1;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=COPY;
SET FOREIGN_KEY_CHECKS = 0;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE parent1 RENAME TO parent;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE parent1, child;
--echo #
--echo # 5) Special case. ALTER TABLE which replaces parent key with another
--echo # one due to new key creation. Old key is generated so it is
--echo # automagically replaced with a new explicit key or more universal
--echo # generated key.
--echo #
CREATE TABLE grandparent (pk INT PRIMARY KEY);
CREATE TABLE parent (pkfk INT, FOREIGN KEY (pkfk) REFERENCES grandparent(pk));
SHOW CREATE TABLE parent;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk));
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
ALTER TABLE parent ADD UNIQUE KEY u (pkfk);
SHOW CREATE TABLE parent;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
DROP TABLE child, parent;
CREATE TABLE self (fk INT, pkfk INT,
FOREIGN KEY (fk) REFERENCES self (pkfk),
FOREIGN KEY (pkfk) REFERENCES grandparent(pk));
SHOW CREATE TABLE self;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
ORDER BY referenced_table_name;
ALTER TABLE self ADD UNIQUE KEY u (pkfk);
SHOW CREATE TABLE self;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
ORDER BY referenced_table_name;
DROP TABLE self, grandparent;
CREATE TABLE grandparent1 (pk INT PRIMARY KEY);
CREATE TABLE grandparent2 (pk1 INT , pk2 INT, PRIMARY KEY(pk1, pk2));
CREATE TABLE parent (pkfk1 INT, pkfk2 INT, FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk));
SHOW CREATE TABLE parent;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk1));
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
ALTER TABLE parent ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2);
SHOW CREATE TABLE parent;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
DROP TABLE child, parent;
CREATE TABLE self (fk INT, pkfk1 INT, pkfk2 INT,
FOREIGN KEY (fk) REFERENCES self (pkfk1),
FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk));
SHOW CREATE TABLE self;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
ORDER BY referenced_table_name;
ALTER TABLE self ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2);
SHOW CREATE TABLE self;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'self'
ORDER BY referenced_table_name;
DROP TABLE self, grandparent1, grandparent2;
SET restrict_fk_on_non_standard_key=ON;
--echo #
--echo # Bug#25722927 "NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT"
--echo #
--echo #
--echo # 1) Check how missing/dropped referencing and referenced columns
--echo # are handled.
--echo #
--echo #
--echo # 1.a) Missing referencing column. This problem is detected
--echo # during generated supported index processing.
CREATE TABLE parent (pk INT PRIMARY KEY, j INT);
--error ER_KEY_COLUMN_DOES_NOT_EXITS
CREATE TABLE child (fk INT, FOREIGN KEY (nocol) REFERENCES parent(pk));
--error ER_KEY_COLUMN_DOES_NOT_EXITS
CREATE TABLE self (pk INT PRIMARY KEY, FOREIGN KEY (nocol) REFERENCES self(pk));
CREATE TABLE child (fk INT, j INT);
CREATE TABLE self (pk INT PRIMARY KEY, fk INT);
--error ER_KEY_COLUMN_DOES_NOT_EXITS
ALTER TABLE child ADD FOREIGN KEY (nocol) REFERENCES parent(pk);
--error ER_KEY_COLUMN_DOES_NOT_EXITS
ALTER TABLE self ADD FOREIGN KEY (nocol) REFERENCES self(pk);
--echo #
--echo # 1.b) Dropped referencing column.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE child DROP COLUMN fk;
--echo # Adding column with the same name at the same time should not help.
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE child DROP COLUMN fk, ADD COLUMN fk INT;
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE self DROP COLUMN fk;
--error ER_FK_COLUMN_CANNOT_DROP
ALTER TABLE self DROP COLUMN fk, ADD COLUMN fk INT;
--echo #
--echo # 1.c) Missing referenced column.
DROP TABLE child;
--error ER_FK_NO_COLUMN_PARENT
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol));
DROP TABLE self;
--error ER_FK_NO_COLUMN_PARENT
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(nocol));
CREATE TABLE child (fk INT);
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(nocol);
CREATE TABLE self (pk INT PRIMARY KEY, fk INT);
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(nocol);
--echo #
--echo # 1.d) Dropped referenced column.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
ALTER TABLE parent DROP COLUMN pk;
--echo # Adding column with the same name at the same time should not help.
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
ALTER TABLE parent DROP COLUMN pk, ADD COLUMN pk INT;
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
ALTER TABLE self DROP COLUMN pk;
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
ALTER TABLE self DROP COLUMN pk, ADD COLUMN pk INT;
--echo #
--echo # 1.e) Special case. Attempt to create parent for orphan
--echo # foreign key which doesn't have matching column.
DROP TABLES child, parent;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol));
--error ER_FK_NO_COLUMN_PARENT
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE parent0 (pk INT PRIMARY KEY);
--error ER_FK_NO_COLUMN_PARENT
RENAME TABLE parent0 TO parent;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE parent0 RENAME TO parent;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent0, self;
--echo #
--echo # 2) Handling of virtual columns in referencing and referenced
--echo # columns lists.
--echo #
--echo #
--echo # 2.a) Virtual columns in referencing columns list are not
--echo # supported.
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL,
FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) VIRTUAL,
FOREIGN KEY (fk) REFERENCES self(pk));
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) VIRTUAL);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
--echo #
--echo # 2.b) One should not be able to get virtual column in referencing
--echo # columns list by ALTERing column.
DROP TABLE child, self;
CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) STORED,
FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE child MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
CREATE TABLE self (pk INT PRIMARY KEY, base INT,
fk INT GENERATED ALWAYS AS (base+1) STORED,
FOREIGN KEY (fk) REFERENCES self(pk));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE self MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
--echo #
--echo # 2.c) Virtual columns in referenced columns list are not
--echo # supported.
DROP TABLE child, parent, self;
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
CREATE TABLE child (fk INT);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT,
UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk));
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT,
UNIQUE KEY(pk));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
--echo #
--echo # 2.d) Again, one should not be able to get referenced virtual
--echo # column by ALTERing it.
DROP TABLE child, parent, self;
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, UNIQUE KEY(pk));
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE parent MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, fk INT,
UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE self MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL;
--echo #
--echo # 2.e) Special case. Attempt to create parent for orphan
--echo # foreign key which has virtual column.
DROP TABLES child, parent, self;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
CREATE TABLE parent0 (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
RENAME TABLE parent0 TO parent;
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE parent0 RENAME TO parent;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent0;
--echo #
--echo # 3) Check how missing/dropped supporting index on referencing table
--echo # is handled.
--echo #
--echo #
--echo # 3.a) Normally, generated supporting index is automatically added
--echo # along with foreign key, so it can be missing only in some rare
--echo # corner case, for example, when generated index is automatically
--echo # converted to spatial index.
CREATE TABLE parent (pk POINT SRID 0 NOT NULL, KEY(pk));
--error ER_FK_NO_INDEX_CHILD
CREATE TABLE child (fk POINT SRID 0 NOT NULL, FOREIGN KEY(fk) REFERENCES parent(pk));
CREATE TABLE child (fk POINT SRID 0 NOT NULL);
--error ER_FK_NO_INDEX_CHILD
ALTER TABLE child ADD FOREIGN KEY(fk) REFERENCES parent(pk);
--error ER_FK_NO_INDEX_CHILD
CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL,
KEY(pk), FOREIGN KEY(fk) REFERENCES self(pk));
CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL, KEY(pk));
--error ER_FK_NO_INDEX_CHILD
ALTER TABLE self ADD FOREIGN KEY(fk) REFERENCES self(pk);
--echo #
--echo # 3.b) Attempt to drop supporting index should be prohibited.
DROP TABLES self, child, parent;
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent(pk));
--error ER_DROP_INDEX_FK
ALTER TABLE child DROP KEY fk;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY(fk) REFERENCES self(pk));
--error ER_DROP_INDEX_FK
ALTER TABLE self DROP KEY fk;
--echo # However, we allow automatic dropping of generated index when new
--echo # explicit supporting index is added.
ALTER TABLE child ADD KEY fk_s(fk);
SHOW CREATE TABLE child;
ALTER TABLE self ADD KEY fk_s(fk);
SHOW CREATE TABLE self;
--echo # Also dropping supporting index and providing replacement is fine.
ALTER TABLE child DROP KEY fk_s, ADD COLUMN j INT, ADD KEY (fk, j);
ALTER TABLE self DROP KEY fk_s, ADD COLUMN j INT, ADD KEY(fk, j);
DROP TABLES self, child, parent;
--echo #
--echo # 4) Check how foreign keys involving partitioned tables are handled.
--echo #
--echo #
--echo # 4.a) Creation of partitioned child table, addition of foreign key to
--echo # partitioned table and attempt to partition child table in a
--echo # foreign key should all lead to errors (since InnoDB doesn't
--echo # support foreign keys involving partitioned tables yet).
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FOREIGN_KEY_ON_PARTITIONED
CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk))
PARTITION BY KEY (pk) PARTITIONS 20;
CREATE TABLE child (pk INT PRIMARY KEY, fk INT) PARTITION BY KEY (pk) PARTITIONS 20;
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
DROP TABLE child;
CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE child PARTITION BY KEY (pk) PARTITIONS 20;
--echo #
--echo # 4.b) Attempt to partition parent table in a foreign key,
--echo # attempt to create table with foreign key referencing
--echo # partitioned table and addition of foreign key with
--echo # partitioned parent should all lead to errors as well.
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE parent PARTITION BY KEY (pk) PARTITIONS 20;
DROP TABLES child, parent;
CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
--error ER_FOREIGN_KEY_ON_PARTITIONED
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
CREATE TABLE child (fk INT);
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
DROP TABLES child, parent;
--echo #
--echo # 4.c) Addition of partitioned parent for previously orphan foreign key
--echo # should result in error as well.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE orphan (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
--error ER_FOREIGN_KEY_ON_PARTITIONED
CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
CREATE TABLE parent0 (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20;
--error ER_FOREIGN_KEY_ON_PARTITIONED
RENAME TABLE parent0 TO parent;
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE parent0 RENAME TO parent;
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=COPY;
--error ER_FOREIGN_KEY_ON_PARTITIONED
ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=INPLACE;
DROP TABLES orphan, parent0;
--echo #
--echo # 4.d) ALTER TABLE which ADD/DROP FOREIGN KEY and change table's
--echo # partitioning status at the same time.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (pk INT PRIMARY KEY, fk INT,
CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Dropping foreign key and adding partitioning is OK.
ALTER TABLE child DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20;
--echo # Adding foreign key and removing partitioning is OK.
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) REMOVE PARTITIONING;
DROP TABLES child, parent;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT,
CONSTRAINT c FOREIGN KEY (fk) REFERENCES self(pk));
--echo # Dropping foreign key and adding partitioning is OK.
ALTER TABLE self DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20;
--echo # Adding foreign key and removing partitioning is OK.
--echo # The below statement provides coverage gor bug#28486106 "ALTER TABLE
--echo # ADD FOREIGN KEY ... REMOVE PARTITIONING FAILS IF SELF-REFENCE".
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk) REMOVE PARTITIONING;
DROP TABLES self;
--echo #
--echo # 6) Check that we prohibit foreign keys with SET NULL action with
--echo # non-nullable referencing columns.
--echo #
--echo #
--echo # 6.1) Attempt to add foreign key with SET NULL action and
--echo # non-nullable column should lead to error.
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_COLUMN_NOT_NULL
CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
--error ER_FK_COLUMN_NOT_NULL
CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL);
CREATE TABLE child (fk INT NOT NULL);
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL;
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL;
DROP TABLE child;
--echo # Case of when column is implicitly made non-nullable due to PRIMARY
--echo # KEY should be handled in the same way.
--error ER_FK_COLUMN_NOT_NULL
CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
--error ER_FK_COLUMN_NOT_NULL
CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL);
CREATE TABLE child (fk INT);
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL;
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL;
DROP TABLE child;
--echo #
--echo # 6.2) Attempt to make referencing column non-nullable in existing
--echo # foreign key with SET NULL action should lead to error as well.
CREATE TABLE child_one (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
CREATE TABLE child_two (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL);
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child_one MODIFY COLUMN fk INT NOT NULL;
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child_two CHANGE COLUMN fk fk1 INT NOT NULL;
--echo # Case of when column is implicitly made non-nullable due addition
--echo # of PRIMARY KEY should be handled in the same way.
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child_one ADD PRIMARY KEY(fk);
--error ER_FK_COLUMN_NOT_NULL
ALTER TABLE child_two ADD PRIMARY KEY(fk);
DROP TABLES child_one, child_two, parent;
--echo #
--echo # 7) Test that we check that referencing and referenced column types are
--echo # compatible. Such check should be performed for newly created foreign
--echo # keys and when we change types of columns in existing foreign keys.
--echo #
--echo #
--echo # 7.a) Attempt to create new foreign key between columns of incompatible
--echo # types should lead to error. This should happen even in
--echo # FOREIGN_KEY_CHECKS=0 mode.
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE child (fk CHAR(10));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
SET FOREIGN_KEY_CHECKS=1;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk));
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk));
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk);
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES self, child;
--echo #
--echo # 7.b) Attempt to change referencing or referenced column in existing
--echo # foreign key to incompatible type should lead to error. This
--echo # should also happen in FOREIGN_KEY_CHECKS=0 mode in general case
--echo # (there is exception for charset changes which we test below).
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE parent MODIFY pk CHAR(10);
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE parent MODIFY pk CHAR(10);
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY fk CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY pk CHAR(10);
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY fk CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY pk CHAR(10);
SET FOREIGN_KEY_CHECKS=1;
--echo # Modifying types of both referencing and referenced columns to
--echo # to compatible types is OK. However FOREIGN_KEY_CHECKS=0 is needed
--echo # to avoid error about possible FK violation due to data conversion.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE self MODIFY pk CHAR(10), MODIFY fk CHAR(10);
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent, self;
--echo #
--echo # 7.c) Test compatibility checks for multi-column foreign keys.
CREATE TABLE parent (pk1 INT, pk2 INT, PRIMARY KEY (pk1, pk2));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk1 INT, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2));
CREATE TABLE child (fk1 INT, fk2 CHAR(10));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child ADD FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2);
DROP TABLE child;
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk2 CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE parent MODIFY pk2 CHAR(10);
DROP TABLE child, parent;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2),
FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2));
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self ADD FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2);
DROP TABLE self;
CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 INT, PRIMARY KEY (pk1, pk2),
FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY fk2 CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE self MODIFY pk2 CHAR(10);
DROP TABLE self;
--echo #
--echo # 7.e) Test how compatibility rules work for various types.
--echo # Different engines have different rules, so the below
--echo # part of test is specific to InnoDB.
--echo #
--echo # We don't check compatibility for BLOB based types as they only
--echo # support prefix indexes which are not usable for foreign keys.
--echo # Also we don't provide coverage for legacy types.
--echo #
--echo # 7.e.I) For integer types both type size and signedness should match.
CREATE TABLE parent (pk INT PRIMARY KEY);
--echo # Foreign keys over integer types are supported.
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk INT UNSIGNED;
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk INT;
DROP TABLE child, parent;
--echo # Integer types are not compatible with most of other types
--echo # (we check types with same storage requirements as INT below).
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Oddly enough, some integer types are compatible with some temporal
--echo # types, enums and sets. However, this is probably a bug and not a
--echo # feature, so we don't test it here.
DROP TABLE parent;
--echo #
--echo # 7.e.II) For floating point types only the exact type matters.
CREATE TABLE parent (pk DOUBLE PRIMARY KEY);
--echo # Though using such types in foreign key is EXTREMELY bad idea they
--echo # are supported.
CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Signedness doesn't matter.
CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
--echo # Floating point types are not compatible with other types.
--echo # (we check types with same storage requirements as FLOAT below).
CREATE TABLE parent (pk FLOAT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.III) Compatibility rules for DECIMAL type are broken.
--echo #
--echo # InnoDB considers this type to be binary string type.
--echo # So it doesn't take into account precision and scale.
--echo # And it is hard to imagine that comparing binary strings
--echo # representing DECIMAL(10,9) and DECIMAL(10,1) can work
--echo # correctly. Making this type compatible with other binary
--echo # string types, some temporals and BIT type is probably a
--echo # bad idea too.
CREATE TABLE parent (pk DECIMAL(6,2) PRIMARY KEY);
--echo # Foreign keys over DECIMAL columns are supported.
CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Signedness doesn't matter.
CREATE TABLE child (fk DECIMAL(6,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # DECIMAL type is not compatible with many other types.
--echo # (we check types with same storage requirements as DECIMAL(6,2) below).
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.IV) All string types are compatible with each other provided
--echo # that charset matches (there are exceptions to the latter
--echo # rule, see below).
CREATE TABLE parent (pk CHAR(10) PRIMARY KEY);
--echo # Difference in size doesn't matter.
CREATE TABLE child (fk CHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Difference in type doesn't matter
DROP TABLE child;
CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
--echo # Even VARCHARs which use different number of bytes to store length
--echo # are compatible.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child, parent;
--echo # However both columns must use the same collation.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(100) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(100) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Binary strings are not compatible with non-binary strings.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo # But all binary string types are compatible.
CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY);
CREATE TABLE child (fk BINARY(100), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
--echo # Non-binary string types are incompatible with non-string types.
CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo # Binary string types are incompatible with many non-string types.
CREATE TABLE parent (pk BINARY(4) PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk BINARY(1);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Since we think that binary string compatibility with DECIMAL and some temporal types
--echo # is probably a bug, we don't cover it here.
DROP TABLE parent;
--echo #
--echo # 7.e.V) Compatibility rules for some of temporal types are broken.
--echo #
--echo # InnoDB considers TIME, TIMESTAMP and DATETIME types to be
--echo # binary strings. As result they are compatible with each other
--echo # (no matter what scale is used), binary string, DECIMAL and BIT
--echo # types, which makes little sense.
--echo # YEAR and DATE types are considered to be integer types which
--echo # makes them compatible with some other integer types, enums and
--echo # sets, which is probably bad idea too.
--echo #
--echo # YEAR and DATE are only compatible with itself (and integer types,
--echo # enums and sets with the same storage size which is probably a bug);
CREATE TABLE parent(pk DATE PRIMARY KEY);
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(6,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(24), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo # TIME, TIMESTAMP and DATETIME types are compatible only
--echo # with other types which InnoDB considers binary strings.
--echo # Their scale doesn't matter (which is probably a bug).
CREATE TABLE parent (pk TIMESTAMP PRIMARY KEY);
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.VI) Columns of ENUM type are compatible if storage size is
--echo # the same.
--echo #
--echo # They are also compatible with some integer types, but it is not
--echo # clear if it is a bug or feature.
--echo #
CREATE TABLE parent(pk ENUM('a') PRIMARY KEY);
--echo # Foreign key over ENUMs are supported, element names and count do
--echo # not matter provided that storage size is the same.
CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Storage size should match.
--let $x100enum =`WITH RECURSIVE gen (num, el) AS (SELECT 1, CAST('\'a1\'' AS CHAR(2048)) UNION SELECT num+1, CONCAT(el,',\'a',num+1,'\'') FROM gen WHERE num <= 256) SELECT el FROM gen WHERE num = 256`
--error ER_FK_INCOMPATIBLE_COLUMNS
--eval CREATE TABLE child (fk ENUM($x100enum), FOREIGN KEY (fk) REFERENCES parent(pk))
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.VII) Columns of SET type are compatible if storage size is
--echo # the same.
--echo #
--echo # They are also compatible with some integer types, but it is not
--echo # clear if it is a bug or feature.
--echo #
CREATE TABLE parent(pk SET('a') PRIMARY KEY);
--echo # Foreign key over SETs are supported, element names and count do
--echo # not matter provided that storage size is the same.
CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Storage size should match.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.VIII) Columns of BIT type are compatible with each other
--echo # independently of size.
--echo #
--echo # They are considered binary strings so compatible with other
--echo # binary string types (which might make sense). They are also
--echo # compatible with some temporals and DECIMAL type, which makes
--echo # little sense.
CREATE TABLE parent(pk BIT(32) PRIMARY KEY);
--echo # Column size doesn't matter.
CREATE TABLE child (fk BIT(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo #
--echo # 7.e.IX) In FOREIGN_KEY_CHECKS=0 mode InnoDB allows to change charsets
--echo # of string columns in existing foreign keys. Allowing such
--echo # temporary discrepancies is necessary as in general case there
--echo # is no way to change charset of both child and parent columns
--echo # simultaneously. Such discrepancies are still prohibited
--echo # in newly created foreign keys.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1,
FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4;
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4;
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET latin1;
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET latin1;
DROP TABLE child;
--echo # Adding new foreign key with discrepancies is not allowed
--echo # even in FOREIGN_KEY_CHECKS=0 mode.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4,
FOREIGN KEY (fk) REFERENCES parent(pk));
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
DROP TABLE child, parent;
--echo # One cannot create such discrepancy when adding parent to
--echo # orphan foreign key.
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1,
FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
CREATE TABLE parent0 (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
RENAME TABLE parent0 TO parent;
DROP TABLES child, parent0;
--echo # This exception doesn't apply to binary strings though.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET binary PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(20) CHARACTER SET binary,
FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent;
--echo #
--echo # WL#8910: Ensure foreign key error does not reveal information about
--echo # parent table for which user has no access privileges.
--echo #
# We cannot verify these scenarios with 'test' database since MTR garants DB level
# privileges to anonymous user. That will interfere the testing
--let current_DB = `SELECT DATABASE()`
CREATE DATABASE wl8910db;
USE wl8910db;
--echo #Set up tables.
CREATE TABLE t1(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=INNODB;
CREATE TABLE t2(fld1 INT PRIMARY KEY, fld2 INT, CONSTRAINT fk2
FOREIGN KEY (fld1) REFERENCES t1 (fld1)) ENGINE=InnoDB;
CREATE TABLE t3(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=InnoDB;
CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) ENGINE=INNODB;
INSERT INTO parent (id) VALUES(1);
INSERT INTO child (id,parent_id) VALUES(1,1);
--echo # Set up stored routines
CREATE PROCEDURE p1() SQL SECURITY INVOKER INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
CREATE PROCEDURE p2() SQL SECURITY DEFINER INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT SQL SECURITY INVOKER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
CREATE FUNCTION f2() RETURNS INT SQL SECURITY DEFINER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
DELIMITER ;|
--echo # Set up views.
CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t2;
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT * FROM t2;
--echo # Set up users and permissions.
CREATE USER user1@localhost;
CREATE USER user2@localhost;
CREATE USER user3@localhost;
CREATE USER user4@localhost;
CREATE USER user5@localhost;
CREATE USER user6@localhost;
CREATE USER user7@localhost;
GRANT INSERT (fld1, fld2) ON t2 TO user1@localhost;
GRANT INSERT ON v1 TO user2@localhost;
GRANT INSERT ON v2 TO user2@localhost;
GRANT SYSTEM_USER ON *.* TO user2@localhost;
GRANT SET_ANY_DEFINER ON *.* TO user2@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO user2@localhost;
GRANT EXECUTE ON PROCEDURE p2 TO user2@localhost;
GRANT EXECUTE ON FUNCTION f1 TO user2@localhost;
GRANT EXECUTE ON FUNCTION f2 TO user2@localhost;
GRANT SELECT, DELETE, INSERT, UPDATE on wl8910db.* to user4@localhost;
GRANT SELECT, DELETE, INSERT, UPDATE on wl8910db.parent to user5@localhost;
GRANT SELECT, DELETE, INSERT, UPDATE on wl8910db.parent to user6@localhost;
GRANT SELECT, DELETE, INSERT, UPDATE on wl8910db.child to user6@localhost;
GRANT SELECT ON wl8910db.* TO user7@localhost;
GRANT DELETE ON wl8910db.parent TO user7@localhost;
--enable_connect_log
connect (con1, localhost, user1,,wl8910db);
--echo # Without patch, reveals parent table's information.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
--echo # Warning displayed does not reveal parent table information.
INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2);
SHOW WARNINGS;
connection default;
GRANT SELECT ON t1 TO user1@localhost;
connection con1;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
connection default;
ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (fld2) REFERENCES t3(fld1);
connection con1;
--echo # Without patch, reveals parent table's information.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
--echo # Warning displayed does not reveal parent table information.
INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2);
SHOW WARNINGS;
connection default;
GRANT SELECT ON t3 TO user1@localhost;
connection con1;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
connection default;
GRANT INSERT (fld1, fld2) ON t2 TO user2@localhost;
GRANT CREATE ROUTINE ON wl8910db.* TO user2@localhost;
--echo # Tests where DML reports FK constraint failure within Stored Routines.
connect (con2, localhost, user2,,wl8910db);
--echo # The SQL security for p1 is invoker where invoker lacks permission
--echo # to parent table, hence parent table information is not displayed.
--error ER_NO_REFERENCED_ROW_2
CALL p1();
--echo # The SQL security p2 is definer, where the definer has access privilege
--echo # to the parent table, hence parent table information is displayed.
--error ER_NO_REFERENCED_ROW_2
CALL p2();
--echo # The SQL security for f1 is invoker where invoker lacks permission
--echo # to parent table, hence parent table information is not displayed.
--error ER_NO_REFERENCED_ROW_2
SELECT f1();
--echo # The SQL security f2 is definer, where the definer has access privilege
--echo # to the parent table, hence parent table information is displayed.
--error ER_NO_REFERENCED_ROW_2
SELECT f2();
--echo # Test for DMLs on VIEWS.
--echo # The invoker does not have access to the parent table, hence the parent
--echo # table information is not displayed.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO v1 VALUES (1, 2);
--echo # DML on view executed within the definer context where the invoker does
--echo # not have access to the parent table, hence the parent table information
--echo # is not displayed.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO v2 VALUES (1, 2);
connection default;
GRANT SELECT ON t1 TO user2@localhost;
GRANT SELECT ON t3 TO user2@localhost;
GRANT CREATE VIEW ON wl8910db.* TO user2@localhost;
disconnect con2;
connect (con2, localhost, user2,,wl8910db);
--echo # DML on view executed within the definer context where the invoker
--echo # has access to the parent table, hence the parent table information
--echo # is displayed.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO v2 VALUES (1, 2);
--echo # Tests with SET_ANY_DEFINER
--echo # Set up stored routines and views by user with SET_ANY_DEFINER
--echo # privilege.
CREATE DEFINER=root@localhost PROCEDURE p3() SQL SECURITY DEFINER
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
DELIMITER |;
CREATE DEFINER=root@localhost FUNCTION f3() RETURNS
INT SQL SECURITY DEFINER
BEGIN
INSERT INTO t2 (fld1, fld2) VALUES (1, 2);
RETURN 0;
END|
DELIMITER ;|
CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v3 AS
SELECT * FROM t2;
--echo # Grant privileges for user3.
connection default;
GRANT EXECUTE ON PROCEDURE p3 TO user3@localhost;
GRANT EXECUTE ON FUNCTION f3 TO user3@localhost;
GRANT INSERT ON v3 TO user3@localhost;
GRANT SELECT ON t1 TO user3@localhost;
GRANT SELECT ON t3 TO user3@localhost;
connect (con3, localhost, user3,,wl8910db);
--echo # Since the execution happens in the definer 'root' user
--echo # context, the parent table informaton is exposed.
--error ER_NO_REFERENCED_ROW_2
CALL p3();
--error ER_NO_REFERENCED_ROW_2
SELECT f3();
--error ER_NO_REFERENCED_ROW_2
INSERT INTO v3 VALUES(4, 5);
--echo # user4 have access to the child table, hence the child table
--echo # information is displayed.
connect (con4, localhost, user4,,wl8910db);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent WHERE id = 1;
--echo # user5 do not have access to the child table, hence the child table
--echo # information is not displayed.
connect (con5, localhost, user5,,wl8910db);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent WHERE id = 1;
--echo # user6 have access to the child table, hence the child table
--echo # information is displayed.
connect (con6, localhost, user6,,wl8910db);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent WHERE id = 1;
--echo # user7 have access to the child table, hence the child table
--echo # information is displayed.
connect (con7, localhost, user7,,wl8910db);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent WHERE id = 1;
--echo # Cleanup
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
disconnect con5;
disconnect con6;
disconnect con7;
DROP VIEW v1, v2, v3;
DROP TABLE t2, t3, t1, parent, child;
DROP USER user1@localhost;
DROP USER user2@localhost;
DROP USER user3@localhost;
DROP USER user4@localhost;
DROP USER user5@localhost;
DROP USER user6@localhost;
DROP USER user7@localhost;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION f3;
DROP DATABASE wl8910db;
# Restore the current DB
--eval USE $current_DB
--disable_connect_log
--echo #
--echo # Bug #28122781 FOREIGN KEY REFERENCE OPTION RESTRICT IGNORED AFTER MYSQLDUMP AND RELOAD.
--echo #
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE t2 (
t1_id INT NOT NULL,
CONSTRAINT t2_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON UPDATE RESTRICT
);
CREATE TABLE t3 (
t1_id INT NOT NULL,
CONSTRAINT t3_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE RESTRICT
);
CREATE TABLE t4 (
t1_id INT NOT NULL,
CONSTRAINT t4_fk FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SHOW CREATE TABLE t4;
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE
TABLE_NAME IN ('t1', 't2', 't3', 't4');
--echo # dump tables t1, t2, t3, t3
--exec $MYSQL_DUMP test t1 t2 t3 t4 > $MYSQLTEST_VARDIR/tmp/testing.sql
DROP TABLE t1, t2, t3, t4;
--echo # reload dump
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/testing.sql
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SHOW CREATE TABLE t4;
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE
TABLE_NAME IN ('t1', 't2', 't3', 't4');
DROP TABLE t1, t2, t3, t4;
--remove_file $MYSQLTEST_VARDIR/tmp/testing.sql
--echo #
--echo # Bug #27353767 "FOREIGN KEY IS ALWAYS IN LOWER CASE".
--echo #
--echo # Check that the way in which referencing and referenced column names in
--echo # foreign key definition are stored and shown in SHOW CREATE TABLE output
--echo # and I_S tables is with 5.7 and earlier versions.
--echo #
--echo # We always use version of name of referencing column which comes from
--echo # the definition of referencing table and not version from FOREIGN KEY
--echo # clause.
--echo # If referenced table exists than we use version of referenced column
--echo # name which comes from its definition, and not version from FOREIGN KEY
--echo # clause.
CREATE TABLE parent (Pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK));
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
DROP TABLE child;
CREATE TABLE child (Fk VARCHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
--echo # Ditto for self-referencing tables.
CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10),
FOREIGN KEY (fK) REFERENCES self(pK));
SHOW CREATE TABLE self;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
DROP TABLE self;
CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10));
ALTER TABLE self ADD FOREIGN KEY (fK) REFERENCES self(pK);
SHOW CREATE TABLE self;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
--echo #
--echo # If column name is changed (even to equivalent one) then the name shown
--echo # in foreign key definition is updated too.
ALTER TABLE parent CHANGE COLUMN Pk PK VARCHAR(20);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
ALTER TABLE child CHANGE COLUMN Fk FK VARCHAR(20);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
--echo # Ditto for self-referencing tables.
ALTER TABLE self CHANGE COLUMN Pk PK VARCHAR(20);
SHOW CREATE TABLE self;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
ALTER TABLE self CHANGE COLUMN Fk FK VARCHAR(20);
SHOW CREATE TABLE self;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='self';
DROP TABLE self;
--echo #
--echo # Referenced column names are preserved when referenced table is dropped
--echo # (which is possible in FOREIGN_KEY_CHECKS = 0 mode).
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent;
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
--echo #
--echo # Addition of parent doesn't change the referenced column names.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
DROP TABLES child, parent;
--echo #
--echo # We use version of referenced column name coming from FOREIGN KEY clause
--echo # when orphan foreign key is created (as referenced table doesn't exist).
--echo #
--echo # Oddly, 5.7 uses version of referencing column name coming from FOREIGN
--echo # KEY clause when orphan FK is added via ALTER TABLE (but not CREATE
--echo # TABLE). This means that in 5.7 there is discrepancy between CREATE and
--echo # ALTER TABLE behavior in this case.
--echo # In 8.0 we are consistent and stick to CREATE TABLE behavior in such
--echo # cases.
CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK));
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
DROP TABLE child;
CREATE TABLE child (Fk VARCHAR(10));
ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
--echo #
--echo # Addition of parent doesn't change the referenced column names.
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
SHOW CREATE TABLE child;
SELECT column_name, referenced_column_name FROM information_schema.key_column_usage
WHERE referenced_table_schema='test' AND referenced_table_name='parent';
SET FOREIGN_KEY_CHECKS=1;
DROP TABLES child, parent;
--echo #
--echo # Test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING INDEX NAME".
--echo #
--echo #
--echo # Original test case.
CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id));
CREATE TABLE t3 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id));
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME IN ('t2', 't3') ORDER BY CONSTRAINT_NAME;
--echo #
--echo # Let us check what foreign key names are used in different situations.
CREATE TABLE t4 (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
FOREIGN KEY (fk1) REFERENCES t1 (id),
CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id),
FOREIGN KEY d (fk3) REFERENCES t1 (id),
CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id));
SHOW CREATE TABLE t4;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME = 't4' ORDER BY CONSTRAINT_NAME;
--echo # Clean-up.
DROP TABLES t1, t2, t3, t4;
--echo #
--echo # Bug #11756183 "FOREIGN KEYS MAY DISAPPEAR AFTER ALTER TABLE RENAME".
--echo # Bug #18713399 "FK CHILD TABLE CANNOT BE CREATED: PROBLEMS AFTER TABLE RENAME"
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
INSERT INTO parent VALUES (1);
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
INSERT INTO child VALUES (1, 1);
SHOW CREATE TABLE child;
--echo # We use ALGORITHM=COPY to ensure that the below ALTER is executed
--echo # using COPY algorithm even if changing column datatype becomes
--echo # in-place operation.
ALTER TABLE child MODIFY COLUMN b BIGINT, RENAME TO child_renamed, ALGORITHM=COPY;
--echo # The foreign key should be still in SHOW CREATE TABLE output.
SHOW CREATE TABLE child_renamed;
--echo # Removal of parent row should not be allowed.
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent WHERE pk = 1;
DROP TABLE child_renamed;
--echo # Part of test covering bug#18713399. The below CREATE TABLE statement
--echo # should not fail due to duplicate foreign key name.
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
DROP TABLES child, parent;
--echo #
--echo # Bug #18199504 "AUTO-NAMING OF FOREIGN KEYS SEEMS BROKEN WHEN
--echo # MULTI-OPERATION ALTER IS USED".
--echo #
CREATE TABLE parent (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32));
CREATE TABLE uncle (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32));
CREATE TABLE child (parent_id INT, c CHAR(32), FOREIGN KEY (parent_id) REFERENCES parent (id));
--echo # Turn off foreign key checking so we can add foreign key using
--echo # inplace algorithm.
SET FOREIGN_KEY_CHECKS=0;
--echo # The problem occured when a foreign key was added by ALTER TABLE
--echo # executed using inplace algorithm which still did table rebuild
--echo # internally. So we drop column to make operation non-instant for
--echo # sure.
ALTER TABLE child ADD COLUMN uncle_id INT, DROP COLUMN c, ADD CONSTRAINT FOREIGN KEY (uncle_id) REFERENCES uncle (id), ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS=1;
SHOW CREATE TABLE child;
DROP TABLES child, parent, uncle;
--echo #
--echo # Bug #28480149 "UPGRADE FAIL: FAILED TO ADD THE FOREIGN KEY
--echo # CONSTRAINT. MISSING INDEX FOR CONSTR".
--echo #
--echo # When InnoDB SE checks if a key can serve as a parent key for a foreign
--echo # key it takes into account columns from hidden part of the key. This
--echo # hidden part consists of primary key columns which are implicitly
--echo # added by InnoDB to non-primary keys.
--echo #
--echo # See dd_upgrade_test test for coverage of upgrade process for such
--echo # foreign keys.
--echo #
SET restrict_fk_on_non_standard_key=OFF;
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, b INT, KEY(a), UNIQUE(b));
--echo # This works both for non-unique and unique keys.
CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk));
DROP TABLES child1, child2;
--echo # This also works when foreign keys are added by ALTER TABLE.
CREATE TABLE child1 (fk1 INT, fk2 INT);
ALTER TABLE child1 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk);
CREATE TABLE child2 (fk1 INT, fk2 INT);
ALTER TABLE child2 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk);
DROP TABLES child1, child2, parent;
--echo # And for complex multi-column cases too.
CREATE TABLE parent (a INT, b INT, c INT, PRIMARY KEY (a,b), KEY(c, a));
CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, FOREIGN KEY (fk1, fk2, fk3) REFERENCES parent (c, a, b));
DROP TABLES child, parent;
--echo # Moreover, this works for keys which are promoted to primary.
CREATE TABLE parent (u INT NOT NULL, a INT, b INT, UNIQUE(u), KEY(a), UNIQUE(b));
CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, u));
CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, u));
DROP TABLES child1, child2, parent;
--echo # Also works correctly when parent table is altered.
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, KEY k1(a, pk), UNIQUE k2(a));
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
ALTER TABLE parent DROP KEY k1;
DROP TABLES child, parent;
--echo # And when we add parent table to previously orphan foreign key.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk1 INT, fk2 INT, CONSTRAINT c FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk));
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE parent (pk INT PRIMARY KEY, a INT, UNIQUE ua(a));
SELECT constraint_name, unique_constraint_name FROM information_schema.referential_constraints
WHERE constraint_schema='test' AND constraint_name='c';
DROP TABLES child, parent;
--echo # However, columns which are implicitly added by InnoDB as hidden
--echo # elements to the primary key are not considered when searching for
--echo # parent key (because primary key contains hidden system DB_TRX_ID,
--echo # DB_ROLL_PTR columns before hidden normal columns).
CREATE TABLE parent (pk INT PRIMARY KEY, a INT);
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (pk, a));
DROP TABLE parent;
--echo # Hidden parts which are column prefixes (because primary key has prefix
--echo # parts [sic!]) are not allowed as parent key parts.
CREATE TABLE parent (a CHAR(10), b int, KEY(b), PRIMARY KEY (a(5)));
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE child (fk1 int, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent (b, a));
DROP TABLE parent;
--echo # Moreover, even hidden parts for full columns are not allowed if
--echo # primary key contains prefix parts. This was supported in 5.7.
CREATE TABLE parent (a INT, b CHAR(10), c int, KEY(c), PRIMARY KEY (a, b(5)));
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE child (fk1 int, fk2 int, FOREIGN KEY (fk1, fk2) REFERENCES parent (c, a));
DROP TABLE parent;
--echo #
--echo # Now similar tests for self-referencing foreign keys.
--echo #
CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
KEY(a), FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk));
CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT,
UNIQUE(b), FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk));
DROP TABLES self1, self2;
CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, KEY(a));
ALTER TABLE self1 ADD FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk);
CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT, UNIQUE(b));
ALTER TABLE self2 ADD FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk);
DROP TABLES self1, self2;
--echo # Test for complex multi-column case.
CREATE TABLE self (a INT, b INT, c INT, fk1 INT, fk2 INT, fk3 INT,
PRIMARY KEY (a,b), KEY(c, a),
FOREIGN KEY (fk1, fk2, fk3) REFERENCES self (c, a, b));
DROP TABLE self;
--echo # Test for removing parent key.
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
KEY k1(a, pk), UNIQUE k2(a),
FOREIGN KEY (fk1, fk2) REFERENCES self (a, pk));
ALTER TABLE self DROP KEY k1;
DROP TABLE self;
--echo # But again this is not supposed to work for hidden columns added to
--echo # primary key.
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT,
FOREIGN KEY (fk1, fk2) REFERENCES self (pk, a));
--echo # Hidden parts which are column prefixes should not work.
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self (a CHAR(10), b int, fk1 int, fk2 CHAR(10), KEY(b),
PRIMARY KEY (a(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (b, a));
--echo # Hidden parts for full columns are not allowed either if
--echo # primary key contains prefix parts. This was supported in 5.7.
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self (a INT, b CHAR(10), c int, fk1 int, fk2 int, KEY(c),
PRIMARY KEY (a, b(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (c, a));
SET restrict_fk_on_non_standard_key=ON;
--echo #
--echo # Bug#21308781 "DROP FOREIGN KEY LEAD TO INCONSISTENT TABLE STRUCTURE
--echo # ON MASTER AND SLAVE".
--echo #
CREATE TABLE t1(pk INT PRIMARY KEY);
CREATE TABLE t2(pk INT PRIMARY KEY);
CREATE TABLE t3(fk1 INT, fk2 INT, KEY k1(fk1),
CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1(pk),
CONSTRAINT b FOREIGN KEY (fk2) REFERENCES t2(pk));
--error ER_DROP_INDEX_FK
ALTER TABLE t3 DROP KEY k1, DROP FOREIGN KEY b, ALGORITHM=COPY;
--echo # Failed ALTER TABLE should have left table intact!
SHOW CREATE TABLE t3;
DROP TABLES t3, t2, t1;
--echo #
--echo # Bug#30214965 "GENERATION OF FK NAMES IS NOT RE-EXECUTION SAFE".
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, a INT);
PREPARE stmt1 FROM 'ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent (pk)';
EXECUTE stmt1;
SHOW CREATE TABLE child;
ALTER TABLE child RENAME COLUMN fk TO fkold, RENAME COLUMN a TO fk;
--echo # Re-execution of ALTER TABLE ADD FOREIGN KEY should succeed.
--echo # New foreign key should get new foreign key name.
EXECUTE stmt1;
SHOW CREATE TABLE child;
DEALLOCATE PREPARE stmt1;
DROP TABLES child, parent;
--echo #
--echo # Bug#30267236 "REPLACE DICT_FOREIGN_PARSE_DROP_CONSTRAINTS() WITH
--echo # SQL-LAYER CHECK."
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk));
CREATE TABLE unrelated (a INT);
--echo #
--echo # Dropping non-existing foreign key should fail with consistent error.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=COPY;
--echo #
--echo # Dropping existing foreign key using wrong table name should fail
--echo # with consistent error as well.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=COPY;
--echo #
--echo # The same should happen if one tries to drop foreign key using
--echo # parent table.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=COPY;
DROP TABLES unrelated, child, parent;
--echo #
--echo # Bug#16904122 "MULTIPLE FK WITH SAME NAME ALLOWED IN ALTER".
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk1 INT, fk2 INT, a INT, KEY(fk1), KEY(fk2));
INSERT INTO child VALUES (NULL, NULL, 1), (NULL, NULL, 1);
--echo # Usage of duplicate foreign key name should be detected before
--echo # proceeding to COPYing of rows (and thus duplicate key error)
--echo # and checking if INPLACE algorithm is supported.
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk),
ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a);
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk),
ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a), ALGORITHM=COPY;
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk),
ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a), ALGORITHM=INPLACE;
--echo # Also check case when we created duplicate to already pre-existing
--echo # foreign key name. Also check that foreign key name comparison is
--echo # case-insensitive in this case.
ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk);
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a);
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a), ALGORITHM=COPY;
--error ER_FK_DUP_NAME
ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk),
ADD UNIQUE (a), ALGORITHM=INPLACE;
DROP TABLES child, parent;
--echo #
--echo # Bug#22364336 "ANOMALY IN ALTER TABLE ADD FOREIGN KEY".
--echo #
--echo # Spaces surrounding dot in qualified parent table name should
--echo # be allowed.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES test . parent (pk));
DROP TABLE child;
CREATE TABLE child (fk INT);
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES test . parent (pk);
DROP TABLES child, parent;
--echo #
--echo # Bug#28703793 "CANNOT ADD FOREIGN KEY CONSTRAINT TO TABLES INSIDE A
--echo # DATABASE NAMED `AUX`"
--echo #
CREATE DATABASE aux;
CREATE TABLE aux.parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES aux.parent (pk));
DROP TABLE child;
DROP DATABASE aux;
--echo #
--echo # Bug#11754659 "CONDITIONAL COMMENTS NOT WORKING IN FOREIGN KEY DEFINITION".
--echo #
CREATE TABLE parent (id INT PRIMARY KEY);
--echo # Test if versioned conditional comments work in CREATE TABLE
--echo # (issue that was reported originally).
CREATE TABLE child (fk INT, CONSTRAINT c1 FOREIGN KEY (fk) REFERENCES parent (id) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */);
SHOW CREATE TABLE child;
ALTER TABLE child DROP FOREIGN KEY c1;
--echo # Also check if they work in ALTER TABLE and that MySQL-specific
--echo # conditional comments also work.
ALTER TABLE child ADD CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES parent /*! (id) */ /*!40008 ON DELETE SET NULL */;
SHOW CREATE TABLE child;
DROP TABLES child, parent;
--echo #
--echo # Bug#21919887 "STATEMENTS IN "EXECUTABLE COMMENTS" ARE IGNORING
--echo # CONSTRAINTS".
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY);
/*!50101 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) */;
SHOW CREATE TABLE child;
DROP TABLE child;
CREATE TABLE child (fk INT);
/*! ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) */;
SHOW CREATE TABLE child;
DROP TABLES child, parent;
--echo #
--echo # Bug#33114045 - TRANSFER_PREEXISTING_FOREIGN_KEYS: ASSERTION `!FIND->IS_VIRTUAL_GCOL()'
--echo # FAILED.
--echo # Assertion to make sure foreign key is referencing non-virtual column failed.
--echo #
CREATE TABLE t (a INT KEY, b INT NOT NULL UNIQUE KEY,
CONSTRAINT FOREIGN KEY (a) REFERENCES t(b));
--echo # Without fix, assertion fails for the following ALTER TABLE statements in
--echo # the debug build. Statements executes successfully in the non-debug build.
--error ER_DUP_FIELDNAME
ALTER TABLE t ADD COLUMN a INT GENERATED ALWAYS AS (1) FIRST;
--error ER_DUP_FIELDNAME
ALTER TABLE t ADD COLUMN b INT GENERATED ALWAYS AS (1) FIRST;
DROP TABLE t;
--echo #
--echo # As a result of table rename operation, orphan non-self-referencing
--echo # foreign keys become non-orphan/adopted self-referencing foreign keys.
--echo # Test cases to verify such transformation of foreign keys.
--echo #
SET SESSION FOREIGN_KEY_CHECKS=0;
--echo # 1. Test case to verify transformation of a foreign key with ALTER TABLE
--echo # RENAME operation using INPLACE algorithm.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f1));
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INPLACE;
# I_S.REFERENTIAL_CONSTRAINTS.unique_constraint_name is updated too.
SELECT constraint_name, unique_constraint_name FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE table_name='t2';
SHOW CREATE TABLE t2;
DROP TABLE t2;
--echo # 2. Test case to verify transformation of a foreign key with ALTER TABLE
--echo # RENAME operation using INSTANT algorithm.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f1));
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT;
# I_S.REFERENTIAL_CONSTRAINTS.unique_constraint_name is updated too.
SELECT constraint_name, unique_constraint_name FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE table_name='t2';
SHOW CREATE TABLE t2;
DROP TABLE t2;
--echo # 3. Test case to verify transformation of a foreign key with ALTER TABLE
--echo # RENAME operation using COPY algorithm.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f1));
ALTER TABLE t1 RENAME TO t2, ALGORITHM=COPY;
# I_S.REFERENTIAL_CONSTRAINTS.unique_constraint_name is updated too.
SELECT constraint_name, unique_constraint_name FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE table_name='t2';
SHOW CREATE TABLE t2;
DROP TABLE t2;
--echo # 4. Test case to verify transformation of a foreign key with RENAME TABLE
--echo # statement.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f1));
RENAME TABLE t1 TO t2;
# I_S.REFERENTIAL_CONSTRAINTS.unique_constraint_name is updated too.
SELECT constraint_name, unique_constraint_name FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE table_name='t2';
SHOW CREATE TABLE t2;
DROP TABLE t2;
--echo # 5. Test case to check if orphan non-self-referencing foreign key is
--echo # transformed when table schema and table name are changed.
--echo # Foreign key is not transformed in this case.
CREATE DATABASE db1;
CREATE TABLE db1.t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f3));
CREATE DATABASE db2;
ALTER TABLE db1.t1 RENAME TO db2.t2, ALGORITHM=COPY;
SHOW CREATE TABLE db2.t2;
RENAME TABLE db2.t2 TO db1.t1;
ALTER TABLE db1.t1 RENAME TO db2.t2, ALGORITHM=INPLACE;
SHOW CREATE TABLE db2.t2;
RENAME TABLE db2.t2 TO db1.t1;
ALTER TABLE db1.t1 RENAME TO db2.t2, ALGORITHM=INSTANT;
SHOW CREATE TABLE db2.t2;
RENAME TABLE db2.t2 TO db1.t1;
RENAME TABLE db1.t1 TO db2.t2;
SHOW CREATE TABLE db2.t2;
DROP DATABASE db1;
DROP DATABASE db2;
--echo # 6. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Foreign key referenced column
--echo # does not exist in the table.
--echo # Table rename operation fails in this case.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY(f2) REFERENCES t2(f3));
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=COPY;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INPLACE;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT;
--error ER_FK_NO_COLUMN_PARENT
RENAME TABLE t1 TO t2;
--echo # 7. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Foreign key referenced column
--echo # is dropped in the same ALTER TABLE statement.
--echo # Table rename operation fails in this case.
ALTER TABLE t1 ADD f3 INT UNIQUE;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, DROP COLUMN f3, ALGORITHM=COPY;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, DROP COLUMN f3, ALGORITHM=INPLACE;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, DROP COLUMN f3, ALGORITHM=INSTANT;
DROP TABLE t1;
--echo # 8. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Foreign key referenced column
--echo # is renamed in the same ALTER TABLE statement.
--echo # Table rename operation fails in this case.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT, KEY (f3),
FOREIGN KEY(f2) REFERENCES t2(f3));
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, CHANGE f3 f4 INT, ALGORITHM=COPY;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, CHANGE f3 f4 INT, ALGORITHM=INSTANT;
--error ER_FK_NO_COLUMN_PARENT
ALTER TABLE t1 RENAME TO t2, CHANGE f3 f4 INT, ALGORITHM=INPLACE;
DROP TABLE t1;
--echo # 9. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Foreign key referenced column
--echo # is a virtual column.
--echo # Table rename operation fails in this case.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, F3 INT AS (f1+1) VIRTUAL,
FOREIGN KEY(f2) REFERENCES t2(f3));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE t1 RENAME TO t2, ALGORITHM=COPY;
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT;
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INPLACE;
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
RENAME TABLE t1 TO t2;
DROP TABLE t1;
--echo # 10. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Parent index does not
--echo # exist in the table.
--echo # Table rename operation fails in this case.
SET restrict_fk_on_non_standard_key = OFF;
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT,
FOREIGN KEY(f2) REFERENCES t2(f3));
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INPLACE;
--error ER_FK_NO_INDEX_PARENT
RENAME TABLE t1 TO t2;
DROP TABLE t1;
SET restrict_fk_on_non_standard_key = ON;
--echo # 11. Test case to verify table rename operation on the table containing
--echo # orphan non-self-referencing foreign key. Table's column is not
--echo # compatible with the foreign key's referenced column.
--echo # Table rename operation fails in this case.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 CHAR(10), KEY(f3),
FOREIGN KEY(f2) REFERENCES t2(f3));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE t1 RENAME TO t2, ALGORITHM=COPY;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT;
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE t1 RENAME TO t2, ALGORITHM=INPLACE;
--error ER_FK_INCOMPATIBLE_COLUMNS
RENAME TABLE t1 TO t2;
DROP TABLE t1;
--echo # WL#15699: Restricts FK on non unique keys and partial keys
--echo # Case 1 - CREATE FK on Simple Key should fail
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, KEY(f2));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, KEY(f2),
FOREIGN KEY(f2) REFERENCES t1(f2));
DROP TABLE t1;
--echo # Case 2 - ALTER FK on Simple Key should fail
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, KEY(f2));
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, KEY(f2));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_f2 FOREIGN KEY(f2) REFERENCES t1(f2);
DROP TABLE t1, t2;
--echo # Case 3 - CREATE FK on Partial Columns of Primary Key should fail
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, PRIMARY KEY(f1, f2, f3));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, f3 INT,
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2));
DROP TABLE t1;
--echo # Case 4 - CREATE FK on Partial Columns of Unique Key should fail
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT, UNIQUE KEY(f2, f3));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, f3 INT,
FOREIGN KEY(f2) REFERENCES t1(f2));
DROP TABLE t1;
--echo # Case 5 - ALTER FK on Partial Columns of Unique Key should fail
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT, UNIQUE KEY(f2, f3));
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
--error ER_FK_NO_UNIQUE_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_f2 FOREIGN KEY(f2) REFERENCES t1(f2);
DROP TABLE t1, t2;
SET restrict_fk_on_non_standard_key=OFF;
--echo # Case 6 - When restrict_fk_on_non_standard_key is set to off,
--echo # FK on Partial Columns of Unique Key should pass
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT, UNIQUE KEY(f2, f3));
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, f3 INT,
FOREIGN KEY(f2) REFERENCES t1(f2));
show create table t1;
show create table t2;
DROP TABLE t2, t1;
--echo # Case 7 - When restrict_fk_on_non_standard_key is set to off,
--echo # FK on Partial Columns of non Unique Key should pass
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT, KEY(f2, f3));
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, f3 INT,
FOREIGN KEY(f2) REFERENCES t1(f2));
show create table t1;
show create table t2;
DROP TABLE t2, t1;
SET restrict_fk_on_non_standard_key=ON;
--echo # Case 8 - FK on Prefix Column key should fail
CREATE TABLE t1 (f1 INT, f2 VARCHAR(100), UNIQUE KEY(f2(10)));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(100),
FOREIGN KEY(f2) REFERENCES t1(f2));
DROP TABLE t1;
--echo # Case 9 - FK on Prefix Partial Column unique key should fail
CREATE TABLE t1 (f1 INT, f2 VARCHAR(100), UNIQUE KEY(f1, f2(10)));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(10),
FOREIGN KEY(f1) REFERENCES t1(f1));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(10),
FOREIGN KEY(f2) REFERENCES t1(f2));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(10),
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2));
DROP TABLE t1;
--echo # Case 10 - FK on Partial Column with multiple unique key should fail
CREATE TABLE t1 (f1 INT, f2 VARCHAR(100), f3 VARCHAR(100),
UNIQUE KEY(f1, f2(10)),
UNIQUE KEY(f1, f2, f3(10)));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(100), f3 VARCHAR(10),
FOREIGN KEY(f1, f2, f3) REFERENCES t1(f1, f2, f3));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(10),
FOREIGN KEY(f1) REFERENCES t1(f1));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (f1 INT, f2 VARCHAR(100), f3 VARCHAR(100),
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2));
--echo # Case 11 - FK on Partial Column unique key with prefix should pass
--echo # with restrict_fk_on_non_standard_key set to off
SET restrict_fk_on_non_standard_key=OFF;
CREATE TABLE t2 (f1 INT, f2 VARCHAR(10),
FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2));
--echo # Case 12 - FK on Composite Unique key with prefix should fail
--echo # with restrict_fk_on_non_standard_key set to off
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE t3(f1 INT, f2 VARCHAR(10), f3 VARCHAR(10),
FOREIGN KEY(f1, f2, f3) REFERENCES t1(f1, f2, f3));
DROP TABLE t2, t1;
SET restrict_fk_on_non_standard_key=ON;
--echo # Case 13 - FK on Functional Index column should fail
CREATE TABLE ft1 (f1 VARCHAR(100), UNIQUE INDEX idx1 ((SUBSTRING(f1, 1, 10))) );
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE ft2 (f1 VARCHAR(100),
FOREIGN KEY(f1) REFERENCES ft1(f1));
drop table ft1;
--echo # Case 14 - FK on virtual generated column index should fail
CREATE TABLE t1 (f1 VARCHAR(10), f2 VARCHAR(10),
f3 VARCHAR(30) AS (CONCAT(f1, f2)) VIRTUAL,
UNIQUE INDEX(f3));
--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN
CREATE TABLE t2 (f1 VARCHAR(100),
FOREIGN KEY(f1) REFERENCES t1(f3));
drop table t1;
-- echo # Case 15 - Self Referencing FK with non unique key
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT, INDEX ix(i),
FOREIGN KEY (j) REFERENCES self_ref(i));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE self_ref(pk INT, i INT, j INT, k INT, key(pk),
FOREIGN KEY (j,k) REFERENCES self_ref(pk,i));
-- echo # Case 16 - Self Referencing FK with partial key
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE self_ref(pk INT, i INT, j INT, k INT, UNIQUE KEY(pk, i),
FOREIGN KEY (j) REFERENCES self_ref(pk));
-- echo # Case 17 - Self Referencing FK with prefix key
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE self_ref(f1 VARCHAR(100), f2 VARCHAR(100),
PRIMARY KEY(f1(10)),
FOREIGN KEY (f2) REFERENCES self_ref(f1));
SET FOREIGN_KEY_CHECKS=OFF;
CREATE TABLE t1 (pk INT PRIMARY KEY, b INT,
FOREIGN KEY (b) REFERENCES t2(c));
--error ER_FK_NO_UNIQUE_INDEX_PARENT
CREATE TABLE t2 (pk1 INT PRIMARY KEY, c INT);
SET restrict_fk_on_non_standard_key=OFF;
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE t2 (pk1 INT PRIMARY KEY, c INT);
DROP TABLE t1;
SET restrict_fk_on_non_standard_key=ON;
#Cleanup
SET FOREIGN_KEY_CHECKS = DEFAULT;
Reference in a new issue View git blame Copy permalink