MySQL Bugs: #55669: Windows converts table names to lowercase

Bug #55669	Windows converts table names to lowercase
Submitted:	1 Aug 2010 4:01	Modified:	20 Jun 2013 19:11
Reporter:	Zach Rattner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2	OS:	Windows (7 (32-bit))
Assigned to:		CPU Architecture:	Any
Tags:	case sensitivity, filename, windows

Description:
Table and column names created on Windows machines are converted to lowercase when saved, which causes issues when dumps are loaded on Linux machines.

How to repeat:
1) Create a table in the SQL Editor using CamelCase table and column names. 
2) Click "Apply" to execute the generated query.
3) Right-click the Object Browser int the left pane, and choose "Refresh All".
4) Expand the Tables folder and note that the table name appears to have been converted to lowercase. 
5) Expand the table and note that the column names appear to have been converted to lowercase. 
6) Open the Server Administrator and go to the "Data Dump" tab. 
7) Select the schema that the table made in step 2 is stored in.
8) Choose "Export to Backup Project Folder".
9) Choose "Start Export".
10) Once the export finishes, copy the dump to a Linux machine. My machine used 64-bit Ubuntu 10.04.
11) On the Linux machine, open the Server Administrator.
12) Go to the "Data Dump" tab, and select the "Import from Disk" sub-tab.
13) Choose "Import from Backup Project Folder" and locate the dump folder copied in step 10.
14) Click "Start Import" and wait for it to finish.
15) Go to the SQL Editor on the Linux machine.
16) Use the Object Browser to see that the table names and columns use only lowercase letters.
17) Attempt to run a query using CamelCase table and column names, and note that the query fails.

Suggested fix:
Make table and column names display as they were entered on Windows machines.

I think this manual page, http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html, explains the results you've got.

Valeriy, just because the directories are case sensitive doesn't mean it should convert capital letters to lowercase. Shouldn't there be some sort of warning if a user tries to save a table with capital letters when lower_case_table_names is set to to 1? If this behavior is expected as dictated by the documentation, then the UI should reflect that.

I agree. This is a reasonable feature request for Workbench: when lower_case_table_names is set to 1 on target MySQL server and upper case letters are used in any identifier Workbench should warn user that case will be lost.

this bug is still around, and very annoying indeed!

I registered just to say that this bug happens also with phpmyadmin on a Xampp install. really annoying, lost half a day figuring out why my WordPress site wasn't working after a migration

Here is a variant of the same bug, that is specific to the MySQL Workbench tool.

Steps to Reproduce:
1) On a Windows MySQL Server, stop the MySQL service.
2) change the value of the lower_case_table_names in the my.ini file to 2 (which means you can use mixed-case on a Windows system for database names and table names)
3) Save this change to the my.ini file
4) Start the MySQL service again in Windows.
5) In MySQL Workbench, open a connection to the local MySQL server (it doesn't matter who you log in as, since this example use the "test" database/schema).
6) In the SQL Editor, set the "test" schema as the default schema.
7) Double-click on the "test" schema (which should put in bold print).
8) Double-click on the "Tables" section of the "test" schema.
9) Create a new table in the "test" schema named "MixedCaseTable"
10) Add a field called "Id" to the "MixedCaseTable", and set it as the Primary Key. Make sure you clear the default value for this field, since by definition, primary keys are "not null".
11) Refresh the schema.
12) Right-click on the table "MixedCaseTable" in the "test" schema.
13) Select "Alter Table"
14) When the "Alter Table" window opens for table "MixedCaseTable", it will have "mixedcasetable" on the top line (in all lower-case).
15) Add a new field to the table named "field2".
16) Apply your changes to the table.
17) MySQL workbench will crash.

Note that if after step 14 if you change the capitalization of "mixedcasetable" back to "MixedCaseTable" and then follow steps 15 and 16, an error will be thrown, and no changes will be make to the table structure.

The problem is this:
If lower_case_table_names has a value of 2 on a Windows MySQL server, then MySQL Workbench incorrectly converts a mixed-case table name to lower case when opening an "Alter Table" window. Changing the case back before applying the changes produces an error, and no changes are made to the table at all. Ignoring the case in the "Alter Table" window and applying other changes to the table results in three things:
1) The table name is converted to lowercase.
2) MySQL Workbench locks up and crashes.
3) The other changes are made to the table.

Basically, MySQL Workbench incorrectly handles case-sensitive database and table names when MySQL Server (for Windows) has a lower_case_table_names value of 2 and database or table names have mixed-case capitalization.

Original report is not a bug, as this is how the server works. Later posted comment looks like a bug, but it is unfortunate that it was posted to this same report...

Duplicate of bug #68208

Actually duplicate of bug #68210

If you set the lower-case-table-names = 0 workbench works fine with both type of chars lower or upper case