ApsaraDB RDS:What do I do if an ApsaraDB RDS for MySQL instance is in the Locked state because its storage capacity is exhausted by data files?

Problem description

A large amount of data is written to your RDS instance. As a result, the storage capacity of the RDS instance is exhausted by data files, and the RDS instance enters the Locked state.

Cause

The storage capacity of an RDS instance is exhausted by the data files that are retained for a long period of time. In this case, the RDS instance is automatically locked to prevent data loss, and you cannot write data to your RDS instance.

Solution

Prerequisites

If your RDS instance runs MySQL 5.6, MySQL 5.7, or MySQL 8.0, the minor engine version of the RDS instance must be 20190815 or later. If the minor engine version of your RDS instance is earlier than 20190815, you must update the minor engine version before you delete data files. For more information, see Update the minor engine version.

Usage notes

• Before you delete a table, make sure that the table data is backed up to prevent loss.
• If you use an ApsaraDB RDS for MySQL instance, you cannot execute the DELETE statement to release the storage of the RDS instance. We recommend that you execute the DROP or TRUNCATE statement to release the storage of the RDS instance.
• If you execute the OPTIMIZE statement on a table, the table becomes locked. We recommend that you execute the OPTIMIZE statement during off-peak hours.
• After data files are deleted, a period of time is required to release the storage of your RDS instance.
• After data files are deleted, ApsaraDB RDS unlocks your RDS instance in approximately 5 minutes.

Procedure

You can use one of the following methods to resolve the issue:

• Expand the storage capacity of an RDS instance.
  You can expand the storage capacity of your RDS instance. For more information, see Change instance specifications.

  Note After the specifications of your RDS instance are changed, ApsaraDB RDS unlocks your RDS instance in approximately 5 minutes.
• Delete historical or unnecessary data.
  1. Use Data Management (DMS) to connect to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
  2. On the SQL Console tab, execute the following statement to view the sizes of data files and identify the data files that contain historical data or unnecessary data:

     SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC

  3. Execute the DROP or TRUNCATE statement to delete historical or unnecessary data.
     Note After the deletion is complete, ApsaraDB RDS unlocks your RDS instance in approximately 5 minutes.
     • DROP: Execute the DROP TABLE <Database name>.<Table name> statement to delete a table.
     • TRUNCATE: Execute the TRUNCATE TABLE <Database name>.<Table name> statement to delete data records from a table.

What to do next