2

[BUG] Stopping Purge/Resuming Purge in Error Logs After Upgrade to MySQL 5.7.40

 1 year ago
source link: https://www.percona.com/blog/bug-stopping-purge-resuming-purge-in-error-logs
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

[BUG] Stopping Purge/Resuming Purge in Error Logs After Upgrade to MySQL 5.7.40

We had a couple of cases where clients reported that the MySQL error log was flooded with the below note:

Shell
2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:28.581674Z 2 [Note] InnoDB: Stopping purge

One of my colleagues Sami Ahlroos found that whenever we trigger a truncate on any table, the function is stopping the purge and then resuming it once it has found it stopped.

Below are the steps to reproduce.

  1. Log verbosity needs to be set to 3 (the default value)
Shell
mysql> show variables like 'log_error_verbosity%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 3     |
+---------------------+-------+

     2. Create a test table and run a truncate multiple times

Shell
mysql> create table t(id int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> truncate table t;
Query OK, 0 rows affected (0.01 sec)
mysql> truncate table t;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table t;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table t;
Query OK, 0 rows affected (0.01 sec)

   3. Check the error logs

Shell
2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:28.581674Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:28.583307Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:29.322114Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:29.323765Z 2 [Note] InnoDB: Resuming purge

The bug was reported to MySQL and confirmed as well.

Along with MySQL version 5.7.40, similar behavior is also noticed in Percona Server for MySQL 5.7.40 and Percona XtraDB Cluster 5.7.40. This behavior was not noticed in the 8.0 version.

NOTE – There is no workaround to get through it as of now. We can only avoid this by setting log_error_verbosity to 2, so it discards notes in the error log. So if you are doing a lot of truncates, this can be a workaround not to add those notes in the error logs.

References:

https://bugs.mysql.com/bug.php?id=109552 

https://jira.percona.com/browse/PS-8575


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK