[#MDEV-29823] Secondary index records may never be purged after rollback

There is a rare race condition between rollback and purge that causes row_undo_mod_del_mark_or_remove_sec_low() to delete-mark a secondary index record instead of deleting it. Eventually that record will enter purge_sys.view, but it will never be deleted. At the time of the rollback the history had to be preserved for potential access in active read views. After the rollback, the undo log record will never be added to the purge queue by trx_purge_add_undo_to_history().

Any orphan delete-marked secondary index records will be ignored by normal MVCC reads and row_vers_impl_x_locked_low(). The only problem is a resource leak, or bloat of secondary indexes. It can be fixed by dropping and re-creating the affected secondary indexes or rebuilding the entire table with OPTIMIZE TABLE.

The ~~MDEV-24402~~ CHECK TABLE...EXTENDED will only report warnings for these records:

InnoDB: Clustered index record not found for index ...

For actual corruption (such as an orphan non-delete-marked secondary index record), the affected secondary indexes will be marked as corrupted.

mleich posted the following RQG grammar in ~~MDEV-24402~~ that should reproduce this:

__clone__1:

    { $my_int= 2 } |

    { $my_int= 6 } |

    { $my_int= 8 } ;

__clone__2:

    { $my_int= 2 } |

    { $my_int= 6 } |

    { $my_int= 8 } ;

insert_part:

    INSERT INTO t3 (col1,col2, col_int, col_string, col_text ) VALUES ;

non_generated_cols:

    col1 INT PRIMARY KEY, col2 INT, col_int INTEGER, col_string INTEGER, col_text TEXT ;

query:

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 ADD UNIQUE KEY ( col_int ) , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 ADD UNIQUE KEY ( col_int ) , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    ALTER TABLE t3 ADD UNIQUE KEY ( col_int ) , ALGORITHM = NOCOPY , LOCK = EXCLUSIVE |

    CHECK TABLE t3 EXTENDED |

    CHECK TABLE t3 EXTENDED |

    CHECK TABLE t3 EXTENDED |

    UPDATE t3 SET col_int = __clone__2 ORDER BY col1 DESC LIMIT 2 ; ROLLBACK |

    UPDATE t3 SET col_int = __clone__1 ORDER BY col1 DESC LIMIT 2 ; ROLLBACK |

    insert_part ( __clone__2 , $my_int, $my_int, '', '' ); ROLLBACK |

    insert_part ( __clone__1 , $my_int, $my_int, '', '' ); ROLLBACK ;

thread1:

    query ;

thread1_connect:

thread1_init:

    CREATE TABLE IF NOT EXISTS t3 ( non_generated_cols ) ENGINE = InnoDB ROW_FORMAT = Compact ;

thread_init:

    { sleep 30 ; exit 0 };

According to my analysis of one rr replay trace of this, the ALTER TABLE should only matter with respect to occasionally blocking concurrent DML and the progress of purge. In that trace, rollback leaked a record in the first created secondary index, which had been created well before the relevant DML transactions were run. We failed to create a simpler test case so far.

[MDEV-29823] Secondary index records may never be purged after rollback Created: 2022-10-19 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s:	10.6, 10.11

[MDEV-29823] Secondary index records may never be purged after rollback Created: 2022-10-19 Updated: 2023-11-28