Bug #48875 CREATE INDEX modifies the table name case
Submitted: 18 Nov 2009 17:54 Modified: 18 Nov 2009 22:55
Reporter: Vincent Molines Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.34, 5.1.42-bzr, 5.4.3 OS:Any (Windows, Mac OS X)
Assigned to:
Tags: case, INDEX, lower_case_table_names, table, windows
Triage: Triaged: D3 (Medium)

[18 Nov 2009 17:54] Vincent Molines
Description:
On MySQL Windows, I use lower_case_table_names=2.

Tables have case. Once I create an index on the tables, they loose case (lowercase).

How to repeat:
- "have lower_case_table_names=2 in the conf"
- CREATE DATABASE TestCase ;
- USE TestCase;
- CREATE TABLE TestTable (IDTestTable INTEGER PRIMARY KEY) ;
- SHOW TABLES ;
- "shows TestTable (correct case)"
- CREATE INDEX IDXTestTable ON TestTable (IDTestTable) ;
- SHOW TABLES ;
- "shows testtable (now in lowercase)"

Suggested fix:
Object name case shouldn't be modified by itself.
[18 Nov 2009 18:54] Valerii Kravchuk
Verified with recent 5.1.42 from bzr on Mac OS X:

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

mysql> CREATE DATABASE TestCase ;
Query OK, 1 row affected (0.01 sec)

mysql> USE TestCase;
Database changed
mysql> CREATE TABLE TestTable (IDTestTable INTEGER PRIMARY KEY) ;
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES ;
+--------------------+
| Tables_in_testcase |
+--------------------+
| TestTable          |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX IDXTestTable ON TestTable (IDTestTable) ;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLES ;
+--------------------+
| Tables_in_testcase |
+--------------------+
| testtable          |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table testtable\G
*************************** 1. row ***************************
       Table: testtable
Create Table: CREATE TABLE `testtable` (
  `IDTestTable` int(11) NOT NULL,
  PRIMARY KEY (`IDTestTable`),
  KEY `IDXTestTable` (`IDTestTable`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.42-debug |
+--------------+
1 row in set (0.00 sec)

Table name in both SHOW TABLE results should be the same.
[18 Nov 2009 22:57] Vincent Molines
Verified in 5.4.3-beta
[22 Jul 2011 14:19] Simon Westcott
Confirmed this is still an issue with 5.5.13,

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

mysql> CREATE TABLE TestTable (IDTestTable INTEGER PRIMARY KEY) ;
Query OK, 0 rows affected (0.22 sec)

mysql> SHOW TABLES ;
+----------------+
| Tables_in_test |
+----------------+
| TestTable      |
+----------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX IDXTestTable ON TestTable (IDTestTable) ;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLES ;
+----------------+
| Tables_in_test |
+----------------+
| testtable      |
+----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.13    |
+-----------+
1 row in set (0.02 sec)
[14 Sep 2011 19:49] Prabal Ghosh
Is there any target milestone for this defect to be fixed? The severity is listed as S3, but I believe this to be a very severe defect. The presence of this defect makes the use of MySQL with .NET Entity Framework really unusable particularly if an application is designed to use SQLServer, Oracle and MySQL interchangeably.
[30 Mar 2012 12:32] acsed acsed
As of today (03/30/12), this bug is not yet fixed. It's now opened for now 2.5 years, current version is 5.5.22 (bug appeared in 5.1.34) but still, it's not fixed yet.

Severity is much more critical than S3 as MySQL is actually unusable in lot of cases with Entity Framework.
[31 Aug 2012 2:15] Daniel LaBare
While the bug does still remain when using the CREATE INDEX syntax, you can use the following suitable workaround:

ALTER TABLE `TableName` ADD INDEX `idxColumnName` (`ColumnName`)

and it will leave your table case intact.