Bug #23221 Foreign Key's Referenced table name always forced to lower-case
Submitted: 12 Oct 2006 16:59 Modified: 29 Jan 2007 21:26
Reporter: Bri Gipson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:14.12 OS:Any (Any)
Assigned to: CPU Architecture:Any
Tags: case, case sensitive, fkc, foreign key, lower case, lower-case, reference, table, table name, tablename, upper case, upper-case

[12 Oct 2006 16:59] Bri Gipson
Description:
When creating a foreign key constraint, the table name that is being referenced is always forced by the database into lower case. This causes foreign keys to fail and thus, forces all inserts to a table containing a foreign key constraint to a table containing an upper case character to fail.

EG
my_table.mot_identity -> My_Other_Table.mot_identity

where "->" resembles a foreign key constraint with my_table as the dependent.

This causes problems on data inserts when case sensitivity is turned on on Windows environments, but was a problem on default settings of the Linux (Fedora Core 4) version as well.

How to repeat:
DROP TABLE IF EXISTS My_Other_Table;
CREATE TABLE My_Other_Table
(
  mot_identity INTEGER NOT NULL,
  mot_title VARCHAR(255) NOT NULL,
  PRIMARY KEY ( mot_identity )
)
ENGINE = INNODB;

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(
  mt_identity INTEGER NOT NULL,
  mot_identity INTEGER NOT NULL,
  mt_title VARCHAR(255) NOT NULL,
  PRIMARY KEY ( mt_identity ),
  CONSTRAINT mt_table_FK1 FOREIGN KEY mt_table_FK1 ( mot_identity )
    REFERENCES My_Other_Table ( mot_identity )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = INNODB;

-- SEED THE INDEPENDENT DATA
INSERT INTO My_Other_Table ( mot_identity, mot_title )
VALUES
  ( 1, 'one' ),
  ( 2, 'two' ),
  ( 3, 'three' ),
  ( 4, 'four' );

-- SEED THE DEPENDENT DATA. THIS FAILS!!!
INSERT INTO my_table ( mt_identity, mot_identity, mt_title )
VALUES ( 1, 1, 'A' );

SHOW CREATE TABLE my_table;

/*
  Notice the case of the referenced table in the foreign key constraint!

MySQL> show create table my_table;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                   |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_table | CREATE TABLE `my_table` (  `mt_identity` int(11) NOT NULL,  `mot_identity` int(11) NOT NULL,  `mt_title` varchar(255) NOT NULL,  PRIMARY KEY  (`mt_identity`),  KEY `mt_table_FK1` (`mot_identity`),  CONSTRAINT `mt_table_FK1` FOREIGN KEY (`mot_identity`) REFERENCES `my_other_table` (`mot_identity`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1        |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/

Suggested fix:
Store the name of the table correctly. This appears to be an issue with the MySQL server itself and not any of the gui or command line tools.
[13 Oct 2006 9:45] Sveta Smirnova
Thank you for the report.

Please indecate accurate version of MySQL server you use and output of the statement SHOW VARIABLES LIKE '%lower%';
[9 Nov 2006 16:47] Valeriy Kravchuk
By default on Linux we have:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> CREATE TABLE My_Other_Table
    -> (
    ->   mot_identity INTEGER NOT NULL,
    ->   mot_title VARCHAR(255) NOT NULL,
    ->   PRIMARY KEY ( mot_identity )
    -> )
    -> ENGINE = INNODB;
Query OK, 0 rows affected (0.10 sec)

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

mysql> CREATE TABLE my_table
    -> (
    ->   mt_identity INTEGER NOT NULL,
    ->   mot_identity INTEGER NOT NULL,
    ->   mt_title VARCHAR(255) NOT NULL,
    ->   PRIMARY KEY ( mt_identity ),
    ->   CONSTRAINT mt_table_FK1 FOREIGN KEY mt_table_FK1 ( mot_identity )
    ->     REFERENCES My_Other_Table ( mot_identity )
    ->     ON DELETE NO ACTION
    ->     ON UPDATE NO ACTION
    -> )
    -> ENGINE = INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO My_Other_Table ( mot_identity, mot_title )
    -> VALUES
    ->   ( 1, 'one' ),
    ->   ( 2, 'two' ),
    ->   ( 3, 'three' ),
    ->   ( 4, 'four' );
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO my_table ( mt_identity, mot_identity, mt_title )
    -> VALUES ( 1, 1, 'A' );
Query OK, 1 row affected (0.01 sec)

mysql> show create table my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `mt_identity` int(11) NOT NULL,
  `mot_identity` int(11) NOT NULL,
  `mt_title` varchar(255) NOT NULL,
  PRIMARY KEY  (`mt_identity`),
  KEY `mt_table_FK1` (`mot_identity`),
  CONSTRAINT `mt_table_FK1` FOREIGN KEY (`mot_identity`) REFERENCES `My_Other_Ta
ble` (`mot_identity`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

So, no problems. If lower_case_table_names=2, the following quote the manual (http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html) clearly describes:

"2 	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1"

Note: InnoDB table names are stored in lowercase. Note also that "On Windows the default value is 1. On Mac OS X, the default value is 2.". This can cause problems like described here on some (non-Linux, or with lower_case_table_names != 0 because of explicit setting) systems, but it is not a bug, as this problem is clearly described in the manual.
[29 Jan 2007 21:19] Bri Gipson
In response to information requested by Sveta:

SHOW VARIABLES LIKE '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

SHOW VARIABLES LIKE 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 5.0.22-community-nt |
+---------------+---------------------+

We found that it didn't matter which platform (Linux or Windows).
[29 Jan 2007 21:26] Bri Gipson
MySQL server has been updated since bug was reported. Settings may have also changed. Will test again and report again if necessary.

Thanks.
-Bri