Making MariaDB/MySQL Case-Insensitive (Ubuntu on Linux)

MariaDB/MySQL is case-sensitive on Linux, so any mismatch in capitalization between your table or column names and your SQL throws errors. That’s why we usually turn case sensitivity off first.

Checking Whether MariaDB/MySQL Is Case-Sensitive

To check, just run this query:

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

A value of 0 means case-sensitive; 1 means case-insensitive.

lower_case_table_names

The lower_case_table_names system variable determines whether table names, table aliases and database names are compared in a case-sensitive manner, and whether tablespace files are stored on disk case-sensitively.

Note: lower_case_table_names cannot be set at runtime; the service must be stopped to change it.

• Set to 0 (the default on Unix-based systems): table names, table aliases, and database names are compared case-sensitively.
• Set to 1 (the default on Windows): table names and database names are stored in lowercase and compared case-insensitively.
• Set to 2: table names and database names are stored as declared but compared in lowercase. This value is rejected if the server uses a case-insensitive filesystem. If it’s set to 0 and the server uses a case-insensitive filesystem, it’s automatically set to 2 — which happens on macOS with HFS+ or APFS unless you use APFS in case-sensitive mode. If set to 2 and the server uses a case-sensitive filesystem, it’s automatically set to 0 — common on Linux, which almost always uses a case-sensitive filesystem.

Changing lower_case_table_names

First, stop the MariaDB/MySQL service:

sudo service mysql stop

Edit the config file /etc/mysql/mysql.conf.d/mysqld.cnf, find [mysqld] (add it if missing), and append:

[mysqld]
lower_case_table_names=1

Start the service again:

sudo service mysql start

Additional Notes

When lower_case_table_names is 2, the server uses a special mode for case-insensitive filesystems: table names and database names are stored as declared, but compared in lowercase.

If it’s set to 0 while the server runs on a case-insensitive filesystem, it’s automatically set to 2, and this warning is written to the error log:

[Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive

That happens on macOS with HFS+ or APFS, unless you use APFS in case-sensitive mode.

If set to 2 while the server uses a case-sensitive filesystem, it’s automatically set to 0, and this warning goes to the error log:

[Warning] lower_case_table_names was set to 2, even though your the file system '/var/lib/mysql/' is case sensitive.
   Now setting lower_case_table_names to 0 to avoid future problems.

That usually happens on Linux, since it almost always uses a case-sensitive filesystem.