How to fix your lower case table name database when you accidentally ran it with a mixed case table name MySQL database server. This will work on Debian / Linux and Mac OS, and perhaps Windows as well.

The situation

If you accept the maintainers' / packagers' default configuration file during an update you will lose settings that the default file has not set, such as lower case table names .

Depending on your application code, you may run in to problems if you don't notice the MySQL configuration has changed and you run your applications against this database server.

In my case, the database migration library created duplicate tables for both applications. One application couldn't start due to this situation. The other could start, but it was now looking at some empty uppercase tables instead of the filled lowercase tables.

The solution

The lower case table names setting

Lower case table names is enabled by setting

lower_case_table_names=1

in the configuration file, such as /etc/mysql/mysql.conf.d/mysqld.cnf on Debian.

When this property is set to 1 MySQL will use lower case table names and convert the table names in all queries to lowercase.

A list of configuration files for your MySQL instance can be found by running: mysql --help | grep .cnf Several paths for configuration files will be listed, in the order that they are read in.

The current value for lower_case_table_names can be found by running: mysqladmin -u root variables | grep lower_case_table_names Replace the username root with the correct username.

Don't enable this yet if you want to keep some of your tables. If you don't care then go ahead and drop the database, stop MySQL, apply the lower_case_table_names=1 setting and restart MySQL.

The steps

We need to remove all tables with upper case names (and mixed case names). After that we want to make sure the database server has lower case table names enabled.

I didn't need to keep the data because the problematic tables were new tables, but if you do you might want to create a new table and copy the data there, for each table that we will remove.

When lower case table names are on, upper case tables can take an extra step to remove if there are foreign key constraints from a lower case table to the upper case table.

• Make sure MySQL is running, with the lower case table names disabled. Comment this out if you have already re-enabled it.
• For every database you have used with the wrong setting:
  • Do show tables;
  • For every uppercase table for which you want to keep the contents, copy it to a temporary table or SQL dump. I did not need this myself so this part stays vague.
  • For every uppercase table, drop table <table name>; . You may have to play with the order you drop the tables in if the tables have rows and key constraints.
• Add lower_case_table_names=1 to the MySQL configuration
• Restart MySQL. For example: /etc/init.d/mysql restart on Debian.

Not working?

We couldn't get this to work for a colleague of mine. Another colleague was sharp enough to ask if they weren't running two different instances of MySQL. The colleague turned out to be running a homebrew MySQL as well as another MySQL. You may be restarting the wrong one. So if you were using launchctl or brew to restart MySQL, try the other one too.