Any Table / Object created in MySQL installed on unix system are case sensitive.
Consider you have a table called “Employee”, in mysql database installed in unix.
The above query will result in “Table / View does not exist” error. The same query will work for the tables in Mysql Database installed on windows Operating System.
For tables in mysql database installed on Unix, the table name in the queries should match exactly to the table defined at the time of creation. (In unix tables names are by default case sensitive.) The below query will retrieve 5 records from the employee table.
This is because in Unix the value of the lower_case_table_names Server variable is set to a value of 0 (In Windows its 1 and Mac its 2). Reset this value to 1 to have the desired behavior.
lower_case_table_names variable set to “0” means the comparisons are case sensitive, “1” means the table name comparisons are not case sensitive, while they are stored with lower case on the disc, and “2” means the table name comparisons happen with lower case and they are stored in database as given.
For more information of server variables, useful information can be found here.
How to change this setting:
1. Find my.cnf (Usually it will be in /etc/mysql/)
2. Edit my.cnf (might need a sudo access)
Add “lower_case_table_names=1” under Basic Settings
3. Restart the mysql server
4. Login to mysql as root and check the status of the settings
a. mysql –user=root –password=<password>
b. show GLOBAL variables like =’lower%’; (Check the status is set to 1)