Novice Tips: Hacking MySql case sensitive objects

 

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.

   1: select * from employee limit 5;

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.

   1: select * from Employee limit 5

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)

image

Add “lower_case_table_names=1” under Basic Settings

image

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)

Advertisements

About Sharath

I am a passionate technologist, I have varied interests in Development, Testing and Maintenance phases of PDLC / SDLC. In my leisure I love to play with my daughter. I love eating out, hanging out with friends and reading blogs/articles.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s