MySQL table names forced to lower-case, and table_exists function case-sensitive ?

[eluser]aidehua[/eluser]
Wow, this gets fiddly.

I'm sure I'm not the only person out there who is developing on Windows for eventual deployment to Linux, and who has a fondness for CamelCasing table names. So here's what I've found:

Read this page to find out about the lower_case_table_names system variable.

The default value of lower_case_table_names on Windows is 1: "Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases."

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. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. 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.

- Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

I've gone for the second option, to set my Windows system to lower_case_table_names=2.

Easy? Not quite yet.

In theory you can set this via the my.ini Setup tab in WinMySQLadmin. But on my system at least (Windows XP, MySQL installed with XAMPP), this did not work.

So, to here: http://www.phpbuilder.com/board/showthre...t=10341962. Look at the 15th post. If running bradgrafelman's suggestion there works, then move on to the 17th post (here).

He suggests doing a registry edit.

I did something slightly different, which also seems to work:

I went to C:\xampp\mysql\bin (the path may be different depending on your system - if you can't find it, then the MySQL registry key as noted in bradgrafelman's 17th post (see above) will help you find it).

Open the my.ini file.

In the section which begins

Code:

# The MySQL server
[mysqld]

Add the following line:

Code:

lower_case_table_names = 2

Save the file, then re-start your MySQL service.

This seems to be working for me.