Rollback may leave a delete-marked secondary index record that purge does not remove

thiru found that, after MDEV-38056, a couple of tests seldom failed on slow runners:

1. innodb.old_blob_purgeable
2. innodb.old_blob_updel

I used AI locally and found that the innodb.old_blob_updel failure is in the test only, but the innodb.old_blob_purgeable failure shows a true gap in the implemented logic (the crash is still prevented, but orphan entries can be left, which CHECK TABLE ... EXTENDED reports).

I'm targeting 10.11 as the opportunity to leave orphan entries was opened by MDEV-38056, in 10.11 too.

Please find below the description provided by the AI tool.

Problem

The MDEV-38056 fix makes row_undo_mod_sec_is_unsafe() freeze purge_sys.view across the dereference of externally stored columns. Under the freeze, it tests whether purge can see the transaction whose undo log record rebuilt the older version. If purge_sys.view advanced between the walk and the freeze, the function reports the secondary index entry as still needed, and the rollback delete-marks the entry instead of removing it.

No undo log record refers to that entry, so purge does not remove it, unless a later write of the same key value reuses it.

For the same transaction, trx_undo_prev_version_build() with TRX_UNDO_CHECK_PURGEABILITY ends the walk with no older version, and the entry is removed. row_purge_is_unsafe() treats the end of the versions in the same way. Only the new exit gives the opposite answer.

Impact

• One delete-marked secondary index record per occurrence, which purge does not remove. It stays until the table is rebuilt, or until a later write of the same key value reuses it.
• CHECK TABLE ... EXTENDED reports it as a warning, after purge_sys.end_view has moved past the transaction that rolled back. The status stays OK:

  Warning InnoDB: Clustered index record not found for index `b` of table `test`.`t`: COMPACT RECORD(info_bits=32, 2 fields): ...

• I found no wrong query results. A reader checks the clustered index record for a delete-marked secondary index record, and the duplicate key check skips delete-marked records.
• It needs the rollback of an UPDATE that changes a secondary index column of a row whose older versions have externally stored columns, and purge advancing in a short window during the rollback. I did not measure how often this happens.

How to repeat

The debug keyword row_undo_mod_sec_is_unsafe_purgeable forces the branch that the race reaches. The second table makes purge run a batch after the rollback, so that CHECK TABLE ... EXTENDED reports the record without depending on purge timing.

--source include/have_innodb.inc

--source include/have_debug.inc

--source include/wait_all_purged.inc

CREATE TABLE t(a INT PRIMARY KEY, pad INT, b MEDIUMTEXT, UNIQUE(b(100)))

ENGINE=InnoDB STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC;

INSERT INTO t VALUES (1, 0, REPEAT('x', @@innodb_page_size));

UPDATE t SET pad = 1 WHERE a = 1;

UPDATE t SET b = REPEAT('y', @@innodb_page_size) WHERE a = 1;

BEGIN;

UPDATE t SET b = REPEAT('z', @@innodb_page_size) WHERE a = 1;

SET SESSION debug_dbug='+d,row_undo_mod_sec_is_unsafe_purgeable';

ROLLBACK;

SET SESSION debug_dbug='';

CREATE TABLE t2(a INT) ENGINE=InnoDB STATS_PERSISTENT=0;

INSERT INTO t2 VALUES (1);

--source include/wait_all_purged.inc

CHECK TABLE t EXTENDED;

DROP TABLE t, t2;

Expected: CHECK TABLE returns only status OK.
Actual: CHECK TABLE also returns the warning above, every time. The server error log contains the same warning, so mtr also reports the test for it.

How it was found

The amd64-fedora-44-valgrind builder failed innodb.old_blob_purgeable once, with the same warning. Without the extra purge batch, the warning appears only when purge happens to run a batch after the rollback, which is more likely under Valgrind:

@@ -54,6 +54,7 @@

 1	1	yy

 CHECK TABLE t EXTENDED;

 Table	Op	Msg_type	Msg_text

+test.t	check	Warning	InnoDB: Clustered index record not found for index `b` of table `test`.`t`: COMPACT RECORD(info_bits=32, 2 fields): {[700]zzzz...

 test.t	check	status	OK

The same builder also failed innodb.old_blob_updel once. That failure is in the test only: purge_hold_cleanup can hold the batch that purges the history of the rows the test inserts first, instead of the batch that the test expects.

 del_mark_records_purged

-1

+0

Proposed fix

In row_undo_mod_sec_is_unsafe(), when the test under the freeze shows that purge can see that transaction, end the walk with no older version, as trx_undo_prev_version_build() does, so that the rollback removes the entry. No read view needs the rebuilt version, and the walk found no match among the newer versions.

The patch also:

• adds the debug keyword row_undo_mod_sec_is_unsafe_keep, which restores the previous answer as a negative control;
• makes innodb.old_blob_purgeable report the record regardless of purge timing, and fixes its failure at innodb_page_size=64k, where REPEAT('x', 65536) does not fit in TEXT;
• fixes the race in innodb.old_blob_updel.