Oracle Cloud Infrastructure Documentation

Troubleshooting MySQL Server

Resolving SUPER_READ_ONLY and OFFLINE_MODE Issue Caused by Low Free Storage Space

MySQL Server enables SUPER_READ_ONLY and OFFLINE_MODE when the available disk space falls below certain configuration values.

Health Monitor enables the SUPER_READ_ONLY and OFFLINE_MODE in the following scenarios:
  • The available disk space falls below disk_low_space_level or disk_low_space_percent, whichever value is lower, for disk_low_space_duration seconds.
  • The available disk space falls below disk_low_space_critical_level, for any duration.
Note

The SUPER_READ_ONLY server variable is also enabled when the database mode of the DB system is set to read-only and the OFFLINE_MODE server variable is also enabled when the access mode of the DB system is set to administrators only. Check the super_read_only_disk_full status variable. It is also enabled if the free storage space is low.

Using the Console

Use the Console to resolve the SUPER_READ_ONLY or OFFLINE_MODE issue of MySQL Server caused by low free storage space.

This task assumes the following:
  • You get the following error when any client attempts to connect to the MySQL Server: 
    MySQL Error 1290 (HY000): The MySQL server is running with the --super-read-only 
option so it cannot execute this statement @ file bytes range [1400005472, 1500005697).
    MySQL Error 1290 (HY000): The MySQL server is running with the --offline-mode 
option so it cannot execute this statement @ file bytes range [1400005472, 1500005697).
  • You check the super_read_only_disk_full status variable is ON to verify that it is caused by low free storage space:
    mysql> SHOW STATUS LIKE 'super_read_only_disk_full';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| super_read_only_disk_full | ON    |
+---------------------------+-------+
Do any of the following to resolve the issue:
  • Increase the storage size, and restart the DB system. See Increasing DB System Storage.
  • If you cannot increase the storage size, do the following:
    1. Create a manual backup of the DB system. See Creating a Backup.
    2. Create a new DB system using the manual backup with a larger storage size. See Creating a DB System from a Backup.
  • Health Monitor resets the SUPER_READ_ONLY and OFFLINE_MODE server variables to OFF after the disk space shortage has been recovered. If the database mode of the DB system is set to read-only, the DB system becomes writable. If the access mode of the DB system is set to administrators only, the DB system allows all users to connect. To maintain the database mode and access mode, you have to restart the DB system.
To prevent the issue from occurring:

To prevent the issue from occurring, you can use the DbVolumeUtilization metric to be alerted before disk space falls below critical levels. For more information, see Checking the Storage of a DB System.

Debugging and Diagnostics Utility

Use the debugging and diagnostics utility to collect diagnostic data on the MySQL Server.

The debugging and diagnostics utility enables you to collect raw diagnostic data from MySQL Servers and generates a diagnostic report to either the local directory or a specified path.