mikewhobikes

After installing a content management system on my local machine (running WampServer) I attempted to deploy it to the live web server. The files copied successfully over FTP and I used phpMyAdmin to export the local database and import it onto the remote server.

Everything should work, right? Wrong. Instead of seeing a duplicate copy of what I had on my local machine I received an error. Upon closer inspection I found that installing the CMS on the remote server produed camel case table names (ie. ‘myDatabaseTable’) while a local install gave me lowercase names (ie. ‘mydatabasetable’).

What’s going on here?

It turns out that while UNIX is case sensitive, Windows is not. Combine this with the fact that MySQL — when installed with WampServer, at least — converts all table names to lowercase and there’s clearly a problem here.

The application tried to create ‘myDatabaseTable’ and was given ‘mydatabasetable’ instead. A query will work fine on windows (where ‘myDatabaseTable’ is equivalent to ‘mydatabasetable’). However, things change when the database is copied over to a case-sensitive UNIX server. As far as MySQL is concerned, the tables being queried don’t exist!

A Simple Solution, Really

Fortunately, there’s a MySQL setting to alter this behaviour so that table names are not modified. Here’s how to do it:

1. Open the ‘my.ini’ configuration file. This can be found under the MySQL menu in WampServer.
2. On a new line at the end of the file, paste: lower_case_table_names=2
3. Restart all services for the change to take effect.
4. Reinstall the application. The created tables will retain their intended case, and you should be able to export this database onto the live server without incident.

I find it surprising that I haven’t run into this before or heard of others with the same problem; it seems like it should be quite common. Is there a good reason to have MySQL alter the table names by default? Should developers be aware of this and use all lowercase table names? Maybe so.