The Saga of the Case Insensitive MySQL Table Name

It all started when Spring Social was failing to create new users with OAuth. After upping the Log Level of org.springframework.social to “debug” in the logback.xml I was able to see the problem.

This wasn't really the problem, of course, as table “UserConnection” did indeed exist.

For a little background, we're working on a new machine (woo-hoo!) and new install of MariaDB 10. Spring uses a SQL table called userconnection to store info about Social Users. The code is hardwired into the framework. When that Spring code threw “UserConnection doesn't exist” I knew right away we were dealing with a MySQL Table Case Insensitive issue.

Which is where our case insensitive saga begins…

MySQL on Windows is case insensitive. Pretty much everything on Windows is case insensitive. MySQL on Linux is case sensitive, like pretty much everything else in Linux. That's one of the things we love about Linux, but having to use case sensitive table names in MySQL queries is rarely of benefit.

The Fix (In Theory)

Below are the MariaDB 10 configuration files in /etc/mysql. Very similar to MySQL as you see. The fix is to add the following to a .CNF file. I added it to conf.d/mysql.conf

[mysqld]
lower_case_table_names=1

About lower_case_table_names from the MariaDB documentation:

If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.

After you add the lower_case_table_names System Variable you restart the MariaDB mysql Server Daemon.

When MySQL is restarted you can perform the following query successfully.

mysql> select * from userCoNNeCTion;

or maybe not…

If you are STILL getting an error, here's a tip that may save you hours and is the reason for this post. BE SURE THE MYSQL DAEMON SHUTDOWN AND RESTARTED SUCCESSFULLY.

Look at the following sequence below. We add the lower_case_table_names System Variable, restart MySQL and expect our change to take effect. Unfortunately the mysql daemon not shutdown successfully so no changes were ever applied.

You never know if there's a mysql server instance running behind the scenes with the following command.

$ sudo service mysql restart

Or separate mysql stop and start commands either. Assuming the daemon shutdown and restarted successfully should not be assumed. Instead you can either kill the PID or use the following which works pretty well.

$ mysqladmin -u root -p shutdown

Let's see that in action.

A clean burn. No residuals.

The Moral of the Story

When reconfiguring your MySQL Server, always make sure it was properly restarted and that no other instances are running in the backgound.

Posted January 04, 2017 12:03 PM EST

More Like This Post