Tables Failed to Be Found After Case-Sensitivity Setting Changes for RDS for MySQL

Scenario

After the RDS for MySQL parameter lower_case_table_names was set to case sensitive, a table containing uppercase letters was created. However, after the parameter was set to case insensitive, the table containing uppercase letters, such as tbl_newsTalking, cannot be found.

Case: When a backup is restored to a new instance, restoration will fail if the parameter case-sensitive setting of the new instance is different from that of the original instance.

Solution

1. Change the value of lower_case_table_names to 0, indicating that table names are case sensitive.
2. Reboot the database.
3. Change uppercase letters in table names to corresponding lowercase letters.
4. Change the value of lower_case_table_names to 1, indicating that table names are case insensitive.
5. Reboot the database, or export the entire database and import it again.

• The names of databases, tables, and variables, and table alias must be case sensitive.
• Column names and aliases are always case insensitive. Field values are case insensitive by default.
• For RDS for MySQL 5.7, you can specify case sensitivity for table names when creating an instance on the console or using APIs, or set the lower_case_table_names parameter after an instance is created.
• For RDS for MySQL 8.0, you can only specify case sensitivity for table names when creating an instance on the console or using APIs.