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.

For more sensitive parameters, see Suggestions on Tuning RDS for MySQL Parameters.

• For RDS for MySQL 5.6 and 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

1. Log in to the management console.
2. Click in the upper left corner and select a region.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose Parameters.
6. Change the value of lower_case_table_names to 0, indicating that table names are case sensitive.
7. Click Save. In the displayed dialog box, click Yes.
8. Return to the DB instance list, locate the DB instance, and choose More > Reboot in the Operation column.
9. In the displayed dialog box, click OK to reboot the DB instance for the modification to take effect.
10. Log in to the database and change uppercase letters in table names to lowercase letters.
11. Change the value of lower_case_table_names to 1, indicating that table names are case insensitive.
12. Reboot the instance again.

• 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. For details, see Buy a DB Instance.
• You can set parameter lower_case_table_names to 0 or 1 when calling an API to create a DB instance. For details, see Creating a DB Instance.
  Value range:

  • 0: Table names are case sensitive.
  • 1: Table names are stored in lowercase and are case insensitive.