Few months back I was working on a task in which I had to construct few SQL queries to fetch specific data and display on the GSP pages. I fired few queries(table names were given in lower case) but it failed. It showed an error that there is no such table with the name I mentioned in the SQL query. The reason was that the table name was in Upper Case but I mentioned the table name in lower case in the query. It was strange for me as I never encountered case sensitive issue with MYSQL while working on PHP projects that I developed on Windows.
I searched and found that in Windows by default MYSQL table name is case insensitive and on Unix its case sensitive.
It means that if you have stored the table JFT_EMPLOYEES (upper case) in your database, the select query:
select * from jft_employees
will not work and will return a message like “table jft_employees doesn’t exist”.
In order to prevent this problem you need to set the mysql variable lower_case_table_names=1 in /etc/mysql/my.cnf file. In this way the mysql server will store the table in the file system using lower case.
Here are the steps which you need to follow to configure MYSQL as case insensitive on Linux:
1. Check the status of lower_case_table_names by logging into Mysql and then typing :
$ show variables
2. Edit the file, put the variable lower_case_table_names=1 and save it.
$ sudo gedit /etc/mysql/my.cnf
[mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # lower_case_table_names=1 user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr .................... ........................... .................................
3. Shutdown the mysqlserver:
$ service mysqld stop;
4. Start the mysqlserver:
$ service mysqld start;
5. Check the new status of lower_case_table_names again by typing:
$ mysqladmin -u root -p variables
Remember that you need to drop your database and then re-create it after you have applied my.cnf new settings because table names are stored as files(as .frm format) so it will be created in lower case only after the lower case setting is correctly applied.
You can test it by running a SQL query like $ select * from jft_employees in case you have stored JFT_EMPLOYEES upper case.
That was all!