MySQL Bugs: #60229: Broken compatibility: Error while create table with foreign key constraints.

Bug #60229	Broken compatibility: Error while create table with foreign key constraints.
Submitted:	24 Feb 2011 7:17	Modified:	26 Aug 2013 6:31
Reporter:	Abhilesh Khatri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.5.9, 5.5.11	OS:	Any (32 bit as well as 64-bit MySQL installer on Windows)
Assigned to:		CPU Architecture:	Any

Description:
I get following errors while creating a table with foreign key constraints in create table statement itself.

Can't create table 'test.PHOTO' (errno: 150)

However, the same create table is working fine with MySQL 5.5.8 build. Backwards compatibility has been broken. 

How to repeat:
Execute the following statements on MySQL Server 5.5.9. Ensure that variable lower_case_table_names = 0.

Use following statement to verify this:
show variables like '%lower_case_table_names%'

DROP TABLE IF EXISTS PHOTO;
DROP TABLE IF EXISTS ADDRESS;
DROP TABLE IF EXISTS PERSON;

CREATE TABLE PERSON (
  PERSON_ID VARCHAR(50) NOT NULL,
  DOB VARCHAR(50) NOT NULL,
  NAME NVARCHAR(255) NOT NULL,
  CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID, DOB)
)Engine=InnoDB;

CREATE TABLE PHOTO (
  PERSON_ID VARCHAR(50) NOT NULL,
  DOB VARCHAR(50) NOT NULL,
  PHOTO_DETAILS VARCHAR(50) NULL,  
  CONSTRAINT PK_PHOTO PRIMARY KEY (PERSON_ID, DOB),
  CONSTRAINT FK_PHOTO_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB)
)Engine=InnoDB;

CREATE TABLE ADDRESS (
  PERSON_ID VARCHAR(50) NOT NULL,
  DOB VARCHAR(50) NOT NULL,
  ADDRESS_ID VARCHAR(50) NOT NULL,
  ADDRESS_DETAILS NVARCHAR(250) NULL,
  CONSTRAINT PK_ADDRESS PRIMARY KEY (PERSON_ID, DOB, ADDRESS_ID),
  CONSTRAINT FK_ADDRESS_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB) ON DELETE CASCADE
)Engine=InnoDB;

You can expect following response:
(0 row(s) affected, 1 warning(s))  -----------> This warning also doesn't appear for MySQL 5.5.8
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

(0 row(s) affected, 1 warning(s))
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

(0 row(s) affected)
Execution Time : 00:00:00:062
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:062

(0 row(s) affected)
Execution Time : 00:00:00:094
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:094

Error Code : 1005
Can't create table 'test.PHOTO' (errno: 150)

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Error Code : 1005
Can't create table 'test.ADDRESS' (errno: 150)

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Sample Sql file to reproduce the issue

Attachment: Simulation.sql (application/octet-stream, text), 944 bytes.

Thank you for the bug report.

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

Copyright (c) 2000, 2010, 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 >use test
Database changed
mysql 5.5 >show variables like '%lower_case_table_names%'
    -> ;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql 5.5 >DROP TABLE IF EXISTS PHOTO;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql 5.5 >DROP TABLE IF EXISTS ADDRESS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >DROP TABLE IF EXISTS PERSON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE PERSON (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   NAME NVARCHAR(255) NOT NULL,
    ->   CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID, DOB)
    -> )Engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE PHOTO (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   PHOTO_DETAILS VARCHAR(50) NULL,
    ->   CONSTRAINT PK_PHOTO PRIMARY KEY (PERSON_ID, DOB),
    ->   CONSTRAINT FK_PHOTO_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID,
    -> DOB)
    -> )Engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.PHOTO' (errno: 150)
mysql 5.5 >
mysql 5.5 >CREATE TABLE ADDRESS (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   ADDRESS_ID VARCHAR(50) NOT NULL,
    ->   ADDRESS_DETAILS NVARCHAR(250) NULL,
    ->   CONSTRAINT PK_ADDRESS PRIMARY KEY (PERSON_ID, DOB, ADDRESS_ID),
    ->   CONSTRAINT FK_ADDRESS_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON
    -> (PERSON_ID, DOB) ON DELETE CASCADE
    -> )Engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.ADDRESS' (errno: 150)
mysql 5.5 >exit
Bye

C:\DBS>55

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

Copyright (c) 2000, 2010, 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 >use test
Database changed
mysql 5.5 >DROP TABLE IF EXISTS PHOTO;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >DROP TABLE IF EXISTS ADDRESS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >DROP TABLE IF EXISTS PERSON;
Query OK, 0 rows affected (0.12 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE PERSON (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   NAME NVARCHAR(255) NOT NULL,
    ->   CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID, DOB)
    -> )Engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE PHOTO (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   PHOTO_DETAILS VARCHAR(50) NULL,
    ->   CONSTRAINT PK_PHOTO PRIMARY KEY (PERSON_ID, DOB),
    ->   CONSTRAINT FK_PHOTO_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID,
    -> DOB)
    -> )Engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE ADDRESS (
    ->   PERSON_ID VARCHAR(50) NOT NULL,
    ->   DOB VARCHAR(50) NOT NULL,
    ->   ADDRESS_ID VARCHAR(50) NOT NULL,
    ->   ADDRESS_DETAILS NVARCHAR(250) NULL,
    ->   CONSTRAINT PK_ADDRESS PRIMARY KEY (PERSON_ID, DOB, ADDRESS_ID),
    ->   CONSTRAINT FK_ADDRESS_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON
    -> (PERSON_ID, DOB) ON DELETE CASCADE
    -> )Engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql 5.5 >show variables like '%lower_case_table_names%'
    -> ;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql 5.5 >

I got the same problem.

On my development machine, i got: 5.5.8 and i wanted to install 5.5.9. on a server

5.5.8 -> working
5.5.0 -> foreign keys broken

My setting: lower_case_table_names = 0

I believe that we all agree with this as a defect (since the status -> verified). 
May I know which MySQL release is expected to have this fix?

This is a very fundamental issue. Since last 2 months no progress is visible. Therefore, increasing defect severity.

My kind request is that at least let us know which MySQL release will this issue be fixed?

I get the same issue regardless of where we define the PK-FK relationships i.e. either in the table definition itself or as a separate alter statement as shown below.

    ALTER TABLE PHOTO ADD CONSTRAINT FK_PHOTO_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB);

    ALTER TABLE ADDRESS ADD CONSTRAINT FK_ADDRESS_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB) ON DELETE CASCADE;

Important thing to notice here is that variable lower_case_table_names=0.

Is there any workaround available for this issue?

I get below error when PK-FK is set using ALTER statement.

Error Code : 1005
Can't create table 'test.#sql-115c_2' (errno: 150)

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Updated the OS section. The issue occurs only on Windows environment. On Linux, despite of lower_case_table_names being 0 (which is the default value), issue doesn't crop up. 

So, issue needs to be addressed for Windows environment alone.

Was this report closed by mistake?

Please justify why this ticket was closed? Re-opened it again.

I have a similar problem with MySQL 5.6.3 on Windows XP. Any query involving a constrained table fails with a 150 error right after importing a SQL dump from a Linux server, involving CamelCased table names. Same job on another linux machine works fine. This is a blocking issue.

Confirmed the bug with the attachment above (Simulation.sql) under MySQL 5.6.3

Note that these tables under information_schema report the wrong name for the tables (test/person instead of test/PERSON):

INNODB_BUFFER_PAGE
INNODB_SYS_TABLESTATS
INNODB_BUFFER_PAGE_LRU
INNODB_SYS_TABLES

Added to changelog for 5.5.20, 5.6.5:

Certain CREATE TABLE statements could fail for InnoDB child tables
 containing foreign key definitions. This problem affected Windows
 systems only, with the setting lower_case_table_names=0. It was a
 regression from MySQL bug #55222.

Well, I verified this on MySQL 5.6.13. Indeed, it works. However, now I see another issue i.e. the tables were stored with lower case names :-(. See below.

mysql> use test;

mysql> SELECT @@lower_case_table_names;
'@@lower_case_table_names'
===========================
'1'

mysql> show tables;

'Tables_in_test'
===================
'address'  ------------> Stored in lower case.
'person'
'photo'

I tried to switch the value of lower_case_table_names to '0'. However, despite of modifying C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini with this property and restarting database service, it is still NOT reflecting.

Workaround
There is one (although NOT so elegant) workaround. When dealing with 'mysql-connector-java-5.0.8-bin.jar', fortunately tables are ALWAYS stored in upper case. That still renders the query response with table name returned in UPPER case.

Is there a better solution already available?