-
Title
Error running queries when table name capitalization do not match between query and database table. -
Description
When running a query in the Toad for Data Analysts Editor, the following error occurs:
Error, Table 'XXX.XXX' doesn't exist
In this example the table name was written in mixed case while the table name registered in MySQL was all capital letters.
For example:
select count(*) from Bugs
[Does not work]
select count(*) from BUGS
[Works] -
Cause
MySQL database is set to 'Case Sensitive'.
-
Resolution
Set the lower_case_table_names to zero in the my.ini file:
1. Close Toad.
2. Browse to C:\Program Files\MySQL\MySQL Server 4.1 (depending on the version running.) and open the my.ini file.
3. Change the lower_case_table_names value to 0.
4. If the setting is not found in the INI file, add it to the end of the my.ini file, as such:
lower_case_table_names=0
5. Restart the MySQL service testing.
NOTE: It is possible to set this value to 2 if desired, which "MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive."