The following simple test demonstrates that innodb_undo_log_truncate=ON fails to truncate undo tablespaces:

--source include/have_innodb.inc

--source include/have_sequence.inc

SET GLOBAL innodb_fast_shutdown=0, innodb_undo_log_truncate=OFF;

CREATE TABLE t(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;

INSERT INTO t SELECT seq, NULL FROM seq_1_to_500000;

--source include/restart_mysqld.inc

SET GLOBAL innodb_fast_shutdown=0, innodb_undo_log_truncate=ON;

--source include/restart_mysqld.inc

DROP TABLE t;

Invocation:

./mtr --mysqld=--innodb-undo-tablespaces=2 name_of_test

wc -c var/mysqld.1/data/undo*

10485760 var/mysqld.1/data/undo001

13631488 var/mysqld.1/data/undo002

24117248 total

The expected outcome would be that all undo tablespaces have been truncated to their default soft limit size (innodb_max_undo_log_size=10M). Instead of that, we will observe that one of the undo tablespace files is larger.

I think that the undo tablespace truncation needs to work also while InnoDB is running (mostly idle, with some writes every now and then) and the parameter innodb_purge_rseg_truncate_frequency caused a call to trx_purge_truncate_history() to be skipped during the last purge batch that made the undo logs logically empty but failed to reclaim the space.

I originally noticed this when testing an upgrade from a server that is affected by MDEV-31234.