We had a couple of cases where clients reported that the MySQL error log was flooded with the below note:
|
1 |
2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge<br>2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge<br>2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge<br>2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge<br>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 |
mysql> show variables like 'log_error_verbosity%';<br>+---------------------+-------+<br>| Variable_name | Value |<br>+---------------------+-------+<br>| log_error_verbosity | 3 |<br>+---------------------+-------+ |
2. Create a test table and run a truncate multiple times
|
1 |
mysql> create table t(id int primary key);<br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql> truncate table t;<br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql> truncate table t;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> truncate table t;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> truncate table t;<br>Query OK, 0 rows affected (0.01 sec) |
3. Check the error logs
|
1 |
2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge<br>2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge<br>2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge<br>2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge<br>2023-01-18T13:08:28.581674Z 2 [Note] InnoDB: Stopping purge<br>2023-01-18T13:08:28.583307Z 2 [Note] InnoDB: Resuming purge<br>2023-01-18T13:08:29.322114Z 2 [Note] InnoDB: Stopping purge<br>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:
