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.

Setting up a Vagrant Environment for PHP/MySQL development

Why Vagrant?  What’s the point?

In my industry, we are often led to believe that the “newest” or “most cutting edge” development tools area must-do, integral part of developing good websites.  Unfortunately, most of the time we end up spending more time learning new tools or workflows than actually getting any work done, and it tends to dissuade us (or me) from branching out and learning new techniques.  I may be tempted by newer, faster ways of doing everyday tasks (SASS compilation, JavaScript minification, etc), but only when I have time and patience.  My line of work is mostly one-time-client-based: fast-moving, short-term projects often stacked in three or four week intervals – so once I start a project, there’s little to no time to try out a new workflow or technology before the deadline.

Having said that, I do stumble across the occasional “must-have” tool (depending on the project), like CodeKit, that truly does improve productivity with minimal impact on my current workflow.

Enter Vagrant – a self-contained, configurable virtual machine for loading and testing websites or applications.  Having run MAMP without problems for years, I figured Vagrant would be cool but not useful enough to incorporate.  But it is, and here’s why:

  1. It keeps the same server environment wherever the project goes
  2. It’s dynamic, and easy to configure to match whatever production server environment I need
  3. It’s fast to boot up, fast to shut down, and fast to erase (one command for each)
  4. It maintains its settings and packages outside the virtual machine, so I don’t waste space with several different Ubuntu environments set up for specific purposes.
  5. FREE

You may be wondering why you would want to run a virtual machine for local server testing if you have *AMP(P) software already running.  The thing is, each installation of Apache, MySQL, and PHP can differ in subtle but important ways.  PHP versions may be different, Apache might not have the same configurations on different machines, and so on.  And in many places, every developer and designer has a local server stack running on their machine to test and preview websites.  This means that code can work on one machine, but not another.  Code may work on all MAMP installs in the office, but fail on an Amazon server (I have had this happen before).  Git takes care of maintaining the same code base across multiple machines, but doesn’t guarantee person A will see the same results as person B.

We could try to include a Virtual Machine image within each repo, but imagine the overhead in updating those files (averaging several gigabytes).

What if, instead, we could just push a flat configuration file for a server, have some software read it, build a virtual machine environment matched to a project, and boot it up?  That’s where Vagrant comes in.

Now, I am by no means a server guru.  I have difficulty setting up virtual hosts, remembering the correct commands to restart Apache, and have to keep a cheat sheet for everything I do over SSH.  And yet, Vagrant was a breeze to set up.  So let’s get into it.  All it takes is some basic knowledge of the command line or terminal app, and you can pick that up as you go.

Installing Vagrant

Head over to Vagrant’s Website and download a copy.  Once you follow the installation procedures, nothing happens.  Yet.

You also need to install virtualization software.  I run VirtualBox for everything here.  All you have to do is have it installed for Vagrant to work with it.  This software basically creates, from system image files, a virtual computer running in it’s own shell, isolated from your host system.  Vagrant’s virtual machine runs in the background, so you don’t actually “see” the virtual machine running.

After that, we are set to start booting up a Vagrant server.

Let’s create a project first.  I’ll create a project folder called Vagrant for the hell of it.  I’ll put it in my Development directory where all my other projects are.  Vagrant automatically uses this directory as the root for your virtual machine user – in essence, a synced folder.  We will see more of that after setup.

Once the project directory is created, I’ll navigate to it via command line / terminal:

Terminal:

cd ~/Development/Vagrant

Windows CMD:

cd C:/users/Calvin/Development/Vagrant

Once I’m there, I will run the vagrant init function. This function takes one argument, where we must specify what type of server (OS, OS version) we want to initialize.  Vagrant refers to this as a Box. Typically, this can be hashicorp/precise32, which is an Ubuntu (Linux) server (Box).

vagrant init hashicorp/precise32

It will grab the latest version of this OS and store it outside of the project folder – so you can have one root machine from which to build different virtual server evironments.

Now we run the boot up function:

vagrant up

And bam!  The server is running.  It may not look like it, but it’s there! To check, run

vagrant status

And it should tell you that the server is running.  You can now SSH into it* using

vagrant ssh

*Note to Windows Users – you will need to install an ssh client to use this command.  Git has one built in, but there are many others out there.

From here, you are using the Linux terminal to perform operations. To test that the folder you are currently in on the Vagrant server is the same as the project folder, run a simple touch command to create a file

touch index.html

Now, outside of the terminal window, navigate to that folder in Finder/Windows. You should see your index.html file sitting there.

But this Virtual Machine is just an operating system – it’s not running Apache, or MySQL, or PHP yet.  We need to install and configure them. However, we don’t want to set it up for just this instance – we can use Vagrant’s configuration methods to make sure that whenever someone clones our source code, they can grab the configuration file for the virtual server and have the exact same environment we are running locally.

Configuring your Vagrant Virtual Machine

Vagrant configuration files are called Vagrantfile, and they are located in the project’s root directory.  Editing them is simple using a text editor.  Loading them into the Vagrant machine is called Provisioning, and provisioning only happens on the first run of a Vagrant project.  To force a machine to re-provision itself, use the command

vagrant reload

or

vagrant reload --provision

If it doesn’t reload properly.

To make a quick, LAMP-stack server, I first created a bash script called bootstrap.sh in the root project directory that checked a few things and installed a LAMP stack.  I then called that script to be run as part of the provisioning configuration.  This comes from a great article Getting Started With Vagrant on This Programming Thing.

bootstrap.sh:

#!/usr/bin/env bash

sudo debconf-set-selections <<< 'mysql-server-5.5 mysql-server/root_password password rootpass'
sudo debconf-set-selections <<< 'mysql-server-5.5 mysql-server/root_password_again password rootpass'
sudo apt-get update
sudo apt-get -y install mysql-server-5.5 php5-mysql apache2 php5

if [ ! -f /var/log/databasesetup ];
then
    echo "CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'database_password'" | mysql -uroot -prootpass
    echo "CREATE DATABASE vagrant_database" | mysql -uroot -prootpass
    echo "GRANT ALL ON vagrant_database.* TO 'database_user'@'localhost'" | mysql -uroot -prootpass
    echo "flush privileges" | mysql -uroot -prootpass

    touch /var/log/databasesetup

    if [ -f /vagrant/data/initial.sql ];
    then
        mysql -uroot -prootpass vagrant_database < /vagrant/data/initial.sql
    fi
fi

if [ ! -h /var/www ];
then 
    rm -rf /var/www
    sudo ln -s /vagrant/public /var/www

    a2enmod rewrite

    sed -i '/AllowOverride None/c AllowOverride All' /etc/apache2/sites-available/default

    service apache2 restart
fi

You can copy this code directly into the bootstrap.sh file you use, just make sure of a few things:

  1. The database_user must be changed to a username of your choice
  2. The database_password must be changed to a secure password
  3. The vagrant_database must be named whatever you want

To make this script run at startup, I changed my Vagrantfile to look like this:


Vagrant.configure(2) do |config|
 config.vm.box = "hashicorp/precise32"
 config.vm.provision :shell, path: "bootstrap.sh"
 config.vm.network "forwarded_port", guest: 80, host: 80
end

The last entry before end deals with port forwarding – basically, the port you want to preview the website from outside of the virtual machine.  Many people opt for port 8080 for development purposes, but I prefer the standard port 80.  It’s up to you.

Now that our provisioning and boot file are set up, head back to the project directory and run

vagrant reload --provision

Just to ensure it really reloads the provisioning files.  You should see the Vagrant software outputting quite a bit of downloading and installation commands, and then boot up. Now you are set with a full LAMP stack in a Vagrant Virtual Machine!

From here, just download/clone/etc the latest version of WordPress, put the files in the root of your project, and run the installer.

For more information:

Vagrant Documentation

Github Repo of some great provisioning bash scripts

New WordPress Site

Well, here we are.

In case anyone actually lands at this page, this is a new blog site/sandbox platform for me to experiment and share my developments, be it with web development, philosophy, or maybe a little project or two with my Arduino Uno.

Thanks for stopping by, and stay tuned…