ApsaraDB RDS:What do I do if databases and tables cannot be accessed or the major engine version of instances cannot be upgraded because the lower_case_table_names parameter is modified

Valid values:

• 0: Database and table names are stored with strict case sensitivity, and the original case form is preserved when accessed (case-sensitive matching).

• 1: Database and table names are forcibly converted to lowercase for storage, and must be accessed using lowercase names (case-insensitive matching).

• 2: Database and table names are case sensitive in storage, but are automatically converted to lowercase when accessed. This value is applicable only to certain file systems, RDS for MySQL does not support this value).

Limits for RDS for MySQL instances:

• Only 0 and 1 are supported. The value 2 is not supported.

• MySQL versions:

  • RDS instances running a MySQL version earlier than 8.0: This parameter is read-only and can be modified by editing the cnf file and restarting the instance.

  • RDS instances running MySQL 8.0: The parameter value is fixed during instance initialization and cannot be modified after initialization is complete.

Databases and tables become inaccessible after changing parameter value from 0 to 1

1. Databases and tables are created when the parameter value is 0. Therefore, their names containing uppercase letters.

   mysql> SHOW GLOBAL VARIABLES LIKE "lower_case_table_names";
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 0     |
   +------------------------+-------+
   1 row in set (0.01 sec)
   
   mysql> CREATE DATABASE db_1;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> USE db_1;
   Database changed
   
   # Create table name containing uppercase letters
   mysql> CREATE TABLE T1(a int);
   Query OK, 0 rows affected (0.00 sec)
   
   # Create database name containing uppercase letters
   mysql> CREATE DATABASE DB_2;
   Query OK, 1 row affected (0.00 sec)

2. After the parameter value is changed to 1, the system cannot recognize the original database and table names, which may result in database and table access failures.

   mysql> SHOW GLOBAL VARIABLES LIKE "lower_case_table_names";
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 1     |
   +------------------------+-------+
   1 row in set (0.00 sec)
   
   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | DB_2               |
   | db_1               |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+
   6 rows in set (0.00 sec)
   
   # Database name access failure: DB_2 cannot be accessed, switching to, modifying, or deleting DB_2 fails
   mysql> USE DB_2;
   ERROR 1049 (42000): Unknown database 'db_2'
   mysql> USE db_2;
   ERROR 1049 (42000): Unknown database 'db_2'
   mysql> ALTER DATABASE DB_2 CHARACTER SET utf8mb3;
   ERROR 1 (HY000): Can't create/write to file './db_2/db.opt' (Errcode: 2 - No such file or directory)
   mysql> ALTER DATABASE db_2 CHARACTER SET utf8mb3;
   ERROR 1 (HY000): Can't create/write to file './db_2/db.opt' (Errcode: 2 - No such file or directory)
   mysql> DROP DATABASE DB_2;
   ERROR 1008 (HY000): Can't drop database 'db_2'; database doesn't exist
   mysql> DROP DATABASE db_2;
   ERROR 1008 (HY000): Can't drop database 'db_2'; database doesn't exist
     
   mysql> USE db_1;
   Database changed
   
   mysql> SHOW TABLES;
   +----------------+
   | Tables_in_db_1 |
   +----------------+
   | T1             |
   +----------------+
   1 rows in set (0.00 sec)
   
   # Table name access failure: T1 cannot be accessed, querying, modifying, or deleting T1 fails
   mysql> SELECT * FROM T1;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> SELECT * FROM t1;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> ALTER TABLE T1 ENGINE=INNODB;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> ALTER TABLE t1 ENGINE = INNODB;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> DROP TABLE T1;
   ERROR 1051 (42S02): Unknown table 'db_1.t1'
   mysql> DROP TABLE t1;
   ERROR 1051 (42S02): Unknown table 'db_1.t1'

Major engine version upgrade from MySQL 5.7 to 8.0 fails

When an RDS instance running MySQL 5.7 contains databases and tables with uppercase letters in their names, and the value of lower_case_table_names is 1, the major engine upgrade of this instance to MySQL 8.0 fails. Sample error logs are as follows:

[ERROR] [MY-013520] [Server] Schema name 'DB_2' containing upper case characters is not allowed with lower_case_table_names = 1.
[ERROR] [MY-013521] [Server] Table name 'db_1.T1' containing upper case characters is not allowed with lower_case_table_names = 1.
[ERROR] [MY-010022] [Server] Failed to Populate DD tables.
[ERROR] [MY-010119] [Server] Aborting