Bug #74609 Unable to update Foreign Key created as NOT NULL to table named with a Dollar $
Submitted: 28 Oct 2014 18:52 Modified: 18 Apr 10:15
Reporter: Chris Thielen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.21/5.7 OS:Linux
Assigned to: CPU Architecture:Any

[28 Oct 2014 18:52] Chris Thielen
Description:
If a BIGINT NOT NULL foreign key is added to a table with FOREIGN_KEY_CHECKS off, the foreign key is populated with zeros (0).  

When FOREIGN_KEY_CHECKS are turned back on, you cannot update the keys to valid values if the second table has a Dollar Sign in its name.

This seems to be a regression in at least the 5.6 series (I also tested against 5.6.14).  5.5.34 does not have the same issue.

FYI, we use $ as a metadata delimiter on table and column names.

The error reported by the update statement is:
--------------------------
   ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdollar`.`table1`, CONSTRAINT `fk_1` FOREIGN KEY (`DollarID`) REFERENCES `dollar$` (`DollarID`))
--------------------------

SHOW ENGINE INNODB STATUS shows:
-------------------
MySQL thread id 5, OS thread handle 0x7f5c4bd58700, query id 251382 air13.local 10.211.55.2 root updating
update table1 set DollarID = 3
Foreign key constraint fails for table `testdollar`.`table1`:
,
  CONSTRAINT `fk_1` FOREIGN KEY (`DollarID`) REFERENCES `dollar$` (`DollarID`)
Trying to add to index `fk_idx` tuple:
DATA TUPLE: 2 fields;
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 8; hex 8000000000000001; asc         ;;

But the parent table `testdollar`.`dollar$`
or its .ibd file does not currently exist!
--------------------------

How to repeat:
Run this test case and note the failure.  Run again, but rename "dollar$" to "dollar" and note the keys are updated.

DROP DATABASE IF EXISTS testdollar; CREATE DATABASE testdollar; USE testdollar;   
CREATE TABLE `table1` (
  `ID` BIGINT NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

INSERT INTO table1 VALUES (1), (2); 

CREATE TABLE `dollar$` (
  `DollarID` BIGINT NOT NULL,
  PRIMARY KEY (`DollarID`)
) ENGINE = InnoDB;

INSERT INTO dollar$ VALUES (3), (4);

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `table1` ADD COLUMN `DollarID` BIGINT(20) NOT NULL,
  ADD CONSTRAINT `fk_1` FOREIGN KEY (`DollarID` ) REFERENCES `dollar$` (`DollarID` )
, ADD INDEX `fk_idx` (`DollarID` ASC) ;

SET FOREIGN_KEY_CHECKS=1;

update table1 set DollarID = 3; -- 

Suggested fix:
A workaround is to define the foreign key as NULLABLE, populate the data, then alter the table and define the foreign key as NOT NULL.
[28 Oct 2014 18:53] Chris Thielen
SQL Script that demonstrates the error

Attachment: testdollar.sql (application/octet-stream, text), 624 bytes.

[28 Oct 2014 23:26] Miguel Solorzano
C:\dbs>net start mysqld56
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > source c:/tmp/testdollar.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.19 sec)

Query OK, 2 rows affected (0.23 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.20 sec)

Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdollar`.`table1`, CONSTRAINT `fk_1` FOREIGN KEY (`DollarID`) REFERENCES `dollar$` (`DollarID`))
mysql 5.6 > exit
Bye

C:\dbs>net start mysqld55
The MySQLD55 service is starting...
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.41-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > source c:/tmp/testdollar.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.28 sec)

Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.22 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.76 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0
[28 Oct 2014 23:28] Miguel Solorzano
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > source c:/tmp/testdollar.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.80 sec)

Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.54 sec)

Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (2.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdollar`.`table1`, CONSTRAINT `fk_1` FOREIGN KEY (`DollarID`) REFERENCES `dollar$` (`DollarID`))
mysql 5.7 >
[28 Oct 2014 23:38] Miguel Solorzano
Thank you for the bug report.
[18 Apr 10:15] Dmitry Lenev
Posted by developer:
 
Indeed, this looks to be a duplicate of bug#21094069/#77043 "FOREIGN KEYS WITH SPECIAL
CHARS IN PARENT DB NAME: FALSE CONSTRAINT VIOLATIONTS" and therefore I am closing
this bug as such.