Resolve automatic locks for RDS for MySQL due to full storage - ApsaraDB RDS

When storage capacity is exhausted, ApsaraDB RDS for MySQL automatically locks the instance to prevent data loss. In the Locked state, no writes are accepted. This topic explains the common causes and how to resolve each one.

Note

After you free up disk space, wait 5 to 15 minutes for the system to automatically unlock the instance.

Diagnose the cause

Log on to the ApsaraDB RDS console. In the top navigation bar, select the region where the instance resides, and then click the instance ID.
In the left-side navigation pane, click Monitoring and Alerts to see which file type is consuming the most storage. For details, see View the monitoring information.
Use the table below to identify your cause and go to the corresponding solution.

Causes and solutions

Cause	Description	Solution
Data files	A large number of data files are stored on the instance.	Resolve storage exhaustion caused by data files
Binary log files	An inappropriate log backup policy or large transactions generate excessive binary log files.	Resolve storage exhaustion caused by binary log files
Temporary files	Sorting, grouping, and join operations in SQL queries produce large temporary files. Large transactions also generate log files cached before the commit.	Resolve storage exhaustion caused by temporary files
System files (undo files)	Long-running queries on InnoDB tables combined with large data updates generate a large number of undo files. Note This does not occur on MySQL 8.0, where the system automatically deletes undo files.	Resolve storage exhaustion caused by system files
General query log	When the `general_log` parameter is set to `ON`, every query, insert, update, and delete operation is logged. The file grows large if queries run for extended periods or the log is not cleared regularly.	Resolve storage exhaustion caused by the general query log

Check the general query log size

To confirm that the general query log is causing storage exhaustion, complete the following steps.

Check whether sys_data_size is unusually large in the monitoring information. For details, see View the monitoring information.
Verify that the general_log parameter is set to ON. For details, see View the parameters of an ApsaraDB RDS for MySQL instance.
Connect to the instance and run the following query to check the log table size. For details, see Connect to an ApsaraDB RDS for MySQL instance.
Note
This query returns the size of the mysql.general_log table from information_schema.TABLES in MB. The values returned are sample data and may vary.
```
SELECT table_schema AS 'Database', table_name,
SUM(data_length + index_length + data_free)/1024/1024 AS "Table size in MB",
SUM(DATA_FREE)/1024/1024 AS "Fragment size in MB"
FROM information_schema.TABLES
WHERE table_name='general_log'
```