Resolve an ApsaraDB RDS for MySQL instance locked due to storage exhaustion by data files - ApsaraDB RDS

Unlock an ApsaraDB RDS for MySQL instance that entered the Locked status because data files consumed all available storage capacity.

Symptoms

The RDS instance shows a Locked status.
Write operations fail.
Data files have grown without cleanup and exhausted the storage capacity.

Root cause

When data files accumulate without periodic cleanup, they exhaust the storage capacity. ApsaraDB RDS automatically locks the instance to prevent data loss. While locked, the instance rejects all write operations.

Prerequisites

Before you begin, ensure that you have:

A minor engine version of 20190815 or later for RDS instances running MySQL 5.6, MySQL 5.7, or MySQL 8.0. If your minor engine version is earlier than 20190815, update it before deleting data files. For more information, see Update the minor engine version

Minor engine version

Before you begin

Back up table data before deleting any table.
The DELETE statement does not release storage. Use DROP or TRUNCATE instead.
The OPTIMIZE statement locks the target table. Run it during off-peak hours.
After deleting data files, storage release takes some time.
After deleting data files, ApsaraDB RDS unlocks the instance in approximately 5 minutes.

Solutions

Solution 1: Expand the storage capacity

Increase the storage capacity of your RDS instance. For more information, see Change instance specifications.

After the specification change completes, ApsaraDB RDS unlocks the instance in approximately 5 minutes.

Solution 2: Delete unnecessary data

Connect to your RDS instance through Data Management (DMS). For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

On the SQL Console tab, run the following query to list data files by size:

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

Identify unnecessary data, then delete it using one of the following statements:
- Drop a table — removes the table and all its data: DROP TABLE <Database name>.<Table name>
- Truncate a table — removes all rows but keeps the table structure: TRUNCATE TABLE <Database name>.<Table name>

After deletion completes, ApsaraDB RDS unlocks the instance in approximately 5 minutes.

Prevent future lockouts

After the RDS instance is unlocked, take the following steps to prevent recurrence:

Enable automatic storage expansion. ApsaraDB RDS automatically expands storage capacity when usage reaches a threshold. For more information, see Configure automatic storage expansion.
Reclaim fragmented tablespace. Tables with frequent DELETE operations accumulate fragmentation over time. Run the following statement during off-peak hours to reclaim storage. For more information, see How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance?
```
OPTIMIZE TABLE <Database name>.<Table name>
```