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:
Triage: Needs Triage: D2 (Serious)

[24 Feb 2011 7:17] Abhilesh Khatri
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
[24 Feb 2011 9:12] Abhilesh Khatri
Sample Sql file to reproduce the issue

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

[24 Feb 2011 13:16] Miguel Solorzano
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 >
[11 Mar 2011 11:59] Martin Keckeis
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
[28 Mar 2011 8:21] Abhilesh Khatri
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?
[2 May 2011 9:51] Abhilesh Khatri
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?
[2 May 2011 12:13] Abhilesh Khatri
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?
[2 May 2011 12:15] Abhilesh Khatri
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
[9 May 2011 6:06] Abhilesh Khatri
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.
[8 Dec 2011 8:25] Peter Laursen
Was this report closed by mistake?
[8 Dec 2011 10:17] Abhilesh Khatri
Please justify why this ticket was closed? Re-opened it again.
[12 Dec 2011 20:36] Benjamin Morel
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.
[13 Dec 2011 9:21] Benjamin Morel
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
[8 Feb 2012 18:05] John Russell
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.
[26 Aug 2013 6:31] Abhilesh Khatri
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?