How to modify lower_case_table_names parameter in Amazon RDS MySQL?

0

Hello, I am using Amazon RDS service with MySQL 8.0 database. The lower_case_table_names parameter defaulted to 0 when creating this database instance. Now due to some application compatibility considerations, I need to change this parameter value to 1 to ignore table name case sensitivity.

I have tried the following but neither works:

Directly modifying the parameter using API calls, got error saying it's not modifiable. Trying to change it via parameter group, but RDS docs say this parameter is read-only in parameter groups too. Checked documentation which says modifying this parameter is not supported on existing instances. I also tried using a read replica with the parameter specified, but write operations are not possible on read replicas.

Is there any good way to modify the lower_case_table_names value on an existing RDS MySQL instance? e.g. via data migration? Any suggestions would be appreciated!

I understand data migration to a new RDS MySQL instance is one way to modify the lower_case_table_names parameter. However, creating a new RDS instance incurs additional costs such as:

Instance usage charges for the new instance configuration and running hours Backup storage costs for the automatic backups of the new instance I/O charges for database activities on the new instance Data transfer charges for migrating data to the new instance While the new instance is needed for the parameter change, I want to minimize costs as much as possible.

Could you please suggest what is the most cost-optimized migration approach in this case? For example:

Using a small and minimal new instance just for migration purposes Shutting down the new instance immediately after the migration is done Using some cost saving mechanisms like reserved instances and scheduled scaling Any other ideas to migrate to a new RDS instance for parameter change, while optimizing the costs involved? Your insights on keeping the RDS migration costs low are much appreciated! Please advise on the most cost-effective way to complete this.

Thanks!

wang
asked 10 months ago863 views
1 Answer
0

Hi, look at this page https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.KnownIssuesAndLimitations.html for section named 'lower_case_table_names', it has limitations depending on config and db version. One may apply to you.

Best, Didier

profile pictureAWS
EXPERT
answered 10 months ago
  • Hi Didier,

    Thanks for your reply. I checked the RDS documentation page you referenced, and understand the limitations around modifying the lower_case_table_names parameter.

    However, in my specific case I have MySQL 8.0 on RDS, and the document mentions this limitation only applies to MySQL 5.7 and earlier versions.

    The key issue I'm facing is that I must change this parameter from 0 to 1 for an existing production database instance. Based on the docs, I don't think it's possible to directly modify it on the current instance.

    I'm wondering if you have any other suggestions on how I could achieve this change while minimizing downtime and costs? For example, is there a way to spin up a new instance just for migration, switch over the application, then delete the instance? Or maybe use DMS for zero-downtime migration?

    I'm happy to provide more details if needed. Please let me know if there are any other options I could explore here. Thanks again for your time!

    Best regards, Wang

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions