The following test reproduces this rather easily:

--source include/have_innodb.inc

--source include/have_sequence.inc

CREATE TABLE t(a INT PRIMARY KEY, b INT NOT NULL, INDEX(b)) ENGINE=InnoDB;

SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR

INSERT INTO t SELECT seq, seq from seq_1_to_10000;

--disable_query_log

let $N=30;

while ($N)

{

UPDATE t SET b=a+1; UPDATE t SET b=a;

dec $N;

}

--enable_query_log

SET GLOBAL innodb_max_purge_lag_wait=0;

DROP TABLE t;

./mtr --parallel=5 main.m-MDEV-35508{,,,,}

main.m-MDEV-35508                        w3 [ pass ]  13148

main.m-MDEV-35508                        w4 [ fail ]  Found warnings/errors in server log file!

        Test ended at 2024-11-27 11:24:14

line

2024-11-27 11:24:06 0 [ERROR] InnoDB: tried to purge non-delete-marked record in index `b` of table `test`.`t`: tuple: TUPLE (info_bits=0, 2 fields): {[4]  "?(0x8000223F),[4]  ">(0x8000223E)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[4]  "?(0x8000223F),[4]  ">(0x8000223E)}

marko, mleich It is great that we could catch the issue. I completely agree that the issue is caused by MDEV-34515 PAGE_MAX_TRX_ID related optimization. I think the issue is more serious and could affect release build too possibly corrupting secondary index. To understand the issue effectively I could create a debug_sync test that repeats the issue consistently every time. Please find attached 10.6_test_repeat.patch.

Here is what I find from analyzing the issue.

1. The assert could still be hit with the current fix and needs to be improved. Essentially, the transaction ID is the start point and there could be many transaction running with ID smaller than PAGE_MAX_TRX_ID set by some transaction that is already committed. A transaction with ID smaller than PAGE_MAX_TRX_ID is not going to update the page. So, the condition needs to be stricter following the same logic that we apply while checking for implicit lock for secondary index page i.e. checking for all active transaction equal or smaller than the PAGE_MAX_TRX_ID. I have attached the patch that fixes the issue - 10.6_fix.patch

2. Since it is also possible another transaction to further delete mark the key (after being unmarked), the purge could actually successfully delete the secondary index which it should not. This could affect several cases including MVCC where a transaction may miss reading a record, Rollback not able to find the older record for removing delete mark eventually leading to missing entries in secondary index. I will test some more trying to simulate other scenarios.

debarun, thank you for 10.6_test_repeat.patch. If I disable the failing debug assertion, the test would trigger a write of the message to the server error log, but neither CHECK TABLE nor CHECK TABLE…EXTENDED would report any error.

Nevertheless, I can imagine a scenario where a secondary index record is being constantly delete-marked and delete-unmarked by a flow of DML operations, and a purge operation would prematurely delete the record, without showing any warning message. That is, a more complex test case could demonstrate actual corruption of a secondary index.

I checked for further impacts for the two cases I mentioned in last comment.

1. MVCC: By changing the attached test case to delete the record again by another transaction, and creating a read only transaction between Update and Delete, missing data read could be simulated. After purge incorrectly purges the delete marked row, SELECT using secondary index for the read only transaction returns no rows. The same SELECT would correctly show the record secondary index is not used in scan.

# Correct - One record fetched

SELECT * FROM t1 IGNORE INDEX (k1);

 col1	col2

 1	100

# Incorrect - No records fetched

SELECT * FROM t1 FORCE INDEX (k1);

col1	col2

2. Rollback: After the delete marked record is purged, rollback resulted in following error in server log. Fortunately, it looks like the rollback operation is able to re-insert back the correct record/key after facing the issue. So, things are in correct state after rollback and no corruption is observed.

2024-11-28 13:08:14 4 [Warning] InnoDB: Record in index `k1` of table `test`.`t1` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[4]   d(0x80000064),[4]    (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

Note that the fix of this moved some duplicated error reporting to a new function row_purge_del_mark_error(), which also includes a debug assertion ut_ad(0). Previously, such a debug assertion was only present in row_purge_remove_sec_if_poss_tree(), not in the function row_purge_remove_sec_if_poss_leaf(). Therefore, after this fix, we will get more corruption-related crashes in debug instrumented builds.