MySQL mysqldump Failure Caused by lower_case_table_names and Missing .frm Files: Diagnosis, Simulation, and Remedies

**Problem Description**: A client needed to migrate a ~40 GB MySQL database using mysqldump , but the dump failed with "table doesn't exist" errors. Inspection revealed that the database directory lacked a db.opt file, the target table's .ibd file was missing, only a .frm file remained, and the MySQL variable lower_case_table_names was set to 1.

**Analysis**: MySQL documentation shows that on Unix platforms the default lower_case_table_names is 0 (case‑sensitive). The problematic table was originally created when the variable was 0, so its physical files used uppercase letters. After the variable was changed to 1, MySQL matches identifiers in lowercase, causing a mismatch during a subsequent DROP DATABASE operation, which left the .frm file orphaned and generated error‑log entries.

**Scenario Simulation**: A test environment (CentOS 7.5, MySQL 5.7.25) was set up with the following steps: Create a test database and a table named Test_table while lower_case_table_names=0 . Modify the MySQL configuration to lower_case_table_names=1 and restart the service. Attempt to drop the test database under the new setting. Inspect the filesystem and mysql‑error.log for remnants. Run mysqldump to reproduce the original error.

**Findings**: The experiment confirmed that (1) Linux file systems are case‑sensitive, so with lower_case_table_names=0 table files preserve case; (2) with the variable set to 1, MySQL forces lowercase matching, which prevents it from locating and deleting uppercase .frm files during a drop, leaving them behind; (3) the .ibd files are removed regardless of case.

**Solution**: Since the residual .frm files cause the dump to fail, the simplest workaround is to exclude the affected database from the backup operation.

**Operational Recommendations**: When performing database migrations or restructurings, pay close attention to the lower_case_table_names setting and the case of database and table names. Two practical scenarios are suggested: Change lower_case_table_names from the default 0 to 1 after converting all database and table names to lowercase, then restart MySQL. Rename uppercase tables using RENAME TABLE and rename databases by dumping the data, recreating the database with a lowercase name, and re‑importing the data.

**Acknowledgments**: Thanks to the DBA experts who assisted in the investigation.