MySQL Bugs: #68260: setting lower_case_table_names variable to 1 prevents access to tables

Bug #68260	setting lower_case_table_names variable to 1 prevents access to tables
Submitted:	4 Feb 2013 9:27	Modified:	5 Feb 2013 7:14
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.5.29	OS:	Linux (Centos 6.3)
Assigned to:		CPU Architecture:	Any
Tags:	lower_case_table_names, system variable

Description:
As documentation says it is good pattern to change value of lower_case_table_names to 1 in Linux for innodb compatibility and etc.:

[link]http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html[/link]

Default value is 0:
[code]
mysql> select @@lower_case_table_names;
	+--------------------------+
	| @@lower_case_table_names |
	+--------------------------+
	|                        0 |
	+--------------------------+
[/code]
So, in my world database there were 3 tables with upper_case names:

[code]
mysql> show tables;
	+-----------------+
	| Tables_in_world |
	+-----------------+
	| City            |
	| Country         |
	| CountryLanguage |
	+-----------------+
[/code]

i changed value of lower_case_table_names to 1 from my.cnf. i put it under [mysqld]. then restart the server. and for test purposes try to drop world database:
[code]
mysql> drop database world;
ERROR 1010 (HY000): Error dropping database (can't rmdir './world', errno: 39
[/code]

ERRNO: 39
[code]
[root@localhost ~]# perror 39
OS error code  39:  Directory not empty
[/code]

Then i try to drop tables. i wrote in different letter cases one lower and one upper:
[code]
mysql> drop table city;
	ERROR 1051 (42S02): Unknown table 'city'
	 
	mysql> drop table City;
	ERROR 1051 (42S02): Unknown table 'city'
[/code]
Even i cant select:
[code]
mysql> select name from City where countrycode='AZE';
ERROR 1146 (42S02): Table 'world.city' doesn't exist
mysql> select name from city where countrycode='AZE';
ERROR 1146 (42S02): Table 'world.city' doesn't exist
[/code]

But again "show tables" shows that there is a table City:
[code]
mysql> show tables;
	+-----------------+
	| Tables_in_world |
	+-----------------+
	| City            |
	| Country         |
	| CountryLanguage |
	+-----------------+
[/code]

How to repeat:
create a table with upper case when lower_case_table_names=0.
Then change lower_case_table_names to 1.

Suggested fix:
There is not a normal fix. Because i dont understand what happened. Why after changing lower_case_table_names to 1 i cant access my previous tables?

After this i set lower_case_table_names to 0 as it was Default for my Linux.
Restart the server and after this i can drop my world database:

[code]
mysql> drop database world;
	Query OK, 3 rows affected (0.11 sec)
[/code]

So with this bug(if it is a bug) i suggest that you must set lower_case_table_names to 1 immediatly after fresh MySQL installation..

Please refer to http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

For lower_case_table_names=1, "MySQL converts all table names to lowercase on storage and lookup". This is consistent with what you see, "City" gets converted to "city" when doing the lookup. Hence you cannot access your old table.

There is no reason why your "City" table should be renamed "city" automatically. Indeed, with lower_case_table_names=0, you could start off with two tables, one named "City" and the other named "city", and then it is not clear how renaming should work.

The manual states:

"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."

So this is not a bug, in my opinion.

As you say and as documentation says lower_case_table_names=0 MySQL store identifier name as you write in create statement.
So if i create City with upper case it will store it as City. and when you want access City as city the error arise.(while lower_case_table_names=0 )

But
with lower_case_table_names=1 City must be converted to city but as my report shows there is no conversion and even i cant access my tables. So if there is no conversion why i can not access my tables? simple logic :)

i read the documentation. If i have 1000 tables and i want to set lower_case_table_names=1 i must manually change them to lowercase or dump and restore.. is it convenient?

let it is not a bug. but it is still very dangerous in production setting this variable to 1 and not access tables. So there must be a hint or a caution section in documentation:
"You can not access your previous data in Linux if you set lower_case_table_names to 1 without manually renaming or dumping your data. "