MySQL case sensitivity and table names in Linux

I recently ran into an interesting issue where a project I worked on in CodeIgniter was using a mix of upper and lower case names to query the same table (this project is older and had been worked on by several people over the course of a few years):

SELECT * FROM CMS_email...
SELECT name FROM cms_email...

The issue is that certain file systems are case sensitive, and since MySQL tables are accessed and stored in a file and folder structure, the underlying file system’s case sensitivity plays a role in how well this code works.  Interestingly, when I downloaded the tables via Sequel Pro and imported them to my local VM server, the table names were all converted to lowercase as well.  Either way, it would be a conflict.

Typically, Windows file systems are not case sensitive, so the above code will work out of the box.  On most Unix based systems, however, the file systems are case sensitive, which means we get MySQL errors when we attempt to run this code in a Ubuntu box, for example.  Since I run an Ubuntu VM with Vagrant, I needed to either:

  1. Modify all table calls to be lowercase, and then modify production table names to match, or modify all queries on my local to match production table names, then manually update table names on my local VM instance.  Doable, but somewhat risky and complicated.
  2. Find another way to make this work with MySQL settings (read: lazy, quick)

Option 2 is doable with the MySQL configuration variable lower_case_table_names.  This variable tells MySQL that no matter what the calls made to the database are, everything is converted to lowercase.  This makes it easier to work with  my all lowercase tables that were automatically formatted upon import, and I don’t have to parse through tens of thousands of lines of code to update queries.

Important note: before using this, we need to make sure our table names are all lowercase, since internally MySQL will still be case sensitive – this just modified the input to assume all lowercase!  This works because my imported tables were auto converted to lowercase names.

All we need to do is find our configuration file (my.cnf) and add an entry to turn this system variable on.  On Linux, this is usually:

/etc/mysql/my.cnf

However, this all depends on how you install MySQL. MySQL configuration files are a topic on their own.  This article shows some advanced ways to locate it.

 

Once we have located the file, we can open it with our text editor and find the section titled [mysqld].  There will be a section called Basic Settings, and at the end of this settings block we will add our entry:

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
lower_case_table_names=1

Once we are done, we can save the file then restart our MySQL server, in my case using the command:

sudo service mysql restart

And now you can query the table

cms_email

with the query

SELECT * FROM CMS_Email