MySQL Bugs: #74609: Unable to update Foreign Key created as NOT NULL to table named with a Dollar $

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 2019 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

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.

SQL Script that demonstrates the error

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

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

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 >

Thank you for the bug report.

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.