Bug #48875 CREATE INDEX modifies the table name case

• Submitted: 18 Nov 2009 17:54
• Modified: 18 Nov 2009 22:55
• Reporter: Vincent Molines
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.1.34, 5.1.42-bzr, 5.4.3, 8.0
• OS: Any (Windows, Mac OS X)
• CPU Architecture: Any
• Tags: case, INDEX, lower_case_table_names, table, windows

[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] Valeriy 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.

[22 Feb 2018 7:29] Eros Innocenti

This bug occurs also in 5.5.47 (MacOS version).

[11 Oct 2021 12:29] MySQL Verification Team

Bug #105137 marked as duplicate of this one.