Enable MySQL case insensitivity for tables names on GNU/Linux
May 2nd 2016
Case insensitivity for MySQL table names is not available out of the box on Linux Systems, like it is on Windows. However, it is possible to enable it by tweaking the MySQL settings.
Let's say that we've just created a new database that contains a table named TEST (with uppercase letters). If we have code that selects the table referencing it with lowercase letters, it will throw an error saying that the lowercase table doesn't exist.
There are a few steps that we need to follow in order to enable case insensitivity for an existing database.
First, we have to dump the entire database to a file (export the database).
We do this by using
mysqldump -u [username] -p -B [database name] > [file]
After that we need to drop the database, we do this using:
DROP DATABASE [database name];
Now, we need to configure Mysql so that case insensitivity is enabled.
The file that needs to be changed is
Add lower_case_table_names=1 in the [mysqld] section, just like in the screenshot below
Close the my.conf file, restart MySQL by running
sudo service mysql restart and import the database again.
mysql -u [user] -p < [file] to import the database from a file.
Let's test to see if everything works as it's supposed to.
Everything works just fine, the former
TEST table is now lowercased and you can access it by either using the lowercase title or the uppercase one.
That's it! I hope you found this tutorial useful.