What do I do if my Amazon RDS MySQL DB instance is stuck in the modifying state?

Last updated: 2021-07-01

My Amazon Relational Database Service (Amazon RDS) for MySQL DB instance is stuck in the modifying state. How do I troubleshoot this?

Short description

In Amazon RDS, some modifications types require the instance to be unavailable and inaccessible during the instance update. After a modification begins, the operation can't be canceled, and the DB instance status displays as "modifying" until the operation completes. However, the following actions are available while Amazon RDS MySQL is in the modifying state.

Resolution

If your Amazon RDS DB instance is stuck in the modifying state, consider the following approaches:

  • Retrieve the Recent events for your Amazon RDS resources from the AWS Management Console to check the current state of your DB instance. The Recent events log displays events from the past 24 hours. For more information about the Recent events log, see Viewing Amazon RDS events.
  • Check the Amazon RDS MySQL error logs to verify the status of your DB engine. Amazon RDS MySQL writes mysql-error.log entries to disk every five minutes. RDS MySQL appends the contents of the log to the mysql-error-running.log file when you view your log file.
  • If the automated backups option is enabled for your RDS MySQL instance, then try restoring a DB instance to a specified time. Or, you can restore your instance from the latest available DB snapshot, creating a new DB instance with the original configuration. For more information, see Restoring a DB instance to a specified time.
  • If you created a Read Replica instance of your DB instance, promote your Read Replica to a standalone DB instance. For more information, see Promoting a read replica to be a standalone DB instance.

Best practices to follow before applying the Modify action

Before you apply the "Modify" action, make sure to follow these best practices:

  • Enable backup retention and set the value to ">0" before you perform any DB instance modifications. Enabling backup retention allows Amazon RDS to perform point-in-time-recovery (PITR) if needed.
  • Review the RDS MySQL monitoring outputs (SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS), and avoid any long-running transactions or queries. For more information, see SHOW PROCESSLIST Statement on the MySQL website.
  • Backup and restore your test DB instance and perform a trial modification. The trial helps you estimate the actual time needed to complete your intended update.
  • For a production environment, enable Multi-AZ deployment. Amazon RDS provides high availability and failover support for DB instances using Multi-AZ deployments.