Tables Failed to Be Found After Case-Sensitivity Setting Changes for RDS for MySQL
Scenario
The RDS for MySQL parameter lower_case_table_names was set to case sensitive, and then a table containing uppercase letters was created. The parameter setting was later changed to case insensitive, and now 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 controlling case-sensitivity for the new instance is different from that of the original instance.
Note
- 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.
Solution
- Log in to the management console.
- Click
in the upper left corner and select a region and a project. - Click Service List. Under Database, click Relational Database Service.
- On the Instances page, click the instance name.
- In the navigation pane, choose Parameters.
- Change the value of lower_case_table_names to 0, indicating that table names are case sensitive.
- Click Save. In the displayed dialog box, click Yes.
- Return to the DB instance list, locate the DB instance, and choose More > Reboot in the Operation column.
- In the displayed dialog box, click OK to reboot the DB instance for the modification to take effect.
- Log in to the database and change uppercase letters in table names to lowercase letters.
- Change the value of lower_case_table_names to 1, indicating that table names are case insensitive.
- Reboot the instance again.
Note
- Database names and variable names must be case sensitive.
- Column names and aliases are case insensitive by default.
- You can set Table Name to Case sensitive or Case insensitive on the RDS console during instance creation.
- You can set parameter lower_case_table_names to 0 or 1 when calling an API to create a DB instance.
Value range:
- 0: Table names are case sensitive.
- 1: Table names are stored in lowercase and are case insensitive.
Parent topic: Parameter-related Issues
- Scenario
- Solution