| 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: | |
| 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: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 2019 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.
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! ...