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

File Permissions for Apache (Ubuntu, Linux)

I find myself Googling this all the time – setting permissions and users/groups for the /var/www folder of a LAMP install.  Also, I break down some of the terminal commands a little.  In my experience, many web developers tend to touch the command line every so often but never get really comfortable understanding what they are actually doing. So here we go, for my benefit and yours:

Find your main user name (the one you will SSH and SFTP with).  For AWS (my most commonly used) with an Ubuntu EC2 install, it is ‘ubuntu‘ and for Vagrant boxes it is ‘vagrant.

We need to add this user to the www-data group so they can share permissions.  Apache runs in the www-data group, and Apache’s ‘run as’ user will be the one creating and executing files within the /var/www folder (read: uploads, online edits, etc).  We also use sudo with all this to avoid any permissions errors before setup is complete.

The command usermod allows us to change users’ settings.  The flag -a means ‘append’ and must be used in conjunction with -G (list of groups).  Then we tell the -G what groups, then what user we are modifying;

sudo usermod -a -G [group-name] [user-name]

For AWS with Ubuntu:

sudo usermod -a -G www-data ubuntu

Next, we need to change group ownership of the /var/www folder (and everything inside it) to www-data (so we can all share permissions within the group).  the command chgrp performs this task, with a flag -R to mean recursively apply this group, followed by the folder we are applying the group to:

sudo chgrp -R www-data /var/www

Finally, set permissions on folders and files for everything in the /var/www folder. We will use 644 for files and 755 for directories (this is standard). If you need special permissions, run these commands first, then apply special permissions to whichever files and directories need it after the fact.

We use the command chmod to perform this action (see link – we will use numeric permissions, as I prefer this method).  However, chmod has a caveat – it has a -R recursive flag, but we want to apply different permissions depending on whether we are working with a folder or a file. Chmod does not have the ability to differentiate between files and folders, so instead we use the find command in conjunction with the exec command.

Reading from left to right, the logic is to find everything in /var/www that is a particular type (a filter flag for find, –type, followed by d for directory and f for file), the execute an arbitrary (inline) command on it.  We will execute chmod, setting our permissions accordingly.

sudo find /var/www -type d -exec chmod 755 "{}" \;
sudo find /var/www -type f -exec chmod 644  "{}" \;

That last bit with the quotes and curly braces tell -exec that we are working on the current path, which will change while the find command loops and executes the chmod command on every search result.  So say we find index.php – exec then runs

chmod 644 index.php.

Since the entire command is prefaced with sudo, it will actually run:

sudo chmod 644 index.php.

There you have it, permissions are ready to go.