Bug #77043 Foreign Keys with special chars in parent db name: false constraint violationts
Submitted: 15 May 2015 1:33 Modified: 19 Jun 2015 17:24
Reporter: Jesper wisborg Krogh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.21, 5.6.24 OS:Any
Assigned to: CPU Architecture:Any

[15 May 2015 1:33] Jesper wisborg Krogh
Description:
When a foreign references a table in a database with a non-alphanumeric character (confirmed with "-" and "@") in the name, then under some circumstances it will claim the parent table does not exist.

The requirements are:

* The database name has a non-alphanumeric (including underscore) character. Not sure exactly which characters are affected, but confirmed with a dash (-) and @.
* The foreign key constraint must be added using ALTER TABLE while @@foreign_key_checks = 0, and the parent table must be referenced using its full qualified name (`db-1`.t1 (t1_id)).

How to repeat:
DROP DATABASE IF EXISTS `db-1`;
CREATE DATABASE `db-1`;
use `db-1`;

CREATE TABLE t1 (
   t1_id int unsigned NOT NULL auto_increment PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE t2 (
   t2_id int unsigned NOT NULL auto_increment PRIMARY KEY,
   t1_id int unsigned NULL DEFAULT NULL
) ENGINE=InnoDB;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

ALTER TABLE t2 ADD FOREIGN KEY (t1_id) REFERENCES `db-1`.t1 (t1_id);
      
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

INSERT INTO t1 VALUES (1);

INSERT INTO t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db-1`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `db-1`.`t1` (`t1_id`))

SHOW ENGINE INNODB STATUS\G
...
------------------------                                                                                                                                                                                       
LATEST FOREIGN KEY ERROR                                                                                                                                                                                       
------------------------                                                                                                                                                                                       
2015-05-15 11:18:59 7f5f872a9700 Transaction:                                                                                                                                                                  
TRANSACTION 423838, ACTIVE 0 sec inserting                                                                                                                                                                     
mysql tables in use 1, locked 1                                                                                                                                                                                
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1                                                                                                                                             
MySQL thread id 4, OS thread handle 0x7f5f872a9700, query id 881 localhost root update                                                                                                                         
INSERT INTO t2 (t1_id) VALUES (1)                                                                                                                                                                              
Foreign key constraint fails for table `db-1`.`t2`:                                                                                                                                                            
,                                                                                                                                                                                                              
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `db-1`.`t1` (`t1_id`)                                                                                                                                
Trying to add to index `t1_id` tuple:                                                                                                                                                                          
DATA TUPLE: 2 fields;                                                                                                                                                                                          
 0: len 4; hex 00000001; asc     ;;                                                                                                                                                                            
 1: len 4; hex 00000001; asc     ;;                                                                                                                                                                            

But the parent table `db-1`.`t1`
or its .ibd file does not currently exist!
...
[15 May 2015 1:36] Jesper wisborg Krogh
Posted by developer:
 
Workarounds:

* Create the foreign key with @@foreign_key_checks = 1. If it already exists, first drop it then recreate.
* Create the foreign key together with the CREATE TABLE statement.
[15 May 2015 1:45] Jesper wisborg Krogh
Posted by developer:
 
Also happens if the non-alphanumeric character is in the table name, e.g.:

DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
use db1;

CREATE TABLE `t-1` (
   t1_id int unsigned NOT NULL auto_increment PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE t2 (
   t2_id int unsigned NOT NULL auto_increment PRIMARY KEY,
   t1_id int unsigned NULL DEFAULT NULL
) ENGINE=InnoDB;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

ALTER TABLE t2 ADD FOREIGN KEY (t1_id) REFERENCES db1.`t-1` (t1_id);
      
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

INSERT INTO `t-1` VALUES (1);

INSERT INTO t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t-1` (`t1_id`))

Same workarounds apply.
[15 May 2015 1:48] Jesper wisborg Krogh
Posted by developer:
 
Additional workaround for the cases where the non-alphanumeric character is in the database name and the parent and child tables are in the same database:

* Don't specify the database name when creating the foreign key.
[15 May 2015 1:56] Jesper wisborg Krogh
Posted by developer:
 
Bug also confirmed in 5.6.24
[19 Jun 2015 17:24] Daniel Price
Fixed as of the upcoming 5.6.26, 5.7.8, 5.8.0 releases, and here's the changelog entry:

Opening a foreign key-referenced table with foreign_key_checks enabled
resulted in an error when the table or database name contained special
characters.
[18 Apr 10:18] Dmitry Lenev
Posted by developer:
 
Bug #19907143 / #74609 "UNABLE TO UPDATE FOREIGN KEY CREATED AS NOT NULL TO
TABLE NAMED WITH A DOLLAR $" has been marked as a duplicate of this one.