Description:
When you create a table with lower_case_table_names=2 in uppercase, you can then not rename this table to lower case.
Docs suggest this *might* be behavior (only create table or create database):
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
---
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.
---
But doesn't say you cannot change case specifically afterward. Not sure if this would be considered a docs issue or not, if you support case in CREATE, then should be able to rename that case.
This can be a problem in mixed environment OS and lower_case_table_names settings after the fact:
Docs:
---
If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity.
...
To avoid data transfer problems arising from lettercase of database or table names, you have two options:
* Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
...
If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.
---
However you can't rename these on the systems with setting of 2 without recreating tables, or a dump, drop database, restore into mysql instance with new setting will work as well as they will then be created in lower case.
How to repeat:
mysql> select version();
+------------------------------------+
| version() |
+------------------------------------+
| 5.1.35-enterprise-gpl-advanced-log |
+------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 2 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql> create database X;
Query OK, 1 row affected (0.00 sec)
mysql> use X;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table X(id int)engine=innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.00 sec)
mysql> rename table X to x;
ERROR 1050 (42S01): Table 'x' already exists
mysql> rename table `X` to `x`;
ERROR 1050 (42S01): Table 'x' already exists
mysql> alter table `X` rename `x`;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.01 sec)
mysql> alter table X;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.00 sec)
mysql> alter table X engine=innodb;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.00 sec)
To do this you must drop recreate table, or temporarily set lower_case_table_names=1:
Database changed
mysql> show global variables like '%case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql> rename table X to x;
ERROR 1050 (42S01): Table 'x' already exists
mysql> rename table `X` to `x`;
ERROR 1050 (42S01): Table 'x' already exists
mysql> alter table `X` rename `x`;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.00 sec)
mysql> alter table X;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| X |
+-------------+
1 row in set (0.00 sec)
mysql> alter table X engine=innodb;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+-------------+
| Tables_in_x |
+-------------+
| x |
+-------------+
1 row in set (0.00 sec)
sh-3.2# ls -la
total 32
drwx------ 4 _mysql staff 136 Jul 1 15:20 .
drwx------ 39 _mysql staff 1326 Jul 1 15:19 ..
-rw-rw---- 1 _mysql staff 65 Jul 1 15:17 db.opt
-rw-rw---- 1 _mysql staff 8556 Jul 1 15:20 x.frm
Suggested fix:
Allow quoting, renaming of tables, or mention in documentation that after create, you cannot rename the table to change case without recreating said table or renaming with alter table engine= after changing the setting to 1.
