Bug #109552 TRUNCATE TABLE causes "Stopping / Starting purge" log entries

Submitted: 9 Jan 2023 12:52 Modified: 9 Jan 2023 13:23
Reporter: Sami Ahlroos Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[9 Jan 2023 12:52] Sami Ahlroos
Description:
After upgrading to 5.7.40, every time TRUNCATE TABLE is executed, this gets logged in the error log:
2023-01-09T12:42:08.822717Z 2 [Note] InnoDB: Stopping purge
2023-01-09T12:42:08.824934Z 2 [Note] InnoDB: Resuming purge

How to repeat:
With log_error_verbosity=3, execute TRUNCATE TABLE and check error log:
----
mysql> create table t(id int primary key);
Query OK, 0 rows affected (0.00 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)
----
Followed by:
----
# tail /var/log/mysqld.log
2023-01-09T12:41:17.788062Z 0 [Note] Failed to start slave threads for channel ''
2023-01-09T12:41:17.792762Z 0 [Note] Event Scheduler: Loaded 0 events
2023-01-09T12:41:17.792998Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.40-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2023-01-09T12:42:08.822717Z 2 [Note] InnoDB: Stopping purge
2023-01-09T12:42:08.824934Z 2 [Note] InnoDB: Resuming purge
2023-01-09T12:42:09.564364Z 2 [Note] InnoDB: Stopping purge
2023-01-09T12:42:09.566206Z 2 [Note] InnoDB: Resuming purge
2023-01-09T12:42:09.944561Z 2 [Note] InnoDB: Stopping purge
2023-01-09T12:42:09.946289Z 2 [Note] InnoDB: Resuming purge
#
----
On a system doing lots of truncating the error log is flooded with the above. This did not happen on 5.7.39.

I suspect this change is what causes this: https://github.com/mysql/mysql-server/commit/c7d0df6b872b60b7f0a67f69ecdc85c188792f7a#diff...
It adds a call to trx_purge_stop() in row0trunc.cc which seems to be the cause of these notes in the log.
[9 Jan 2023 13:23] MySQL Verification Team
Hi Mr. Ahiroos,

Thank you for your bug report.

We have repeated your test case with 5.7.40.

It is possible that this report will finish as a Documentation bug, but that is not our call to make.

Verified as reported.
[9 Jan 2023 13:30] MySQL Verification Team
The same behaviour was NOT observed with 8.0.31.
[9 Jan 2023 13:31] MySQL Verification Team
Hence, this is 5.7-only bug.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.