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: | |
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
[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