Bug #92567 Cannot add foreign key constraint to tables inside a database named `aux`
Submitted: 25 Sep 2018 21:30 Modified: 25 Sep 2018 23:45
Reporter: Dan Gorby Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.7.23/5.6 OS:Any
Assigned to: CPU Architecture:Any

[25 Sep 2018 21:30] Dan Gorby
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.
[25 Sep 2018 23:45] Miguel Solorzano
Thank you for the bug report. Verified as described with version 5.6 and 5.7. The version 8.0 works.
[30 Apr 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 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.