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

This topic describes how to 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

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

Before you begin

Back up table data before you delete 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 you delete data files, storage release takes some time.
After you delete 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 with 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 these 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. If DELETE is frequently executed on certain tables, fragmentation builds up. 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>
```