Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.10(EOL)
-
None
Description
CREATE OR REPLACE failed with "Duplicate key on write or update" when it replace an empty table with the non-anonymous constraint name
From comment
Test fails due to a duplicate key on SYS_FOREIGN.NAME on the non-anonymous constraint name:
Test:
--source include/have_innodb.inc
|
|
CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB; |
CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB; |
CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB; |
DROP TABLE u, t; |
Actual result:
At line 5: query 'CREATE OR REPLACE TABLE u(a INT PRIMARY KEY, CONSTRAINT c FOREIGN KEY d (a) REFERENCES t (a)) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`u` (errno: 121 "Duplicate key on write or update") |
Expected result:
No errors
Attachments
Issue Links
- blocks
-
MDEV-25292 Atomic CREATE OR REPLACE TABLE
-
- In Progress
-
- is blocked by
-
MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN
-
- Closed
-
- is caused by
-
MDEV-25292 Atomic CREATE OR REPLACE TABLE
-
- In Progress
-
- relates to
-
MDEV-29409 ASAN failure on long fk_id when renaming a table
-
- Closed
-
-
MDEV-19191 Partitioning in child tables with foreign keys
-
- In Progress
-
-
MDEV-29258 Failing assertion for name length on RENAME TABLE
-
- Closed
-
-
MDEV-30416 Can't redefine constraint in a single ALTER TABLE
-
- Confirmed
-
Please squash the fix into a single commit and make sure that the commit message accurately describes and provides justification for all changes.
It is unclear to me why the size of fk_id[] would have to be reduced by 2 bytes instead of being extended by 2 bytes. It is also unclear why dict_remove_db_name() would have to be changed.
One of the commit messages mentions table_name, even though SYS_FOREIGN.ID (which the commit message fails to mention) stores constraint names and not table names. An added comment to row_rename_table_for_mysql() in the same commit appears to disagree with the commit message.
Because innodb_drop_database implements some "garbage collection" of any orphaned FOREIGN KEY constraints on DROP DATABASE, it is simplest that we will retain the databasename/ prefix for all SYS_FOREIGN.ID. Some \377 bytes can be added to the start or the end of the constraint name, e.g., databasename/\377constraintname, databasename/constraintname\377, or databasename/\377\377constraintname.