Can not create a new RDS MySQL DB instance with "lower_case_table_names=1"

Question

I'm trying to create a new MySQL v8.0.11 RDS DB Instance with "lower_case_table_names=1".

The creation of the database is stuck and in the logs I can see the following error:

"Different lower_case_table_names settings for server ('1') and data dictionary ('0')."

Anyone has gone through this?

Please help.

Answer 1

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.KnownIssuesAndLimitations.html

lower_case_table_names Because Amazon RDS uses a case-sensitive file system, setting the value of the lower_case_table_names server parameter to 2 ("names stored as given but compared in lowercase") is not supported. The following are the supported values for Amazon RDS for MySQL DB instances:

0 ("names stored as given and comparisons are case-sensitive") is supported for all Amazon RDS for MySQL versions.

1 ("names stored in lowercase and comparisons are not case-sensitive") is supported for Amazon RDS for MySQL version 5.5, version 5.6, version 5.7, and version 8.0.19 and higher 8.0 versions.

The lower_case_table_names parameter should be set as part of a custom DB parameter group before creating a DB instance. You should avoid changing the lower_case_table_names parameter for existing database instances because doing so could cause inconsistencies with point-in-time recovery backups and read replica DB instances.

Read replicas should always use the same lower_case_table_names parameter value as the source DB instance.

Answer 2

Amazon RDS support Parameter groups that define settings for database engines.

The parameter group for MySQL 8 includes a setting for lower_case_table_names:

You should configure this parameter group, then launch an RDS cluster using the parameter group.

See: Working with DB Parameter Groups - Amazon Relational Database Service

Answer 3

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

This is a question for AWS regarding support for this option. It depends on how they initialize RDS instances. I'm guessing that they clone an image of a pre-initialized InnoDB tablespace, instead of initializing a new tablespace.