| Bug #92567 | Cannot add foreign key constraint to tables inside a database named `aux` | ||
|---|---|---|---|
| Submitted: | 25 Sep 2018 21:30 | Modified: | 24 Sep 2019 18:47 |
| Reporter: | Dan Gorby | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.7.23/5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Sep 2018 23:45]
MySQL Verification Team
Thank you for the bug report. Verified as described with version 5.6 and 5.7. The version 8.0 works.
[30 Apr 2019 11:19]
Dmitry Lenev
Posted by developer:
The problem affects special ("AUX","PRN","COM1","LPT1",...) table names as well:
CREATE TABLE aux(pk INT PRIMARY KEY);
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES aux (pk));
# The above fails with ERROR HY000: Cannot add foreign key constraint
SHOW ENGINE INNODB STATUS;
#...
#------------------------
#LATEST FOREIGN KEY ERROR
#------------------------
#2019-04-30 13:53:27 0x7f1e453a0700 Error in foreign key constraint of table test/child:
#FOREIGN KEY (fk) REFERENCES aux (pk)):
#Cannot resolve table name close to:
# (pk))
#...
The problem is that dict_scan_id() which is used to parse referenced table
name is not aware of the fact these special names are enconded, and thus
doesn't convert them to encoded form as it does for, e.g. non-filename-safe
characters.
In 8.0 the same works mostly because after failing to lookup table under
unencoded name in InnoDB's internal dictionary cache we try to load its
definition from the SQL-layer data-dictionary using its API, and this
API takes unencoded version of names and is able to find the table.
However, the code is still sub-optimal as it doesn't work correctly
with InnoDB's internal dictionary cache/causes extra overhead.
This code will be eventually removed/changed once InnoDB's code doing
FOREIGN KEY definition parsing is changed to use structures passed
from SQL-layer.
[17 Sep 2019 7:11]
Dmitry Lenev
Posted by developer:
Hello!
Since the code has been changed in 8.0 in such a way that problem is no longer repeatable
and the sub-optimal handling of 'aux' name has been addressed by the following patch:
commit b9cfd9bb493b600637a02bafa2cc824603b4da42 (origin/mysql-8.0, mysql-8.0)
Author: Dmitry Lenev <dmitry.lenev@oracle.com>
Date: Thu Sep 12 10:42:03 2019 +0300
Bug#30287895 "REMOVE DICT_CREATE_FOREIGN_CONSTRAINTS() AND FRIENDS".
After transition to new data-dictionary MySQL 8.0 and subsequent move
of DDL checks for foreign keys to SQL-layer InnoDB's function
dict_create_foreign_constraints() became redundant.
It no longer stores anything in InnoDB data-dictionary (since it was
removed). It is not responsible for updating InnoDB's dictionary cache
either (instead we rely on later dd_table_load_fk() call to do this).
It only does some DDL checks for foreign keys which are duplicated by
SQL-layer or elsewhere in InnoDB code.
OTOH its code still does parsing of original ALTER or CREATE TABLE
statements, which causes a few corner-case bugs. It also creates
problems for further improvements in the foreign key area.
This patch removes dict_create_foreign_constraints() and associated
InnoDB code related to foreign key definitions parsing.
The following bugs are fixed as result:
- Bug#22364336 "ANOMALY IN ALTER TABLE ADD FOREIGN KEY" - spaces
around dot in fully-qualified referenced table name were
erroneously not allowed by InnoDB parser.
- Bug#28486106 "ALTER TABLE ADD FOREIGN KEY ... REMOVE PARTITIONING
FAILS IF SELF-REFENCE" - adding a foreign key and removing
partitioning from table was not allowed within same ALTER TABLE,
even though theoretically this should have been possible.
Again the problem was caused by InnoDB parser not detecting the
fact that new table version was not partitioned.
Also this removes sub-optimal handling of case when parent table
resides in database named "aux" which was mentioned in bug#28703793
"CANNOT ADD FOREIGN KEY CONSTRAINT TO TABLES INSIDE A DATABASE
NAMED `AUX`" (which by itself is not repeatable in MySQL 8.0).
Finally, to minimize impact of dict_create_foreign_constraints()
check was added to SQL-layer to detect situation when user tries
to create several foreign keys on the table with the same name
at early stage of ALTER TABLE execution. This solves the issue
described in bug#16904122 "MULTIPLE FK WITH SAME NAME ALLOWED IN
ALTER".
I am closing this bug as fixed in 8.0.19. Moving it to Documenting
state to let Documentation team to update Release Notes.
[24 Sep 2019 18:47]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.19 release. The following changelog entry applies to: Bug #30287895 REMOVE DICT_CREATE_FOREIGN_CONSTRAINTS() AND FRIENDS. Bug #22364336 ANOMALY IN ALTER TABLE ADD FOREIGN KEY Bug #28486106 ALTER TABLE ADD FOREIGN KEY ... REMOVE PARTITIONING FAILS IF SELF-REFERENCE. Bug #28703793 CANNOT ADD FOREIGN KEY CONSTRAINT TO TABLES INSIDE A DATABASE NAMED `AUX` Bug #16904122 MULTIPLE FK WITH SAME NAME ALLOWED IN ALTER. "The internal InnoDB dict_create_foreign_constraints() function that parsed SQL statements and performed foreign key related DDL checks was removed. The function became redundant with introduction of the data dictionary in MySQL 8.0 and the subsequent relocation of foreign key related DDL checks to the SQL layer. Removal of the dict_create_foreign_constraints() function also addressed the following foreign key issues: * Spaces around dots (.) in a fully qualified referenced table name were not permitted by the InnoDB parser. * Adding a foreign key and removing partitioning in the same ALTER TABLE statement was not permitted. The InnoDB parser did not detect that the new table version was no longer partitioned. * A foreign key constraint could not reference a table inside a schema named AUX. The function that parsed referenced table names did not recognize that special names such as AUX are encoded. Additionally, a check was added to the SQL layer to detect attempts to create multiple foreign keys of the same name on a table at an early stage in the execution of ALTER TABLE statement."
Description: If a database is named 'aux', a table in another database cannot set a foreign key constraint to a table inside of the 'aux' database. When trying to, a 1215 error is reported. This is true for both `CREATE TABLE...` and `ALTER TABLE...` statements. This bug exists in versions 5.6.34, 5.6.41, and 5.7.23, but not in version 8.0.12. My guess is that it has something to do with the fact that databases named 'aux' are stored as 'aux@@@' on the file system. How to repeat: What I did: CREATE DATABASE `aux` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE DATABASE `box` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; DROP TABLE IF EXISTS `aux`.`dads`; DROP TABLE IF EXISTS `box`.`kids`; CREATE TABLE `aux`.`dads` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `box`.`kids` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dad_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `dad_id` (`dad_id`) USING BTREE, CONSTRAINT `kids_ibfk_1` FOREIGN KEY (`dad_id`) REFERENCES `aux`.`dads` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ----- What should happen: Both databases should be created, both tables should be created, and `box.kids.dad_id` should become a foreign key of `aux.dads.id`. ----- What actually happened: Every statement worked except for the last one (CREATE TABLE `box`.`kids`...), which outputted: 1215 - Cannot add foreign key constraint Afterwards, `SHOW ENGINE INNODB STATUS;` reported this: ... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2018-09-25 15:42:08 0x70000b366000 Error in foreign key constraint of table box/kids: FOREIGN KEY (`dad_id`) REFERENCES `aux`.`dads` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci: Cannot resolve table name close to: (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ... ----- I also tried taking out the the `CONSTRAINT...` line from the `CREATE TABLE...` statement and then running a separate `ALTER TABLE...` statement afterwards: CREATE TABLE `box`.`kids` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dad_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `dad_id` (`dad_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE `box`.`kids` ADD FOREIGN KEY (`dad_id`) REFERENCES `aux`.`dads` (`id`); The output was the same: 1215 - Cannot add foreign key constraint ----- I tried both scenarios above with MySQL versions 5.6.34, 5.6.41, 5.7.23, and 8.0.12. The error occurred in versions 5.6.34, 5.6.41, and 5.7.23 but not in version 8.0.12.