:No data available for slow logs of RDS MySQL instances

Problem description

Apsaradb RDS for MySQL instances have no data in the slow log details and slow log statistics. Therefore, you cannot query slow logs or generate slow log.

Cause

mysql.slow_log table damage RDS for MySQL provides the instance of the slow log entry in mysql.slow_log table background are regularly get slow log entry and clean mysql.slow_log table and then to show for it in the RDS console slow log details of the page.

Solution

RDS MySQL 5.5 edition instance

You can confirm that the slow query log feature is enabled. If the slow query log feature is enabled, submit a ticket to verify the problem.

RDS MySQL 5.6 edition instance

In order to completely solve the problem of mysql.slow_log damage, it is recommended that you upgrade the minor version of the RDS kernel to the latest. For more information, see upgrade the minor version to avoid mysql.slow_log damage.

Note: RDS MySQL 5.6 instances do not support running the repair command to repair mysql.slow_log tables. If you do not want to upgrade the minor kernel version, submit a ticket.

RDS MySQL 5.7 and RDS MySQL 8.0 edition instances

Method 1

In order to completely solve the problem of mysql.slow_log damage, it is recommended that you upgrade the minor version of the RDS kernel to the latest. For more information, see upgrade the minor version to avoid mysql.slow_log damage.

Method 2

We recommend that you run this solution during off-peak hours to avoid performance fluctuations caused by the repair command when the table to be mysql.slow_log is large.

1. Use the privileged account of your apsaradb for RDS instance to log on to the apsaradb for RDS Database by using DMS or a command line tool, and run the following SQL statement to query mysql.slow_log tables:

   select * from mysql.slow_log where sql_text like 'test%';

   If RDS returns the following information, you can confirm that the mysql.slow_log is damaged. Proceed to the next step.

   Table 'slow_log' is marked as crashed and should be repaired

2. Log on to the RDS console, and click instances.
3. Select a region and then click the ID of the target SLB instance.
4. Locate and then click the how to reconfigure parameters for an RDS parameter settings page will slow_query_log parameter is set to OFF, and then click submission parameters, turn OFF the slow log collection.
5. Create a master account. For more information, see create master accounts.
6. Log on to the RDS Database from the DMS console or CLI and run the following SQL statement to fix the issue:

   Note:
   

   • You can execute the repair statement to repair slow logs. New slow logs are generated and stored, but slow logs that are in the damaged process cannot be recovered.
   • The time of the repair statement operation depends on the size of the corrupted mysql.slow_log table.

   repair table mysql.slow_log

7. After the troubleshooting is completed, ON the parameter settings page of the RDS console, set the slow_query_log parameter to ON, and then click submit parameters to enable slow log collection.

Application scope

• ApsaraDB RDS for MySQL