mysql-test/t/foreign_key_debug.test

#
# Tests of foreign keys that need a debug build or debug_sync feature.
#

--source include/have_debug.inc
--source include/have_debug_sync.inc
# Some parts of the test require enabled binary log.
--source include/have_log_bin.inc

SET @saved_binlog_format= @@SESSION.binlog_format;

SET SESSION debug= '+d,skip_dd_table_access_check';
SET @@foreign_key_checks= DEFAULT;

--echo #
--echo # WL#6929: Move FOREIGN KEY constraints to the global data dictionary
--echo #

--echo # Test coverage for foreign key name generation. Should be removed
--echo # or rewritten when WL#7141 is pushed.

CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY);
CREATE TABLE t3(a INT PRIMARY KEY, b INT, c INT);

ALTER TABLE t3 ADD FOREIGN KEY (b) REFERENCES t1(a);
# InnoDB generated name (until after WL#6599)
SHOW CREATE TABLE t3;
# Server generated name (until after WL#6599)
SELECT fk.name FROM mysql.foreign_keys AS fk, mysql.tables AS t
WHERE fk.table_id = t.id AND t.name = 't3';

ALTER TABLE t3 ADD FOREIGN KEY (c) REFERENCES t1(a);
# InnoDB generated name (until after WL#6599)
SHOW CREATE TABLE t3;
# Server generated name (until after WL#6599)
SELECT fk.name FROM mysql.foreign_keys AS fk, mysql.tables AS t
WHERE fk.table_id = t.id AND t.name = 't3';

ALTER TABLE t3 ADD FOREIGN KEY (b) REFERENCES t1(a);
# InnoDB generated name (until after WL#6599)
SHOW CREATE TABLE t3;
# Server generated name (until after WL#6599)
SELECT fk.name FROM mysql.foreign_keys AS fk, mysql.tables AS t
WHERE fk.table_id = t.id AND t.name = 't3';

ALTER TABLE t3 DROP FOREIGN KEY t3_ibfk_1;
# InnoDB generated name (until after WL#6599)
SHOW CREATE TABLE t3;
# Server generated name (until after WL#6599)
SELECT fk.name FROM mysql.foreign_keys AS fk, mysql.tables AS t
WHERE fk.table_id = t.id AND t.name = 't3';

ALTER TABLE t3 ADD FOREIGN KEY (b) REFERENCES t1(a);
# InnoDB generated name (until after WL#6599)
SHOW CREATE TABLE t3;
# Server generated name (until after WL#6599)
SELECT fk.name FROM mysql.foreign_keys AS fk, mysql.tables AS t
WHERE fk.table_id = t.id AND t.name = 't3';

DROP TABLE t3, t2, t1;

CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE name567890123456789012345678901234567890123456789012345678901234(a INT PRIMARY KEY, b INT);

--error ER_TOO_LONG_IDENT
ALTER TABLE name567890123456789012345678901234567890123456789012345678901234
ADD FOREIGN KEY(b) REFERENCES t1(a);

DROP TABLE name567890123456789012345678901234567890123456789012345678901234, t1;


--echo #
--echo # WL#6049: Meta data locking for foreign keys.
--echo #

--echo #
--echo # Normal CT will set the FK unique constraint name.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key (j));
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j));
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLES child, parent;

--echo #
--echo # CT introducing a missing parent will update the FK unique constraint name in the child.
--echo #

SET @@foreign_key_checks= 0;
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j));

--echo # An index is created for the FK, but the unique constraint name is NULL.
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'child');
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key (j));
SET @@foreign_key_checks= 1;

--echo # After creating the parent, the unique constraint name is updated.
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLES child, parent;

--echo #
--echo # CTL does not copy FKs from the source table.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key (j));
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j));
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

CREATE TABLE child_copy LIKE child;

--echo ## The index is re-created for the new table.
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'child');
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'child_copy');

--echo ## ... but not the constraint.
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLES child, child_copy, parent;

--echo #
--echo # CTL introducing a missing parent will update the FK in the child.
--echo #

SET @@foreign_key_checks= 0;
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j));

--echo ## An index is created for the FK, but the unique constraint name is NULL.
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'parent');
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

CREATE TABLE parent_base(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key (j));
CREATE TABLE parent LIKE parent_base;
SET @@foreign_key_checks= 1;

--echo ## After creating the parent, the unique constraint name is updated.
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLE child, parent_base, parent;

--echo #
--echo # CTS will update the unique constraint name in its FK info.
--echo #

CREATE TABLE source(pk INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO source VALUES (1, 1);

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key(j));
INSERT INTO parent VALUES (2, 1);

SET @@SESSION.binlog_format=STATEMENT;
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j)) AS SELECT pk, j AS fk FROM source;
SET SESSION binlog_format= @saved_binlog_format;
SELECT * FROM child;

--echo ## An index is created for the FK, and the unique constraint name is updated.
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'child');
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLES source, child, parent;

--echo #
--echo # CTS introducing a missing parent will update the FK in the child.
--echo #

SET @@foreign_key_checks= 0;
CREATE TABLE source(pk INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO source VALUES (1, 1);

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j));

--echo ## An index is created for the FK, but the unique constraint name is NULL.
SELECT name FROM mysql.indexes
  WHERE table_id = (SELECT id from mysql.tables WHERE name LIKE 'child');
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

SET @@SESSION.binlog_format=STATEMENT;
CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY my_key(j)) AS SELECT * FROM source;
SET SESSION binlog_format= @saved_binlog_format;
SELECT * FROM child;
SET @@foreign_key_checks= 1;

--echo ## After creating the parent, the unique constraint name is updated.
SELECT unique_constraint_name FROM mysql.foreign_keys
  WHERE referenced_table_name LIKE 'parent';

DROP TABLES source, child, parent;

--echo #
--echo # RENAME will update FK information in both children and parents.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY parent_key(j));

CREATE TABLE child(pk INTEGER PRIMARY KEY, k INTEGER, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j), UNIQUE KEY child_key(k));
--echo # Constraint is enforced.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO child VALUES (1, 2, 3);

CREATE TABLE grandchild(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES child(k));
--echo # Constraint is enforced.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO grandchild VALUES (1, 2);

SET @@foreign_key_checks= 0;
CREATE TABLE orphan_grandchild(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES siebling(k));
SET @@foreign_key_checks= 1;

--echo # FK definitions before rename:

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

RENAME TABLE child TO siebling;

--echo # After the rename, we see that:
--echo # 1. The name of the constraint is changed to 'siebling_ibfk...'.
--echo # 2. The referenced table name of the grandchild is changed to 'siebling'.
--echo # 3. The unique constraint name of the orphan_grandchild is corrected.
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

--echo # Constraint is still enforced.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO siebling VALUES (1, 2, 3);

--echo # Constraint is still enforced.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO grandchild VALUES (1, 2);

--echo # Constraint is enforced here too.
--error ER_NO_REFERENCED_ROW_2
INSERT INTO orphan_grandchild VALUES (1, 2);

DROP TABLE grandchild;
DROP TABLE orphan_grandchild;
DROP TABLE siebling;
DROP TABLE parent;

--echo # When processing LOCK TABLES, we will prelock even
--echo # when F_K_C = 0.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY parent_key(j));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j) ON DELETE CASCADE);

SET @@foreign_key_checks= 0;
LOCK TABLES parent WRITE;

--echo # There are two metadata locks because the child has
--echo # two different FK roles wrt. the parent. Note that
--echo # the locks are stronger for LOCK TABLES.
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME,
  LOCK_TYPE FROM performance_schema.metadata_locks
  WHERE OBJECT_NAME LIKE 'child'
  ORDER BY OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME, LOCK_TYPE;

--echo # From another connection, verify that child is locked.
--connect (con_1, localhost, root)
SET @@session.lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO child VALUES (1, 1);
--disconnect con_1

--connection default
UNLOCK TABLES;
SET @@foreign_key_checks= 1;

--echo # Now, the locks are gone.
SELECT LOCK_TYPE FROM performance_schema.metadata_locks
  WHERE OBJECT_NAME LIKE 'child';

DROP TABLE child;
DROP TABLE parent;

--echo #
--echo # A prepared statement will become invalid if a child table
--echo # is modified between executions of the prepared statement.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, j INTEGER,
  UNIQUE KEY parent_key(j));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk INTEGER,
  FOREIGN KEY (fk) REFERENCES parent(j) ON DELETE CASCADE);

PREPARE stmt FROM 'DELETE FROM parent WHERE pk = ?';

SELECT COUNT_REPREPARE, COUNT_EXECUTE
  FROM performance_schema.prepared_statements_instances
  WHERE STATEMENT_NAME LIKE 'stmt';

SET @a= 1;
EXECUTE stmt USING @a;

--echo # No reprepare for first execution.
SELECT COUNT_REPREPARE, COUNT_EXECUTE
  FROM performance_schema.prepared_statements_instances
  WHERE STATEMENT_NAME LIKE 'stmt';

--echo # Altering child will trigger reprepare on next execution.
ALTER TABLE child ADD COLUMN (j INTEGER);

EXECUTE stmt USING @a;

--echo # Statement has been reprepared for second execution.
SELECT COUNT_REPREPARE, COUNT_EXECUTE
  FROM performance_schema.prepared_statements_instances
  WHERE STATEMENT_NAME LIKE 'stmt';

EXECUTE stmt USING @a;

--echo # Cache version for the prelock entry is updated, so no
--echo # reprepare for third execution.
SELECT COUNT_REPREPARE, COUNT_EXECUTE
  FROM performance_schema.prepared_statements_instances
  WHERE STATEMENT_NAME LIKE 'stmt';

DROP TABLE child;
DROP TABLE parent;


--echo #
--echo # Check situations where there are multiple foreign keys
--echo # referring the same table.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, j INTEGER,
  UNIQUE KEY parent_i_key(i), UNIQUE KEY parent_j_key(j));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER, fk_j INTEGER,
  FOREIGN KEY (fk_i) REFERENCES parent(i),
  FOREIGN KEY (fk_j) REFERENCES parent(j));

ALTER TABLE child RENAME TO siebling;

DROP TABLES siebling, parent;

--echo #
--echo # Rename a table multiple times in the same statement.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
  UNIQUE KEY parent_key(i));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER,
  FOREIGN KEY (fk_i) REFERENCES parent(i));

RENAME TABLE parent TO mother, mother TO father;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

RENAME TABLE child TO sister, sister TO brother;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

RENAME TABLE father TO mother, brother TO sister, mother TO parent, sister TO child;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

DROP TABLES child, parent;

--echo #
--echo # Let a RENAME statement introduce a missing parent and rename
--echo # it further as well.
--echo #

SET @@foreign_key_checks= 0;
CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER,
  FOREIGN KEY (fk_i) REFERENCES mother(i));
SET @@foreign_key_checks= 1;

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
  UNIQUE KEY parent_key(i));

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

RENAME TABLE parent TO mother, mother TO father;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

DROP TABLES child, father;

--echo #
--echo # Rename a parent with a child having several FKs to it.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, j INTEGER,
  UNIQUE KEY parent_i_key(i),
  UNIQUE KEY parent_j_key(j));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER, fk_j INTEGER,
  FOREIGN KEY (fk_i) REFERENCES parent(i),
  FOREIGN KEY (fk_j) REFERENCES parent(j));

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

RENAME TABLE parent TO mother;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

DROP TABLES child, mother;


--echo #
--echo # ALTER TABLE RENAME and complex ALTER TABLE RENAME involving
--echo # self-referncing foreign key.
--echo #
CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY(fk) REFERENCES self(pk));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self RENAME TO self2;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self2 RENAME TO self3, ADD COLUMN i INT;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE self3;

--echo #
--echo # Drop a schema with tables referencing/being referenced by tables
--echo # in a different schema.
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
  UNIQUE KEY parent_i_key(i));

SET @@foreign_key_checks= 0;
CREATE TABLE grandchild(pk INTEGER PRIMARY KEY, fk_i INTEGER,
  FOREIGN KEY (fk_i) REFERENCES s1.child(i));
SET @@foreign_key_checks= 1;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 's1';

CREATE SCHEMA s1;
CREATE TABLE s1.child(pk INTEGER PRIMARY KEY, i INTEGER, fk_i INTEGER,
  UNIQUE KEY child_i_key(i),
  FOREIGN KEY (fk_i) REFERENCES test.parent(i));

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 's1';

--error ER_FK_CANNOT_DROP_PARENT
DROP SCHEMA s1;

SET @@foreign_key_checks= 0;
DROP SCHEMA s1;
SET @@foreign_key_checks= 1;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 's1';

--echo # Skip FK to parent.
CREATE SCHEMA s1;
CREATE TABLE s1.child(pk INTEGER PRIMARY KEY, i INTEGER,
  UNIQUE KEY child_i_key(i));

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 's1';

--error ER_FK_CANNOT_DROP_PARENT
DROP SCHEMA s1;

SET @@foreign_key_checks= 0;
DROP SCHEMA s1;
SET @@foreign_key_checks= 1;

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 's1';

DROP TABLE grandchild;

--echo # No FK from grandchild.
CREATE SCHEMA s1;
CREATE TABLE s1.child(pk INTEGER PRIMARY KEY, fk_i INTEGER);

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

--echo # Introduce FK to parent.
ALTER TABLE s1.child ADD FOREIGN KEY (fk_i) REFERENCES test.parent(i);

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

DROP SCHEMA s1;

DROP TABLE parent;


--echo #
--echo # Trigger an error in Foreign_key_parents_invalidator::invalidate().
--echo #

CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER,
  UNIQUE KEY parent_key(i));

CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER,
  FOREIGN KEY (fk_i) REFERENCES parent(i));

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

SET @@session.debug= '+d,fail_while_invalidating_fk_parents';
ALTER TABLE child RENAME TO siebling;
SET @@session.debug= '-d,fail_while_invalidating_fk_parents';

SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';

DROP TABLE siebling, parent;


--echo #
--echo # Coverage for various corner cases when figuring out unique_constraint_name.
--echo #
CREATE TABLE parent (i INT, j INT, PRIMARY KEY (i), UNIQUE u(i,j));
CREATE TABLE child (i INT, j INT, FOREIGN KEY (i, j) REFERENCES parent (i, j));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE parent RENAME KEY u TO u1;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE child, parent;

CREATE TABLE parent (i INT, j INT, k INT, PRIMARY KEY (i), UNIQUE u(j), UNIQUE u1(i,j), UNIQUE u2(i,j,k));
CREATE TABLE child (i INT, j INT, k INT, FOREIGN KEY (i, j, k) REFERENCES parent (i, j, k));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE parent RENAME KEY u2 TO u3;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE child, parent;

CREATE TABLE parent (i INT, j INT,
                     d INT GENERATED ALWAYS AS (i) VIRTUAL,
                     e INT GENERATED ALWAYS AS (j) VIRTUAL,
                     PRIMARY KEY (i), UNIQUE u(i,d), UNIQUE u1(i,j,e));
CREATE TABLE child (i INT, j INT, FOREIGN KEY (i, j) REFERENCES parent (i, j));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE parent RENAME KEY u1 TO u2;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE child, parent;

CREATE TABLE parent (i INT, a VARCHAR(10), b VARCHAR(10),
                     PRIMARY KEY (i), UNIQUE u(i,a(5)), UNIQUE u1(i,a,b(5)));
CREATE TABLE child (i INT, a VARCHAR(10), FOREIGN KEY (i, a) REFERENCES parent (i, a));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE parent RENAME KEY u1 TO u2;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE child, parent;

CREATE TABLE self (i INT, j INT, i2 INT, j2 INT, PRIMARY KEY (i), UNIQUE u(i,j),
                   FOREIGN KEY (i2, j2) REFERENCES self (i, j));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self RENAME KEY u TO u1;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE self;

CREATE TABLE self (i INT, j INT, k INT, i2 INT, j2 INT, k2 INT,
                   PRIMARY KEY (i), UNIQUE u(j), UNIQUE u1(i,j), UNIQUE u2(i,j,k),
                   FOREIGN KEY (i2, j2, k2) REFERENCES self (i, j, k));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self RENAME KEY u2 TO u3;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE self;

CREATE TABLE self (i INT, j INT, i2 INT, j2 INT,
                   d INT GENERATED ALWAYS AS (i) VIRTUAL,
                   e INT GENERATED ALWAYS AS (j) VIRTUAL,
                     PRIMARY KEY (i), UNIQUE u(i,d), UNIQUE u1(i,j,e),
                     FOREIGN KEY (i2, j2) REFERENCES self (i, j));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self RENAME KEY u1 TO u2;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE self;

CREATE TABLE self (i INT, a VARCHAR(10), b VARCHAR(10), i2 INT, a2 VARCHAR(10),
                   PRIMARY KEY (i), UNIQUE u(i,a(5)), UNIQUE u1(i,a,b(5)),
                   FOREIGN KEY (i2, a2) REFERENCES self (i, a));
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
ALTER TABLE self RENAME KEY u1 TO u2;
SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_name
  FROM mysql.foreign_keys
  WHERE referenced_table_schema LIKE 'test';
DROP TABLE self;


# Restore defaults.
SET @@foreign_key_checks= DEFAULT;
SET SESSION debug= '-d,skip_dd_table_access_check';

--echo #
--echo # Part of systemic test coverage for metadata locks related to foreign
--echo # keys acquired by various DDL statements which requires debug_sync.
--echo #
--echo # The main part of this coverage resides in foreign_key.test.
--echo #

--enable_connect_log
connect (con1, localhost, root,,);
connection default;

--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.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk INT);

SET DEBUG_SYNC="alter_table_inplace_after_lock_downgrade SIGNAL reached WAIT_FOR go";
SET FOREIGN_KEY_CHECKS=0;
--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent (pk), ALGORITHM=INPLACE;

connection con1;
SET DEBUG_SYNC="now WAIT_FOR reached";

--echo # DML on parent is still possible at this point.
INSERT INTO parent VALUES (1);

--echo # But not DDL.
SET @old_lock_wait_timeout= @@lock_wait_timeout;
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;

SET DEBUG_SYNC="now SIGNAL go";

connection default;
--echo # Reap ALTER TABLE
--reap
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE child DROP FOREIGN KEY fk;

--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.

SET DEBUG_SYNC="alter_table_copy_after_lock_upgrade SIGNAL reached WAIT_FOR go";
--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent (pk), ALGORITHM=COPY;

connection con1;
SET DEBUG_SYNC="now WAIT_FOR reached";

--echo # DML on parent is still possible at this point.
INSERT INTO parent VALUES (2);

--echo # But not DDL.
SET @old_lock_wait_timeout= @@lock_wait_timeout;
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;

SET DEBUG_SYNC="now SIGNAL go";

connection default;
--echo # Reap ALTER TABLE
--reap

SET DEBUG_SYNC="RESET";

ALTER TABLE child DROP FOREIGN KEY fk;

--echo # 8.1') ALTER TABLE ... ADD FOREIGN KEY ... COPY due to workaround
--echo #       must upgrade SU lock on parent table SRO lock.

SET DEBUG_SYNC="alter_after_copy_table SIGNAL reached WAIT_FOR go";
--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent (pk), ALGORITHM=COPY;

connection con1;
SET DEBUG_SYNC="now WAIT_FOR reached";

--echo # SELECT on parent is still possible at this point.
SELECT * FROM parent;

--echo # But not changes.
SET @old_lock_wait_timeout= @@lock_wait_timeout;
SET @@lock_wait_timeout= 1;
--error ER_LOCK_WAIT_TIMEOUT
DELETE FROM parent;
SET @@lock_wait_timeout= @old_lock_wait_timeout;

SET DEBUG_SYNC="now SIGNAL go";

connection default;
--echo # Reap ALTER TABLE
--reap

SET DEBUG_SYNC="RESET";

DROP TABLES child, parent;

connection con1;
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;
--disable_connect_log