Tuesday, September 1, 2009

Use uppercase letters for mysql data table names in Windows

When we developing mysql based applications, there is a good probability to developers develop the application on Windows OS and deploy it on a linux server or vice versa. MySQL supports both lowercase and uppercase letters for table names in unix enviroments, but when it came to Windows, the default MySQL configuration does not support upper case letters in table names. This becomes a big headache if you need to restore mysql database backups from windows environments to unix environments ( other way round is not quiet a problem since all table names becomes lowercase in Windows machines) during several times while development.

Today I found a solution for this case insensitvity of table names in MySQL servers on Windows environment. There is a mysql system variable called "lower_case_table_names" and it has set to 1 in default mysql configuration for Windows. 1 means only lower case table names are supported. For gaining the support for Both lower and upper case letters in table names, what you have to do is find relevant my.ini file for your MySQL installation and add the following line to the end of the file.
lower_case_table_names=2
Then restart the MySQL server. Now you will be able to create tables with names which include both upper-case and lower-case letters.

Related MySQL articles,
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names