Posted In: Database, MySQL

MySQL table names are case sensitive

I have own product called www.labworkflows.com. After moving application from my Windows local environment to Linux CentOS server I observed lot of my queries stopped working.
e.g. select * from LABTESTS works on Windows default MySQL installation but dis not work on CentOS.

After digging more into it observed that scripts that I generated through Workbench has lowercase table names and some of my queries has it upper case. Following solution being provided on stackoverflow and MySQL.

If set to 0, table names are stored as specified and comparisons are case sensitive.
If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.
If set to 2, table names are stored as given but compared in lowercase.
This option also applies to database names and table aliases.

SET lower_case_table_names=1;

The case sensitivity of the underlying operating system plays a part in the case
sensitivity of database, table, and trigger names.
This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.
Although database, table, and trigger names are not case sensitive on some platforms,
you should not refer to one of these using different cases within the same statement.
The following statement would not work because it refers to a table both as my_table and as MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

This solution may not work in a big organization as they will not do database setting change for particular application. So only way I see is to make sure to take care of it in your application. Make sure all your table names in queries are in lowercase and your table creation scripts are also in lowercase.

by , on August 21st, 2017

  • Categories