MySQL Bugs: #114904: Rename table with COPY does not change the constraint name

Bug #114904	Rename table with COPY does not change the constraint name
Submitted:	7 May 7:34	Modified:	7 May 8:33
Reporter:	John Jove	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	8.0.36, 8.0.11, 8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Run the following statements, in which the last INSERT statement should succeed to insert the value, since there is no FOREIGN KEY violation. But actually, it failed, and returned the following error message:
[23000][1452] Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE)

It seems that the rename table operation does not modify the constraint, since we have already changed table t0 to t2. However, when running SHOW CREATE TABLE t1, a correct constraint is returned.

CREATE TABLE `t1` (
  `c1` double DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t2` (`c2`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How to repeat:
CREATE TABLE t0 (c2 FLOAT8, PRIMARY KEY (c2));
CREATE TABLE t1 (c1 FLOAT8 UNIQUE, FOREIGN KEY (c1) REFERENCES t0(c2) ON UPDATE CASCADE);
ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY;
INSERT INTO t2 (c2) VALUES (1);
INSERT INTO t1 (c1) VALUES (1); -- failed, expected to success

Hello John,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Hi, John Jove:

The main reason for the issue is that the foreign key information of table `t1` has not been updated during or after executing the ddl `ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY`, and you could avoid the issue by restart mysqld after execute the ddl statement.

mysql> CREATE TABLE t0 (c2 FLOAT8, PRIMARY KEY (c2));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t1 (c1 FLOAT8 UNIQUE, FOREIGN KEY (c1) REFERENCES t0(c2) ON UPDATE CASCADE);
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` double DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t2` (`c2`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

restart mysqld.

mysql> INSERT INTO t2 (c2) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (c1) VALUES (1);
Query OK, 1 row affected (0.05 sec)

Contribution submitted via Github - bug#114904 Rename table with COPY does not change the constraint nam… (*) Contribution by c q (Github hotdb-cq, mysql-server/pull/567#issuecomment-2399398198): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2112640864.txt (text/plain), 1.39 KiB.