Why MySQL Table Names Fail on Linux: Mastering Case Sensitivity and lower_case_table_names

MySQL treats identifier case differently on Windows (case‑insensitive) and Linux (case‑sensitive). When a table name's case does not match the actual name on a case‑sensitive system, queries fail with errors such as "Table 'xxx' doesn't exist".

【Strong】Table and column names must use only lowercase letters or digits; numbers cannot start a name, and double underscores cannot contain only digits. Changing column names is costly, so choose names carefully.

Note: MySQL on Windows does not distinguish case, but on Linux the default is case‑sensitive, so database, table, and column names should avoid uppercase letters.

Correct example: aliyun_admin , rdc_config , level_3_name Incorrect example: AliyunAdmin , rdcConfig , level3Name

A real‑world issue occurred when a table was created as tb_sutyho (lowercase) but referenced in code as tb_sutyHo (uppercase H). The query worked on Windows but failed on Linux.

lower_case_file_system

This variable describes whether the underlying file system treats file names as case‑sensitive (OFF) or case‑insensitive (ON). It is read‑only.

lower_case_table_names

This static variable can be set to 0, 1, or 2:

0 – case‑sensitive (default on Unix/Linux)

1 – case‑insensitive (default on Windows)

2 – case‑insensitive but stores names as given (default on macOS)

On Windows the default is 1; on macOS the default is 2; Linux does not support value 2 and forces 0.

To view the current settings:

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+

On a Linux server the output is:

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

Changing lower_case_table_names requires editing the MySQL configuration file ( my.ini on Windows, my.cnf on Linux) and restarting the service.

Common pitfalls: Setting lower_case_table_names=0 and creating tables with uppercase letters, then switching to 1, will make those tables invisible. Likewise, tables created under 0 become inaccessible when the value is changed to 1.

Solution: If you need to switch from 0 to 1, rename existing tables to lowercase (using RENAME TABLE) or export and re‑import the database after converting names.

Practical steps

1. With lower_case_table_names=0, create tables using the desired case.

CREATE TABLE `Student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW TABLES;
+----------------+
| Tables_in_aflyun |
+----------------+
| Student         |
+----------------+

2. Change the variable to 1 and restart MySQL. After the change, queries for Student or student will both report "Table does not exist" because the original mixed‑case table is no longer visible.

SELECT * FROM Student;
1146 - Table 'aflyun.Student' doesn't exist
SELECT * FROM student;
1146 - Table 'aflyun.student' doesn't exist

3. To avoid such issues, keep table and column names lowercase during development, or enforce lower_case_table_names=0 in the development environment to catch case‑sensitivity problems early.

Summary

Understanding MySQL's case‑sensitivity rules and the lower_case_table_names setting helps prevent deployment failures across operating systems. Set lower_case_table_names=0 in development to enforce strict naming, and convert existing uppercase identifiers before changing the setting in production.

Reference materials:

lower_case_file_system documentation

lower_case_table_names documentation

MySQL case‑sensitivity article