ApsaraDB RDS: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

Value description:

• 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 preserve case sensitivity for storage, but are automatically converted to lowercase when accessed (only applicable to certain file systems, RDS MySQL does not support this value).

RDS MySQL instance limits:

• Only values 0 or 1 are supported. Value 2 is not supported.

• Version differences:

  • MySQL versions below 8.0: This parameter is read-only and can be modified by editing the cnf file and restarting the instance.

  • MySQL 8.0 version: 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. When the initial parameter value is 0 (case-sensitive storage), database and table names containing uppercase letters are created:

   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 changing the parameter to 1 (forced lowercase storage), the system cannot correctly detect the original mixed-case 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 version upgrade to MySQL 8.0 fails

When a MySQL 5.7 instance contains uppercase database and table names, and lower_case_table_names = 1, the upgrade to MySQL 8.0 fails. Example error log:

[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