How to configure MySQL and MariaDB table name case sensitivity

Table-name lettercase mismatches are a common source of cross-platform breakage in MySQL and MariaDB. A schema created on a case-insensitive filesystem can appear to work even when application queries mix capitalization, then fail after moving to a case-sensitive host or after restoring data onto a different platform.

Both servers map database and table identifiers to filesystem objects in the data directory, so the underlying filesystem’s case rules matter. The lower_case_table_names server option controls how table and database names are stored on disk and how name comparisons are performed during lookup.

Defaults differ by platform (0 on Unix-like systems, 1 on Windows, 2 on macOS), and the chosen mode directly impacts portability and collision risk. On modern MySQL (8.0+) and MariaDB, lower_case_table_names is an initialization-time setting; changing it after system tables exist is not supported and can prevent startup, so switching modes requires a dump-and-reload or rebuilding the instance.

1. Decide the target lower_case_table_names value for the instance.

   ^Value^Storage and lookup behavior^

   0	Store database and table names using the lettercase from the CREATE statement; comparisons are case-sensitive.
   1	Store database and table names in lowercase; comparisons are not case-sensitive.
   2	Store database and table names as declared; comparisons are not case-sensitive on lookup.

   2 works only on case-insensitive filesystems, and may still force lowercase storage for some objects depending on engine/version. Platform defaults are 0 (Unix), 1 (Windows), 2 (macOS).

   MySQL 8.0+ prohibits changing lower_case_table_names after server initialization, and MariaDB treats it as a database initialization parameter.

2. Read the current lower_case_table_names value from SQL.

   $ mysql -t -e "SHOW VARIABLES LIKE 'lower_case_table_names';"
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 0     |
   +------------------------+-------+

   If socket authentication is not enabled for root, use mysql -u root -p instead of sudo mysql.

3. Check for tables that differ only by case before moving to a case-insensitive mode.

   $ mysql -t -e "SELECT COUNT(*) AS collisions FROM (SELECT table_schema, LOWER(table_name) AS table_name_lc, COUNT(*) AS cnt FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') GROUP BY table_schema, LOWER(table_name) HAVING COUNT(*) > 1) AS collisions;"
   +------------+
   | collisions |
   +------------+
   |          0 |
   +------------+

   Any non-zero result indicates a collision risk when switching to 1 or 2, because names that differ only by case become identical at lookup time.

4. Open the server configuration file in a text editor.

   $ sudo vi /etc/mysql/conf.d/lowercase.cnf

   Common alternatives include /etc/mysql/mariadb.conf.d/50-server.cnf and /etc/my.cnf depending on distro packaging.

   Related: How to locate and modify MySQL and MariaDB configuration files

5. Add the lower_case_table_names line under the [mysqld] section.

   [mysqld]
   lower_case_table_names=1

   Changing this value on an existing MySQL 8.0+ or MariaDB instance is not supported without rebuilding the data directory and restoring from backup.

   Switching from 0 to 1 can make uppercase-named tables inaccessible, and may require renaming/conversion before reload.

6. Restart the rebuilt database service.

   $ sudo docker restart sg-mysql-lowercase
   sg-mysql-lowercase

   Service units are commonly mysql or mariadb depending on distro.

   Related: How to manage MySQL or MariaDB service in Linux

7. Confirm the service is running.

   $ docker ps --filter name=sg-mysql-lowercase --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
   NAMES                IMAGE       STATUS
   sg-mysql-lowercase   mysql:8.0   Up About a minute

8. Confirm the active lower_case_table_names value after restart.

   $ mysql -t -u root -p -e "SHOW VARIABLES LIKE 'lower_case_table_names';"
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 1     |
   +------------------------+-------+

9. Use a consistent identifier lettercase within a single SQL statement.

   SELECT * FROM a_table WHERE A_table.id > 10;

   Even on case-insensitive settings, mixed lettercase references to the same identifier within one statement can fail.