MySQL table name case sensitivity

Just encountered a fun little issue when trying to move something from a local MySQL server to a ‘Prod’ server. The difference in architecture being that dev MySQL was running on OSX whereas the ‘Prod’ MySQL was running on Linux (debian).

The issue was that a lot of my queries/views/procedures were failing on the Linux box due to non-existence of tables. (When they definitely existed.)

The problem was how different operating systems treat case sensitivity in tables. In short, on Windows and OSX table names are case insensitive. In Linux and other Unix based platforms, they are case sensitive.

The way to control this is through a MySQL variable called ‘lower_case_table_names’. You can check current status by issuing the following SQL

show VARIABLES where variable_name='lower_case_table_names'

The options here are:

0 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.
1 - Table names are stored in lowercase on disk and name comparisons are not case sensitive.
2 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive.

So ultimately, if you want to move between Windows/OSX/Linux with no issues, you need 1 or 2. By default, Linux is 0, hence the issues.

The way to fix this on my Linux box (debian/raspbian) on RPI3 was:

<br /> sudo nano /etc/mysql/my.cnf<br /> lower_case_table_names=1<br /> sudo /etc/init.d/mysql restart<br />

Add the second line to the bottom of the my.cnf. Bear in mind this won’t fix it for pre-existing tables, so you either to manually rename all your existing tables or export/drop/import.