Bug #30777 RENAME Table fails on Windows when

• Submitted: 4 Sep 2007 5:33
• Modified: 4 Sep 2007 6:19
• Reporter: Harrison Ulrich
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.45-community-nt
• OS: Windows (XP)
• CPU Architecture: Any
• Tags: rename, table

[4 Sep 2007 5:33] Harrison Ulrich

Description:
I am on Windows. I changed the startup variable lower_case_table_names to 0. I tried to rename a table and the rename fails with a "Table already exists".

I am, however, able to change the name to something totally different, then rename it back and the case sticks. 
I did stop and restart the service. 
I am using the MySQL Administrator.

Example:

ALTER TABLE `db`.`users` RENAME TO `db`.`Users`;

produces:
MySQL Error Number 1050
table 'Users' already exists

How to repeat:
Open up a MySQL Server. Set the lower_case_table_names variable to 1 (default for Windows). 

Create a table (it will be all lowercase).

Change the lower_case_table_names to 0.

Try to rename your table, only adjusting the case.
example: ALTER TABLE `db`.`users` RENAME TO `db`.`Users`;

[4 Sep 2007 6:19] Hartmut Holzgraefe

lower_case_table_names=0 only works on case sensitive file systems,
the Windows file sytems (FAT; NTFS) are not case sensitive though
so it is not possible to have two files "User.ext" and "user.ext"
on them at the same time.