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:
- 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.
- 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:
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
with the query
SELECT * FROM CMS_Email