[MDEV-32869] SELECT EXTENDED for the recovery of corrupted data Created: 2023-11-24  Updated: 2023-11-24

Status: Open
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Fix Version/s: 11.5

Type: New Feature Priority: Major
Reporter: Marko Mäkelä Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: None


 Description   

monty has requested that REPAIR TABLE be implemented for InnoDB tables.

I believe that there are some fundamental problems with REPAIR TABLE:

  1. It is a destructive operation: The corrupted table will be replaced with something, and there will be no way to retry the repair with other options.
  2. There are some underlying assumptions that do not seem appropriate for storage engines that supports MVCC or row-level locking.
  3. If the InnoDB undo logs or transaction metadata are corrupted (such as in MDEV-27800 and MDEV-32757), the table data itself may be correct.

For these reasons, I think that a better option to resurrect data from a corrupted database instance would be to implement a special form of SELECT * statement that would include some additional columns in the result set that would be defined by the storage engine. For InnoDB, these could include the following:

  1. DB_ROW_ID (usually NULL; assigned if the table lacks a primary key)
  2. DB_TRX_ID (the transaction ID; 0 if all history has been purged)
  3. DB_ROLL_PTR (the undo log pointer; apart from the "is insert" flag this is not very useful information)
  4. whether the record is delete-marked
    At READ UNCOMMITTED isolation level, I think that it would make sense to return also delete-marked records, so that they can be "undeleted" for particular DB_TRX_ID that were in progress when some corruption was noticed.
  5. whether the record resides in a page that has been marked as free
  6. whether the record resides in a page whose checksum is incorrect
    (some tweak would be needed to access such pages; they are typically evicted on read completion)
  7. whether any BLOBs of the record are corrupted or reside in marked-as-free pages

I think that implementing this would require a new virtual member function in class handler.



 Comments   
Comment by Sergei Golubchik [ 2023-11-24 ]

SELECT is supposed to select, and if one wants to repair a table, one uses REPAIR.

Nothing prevents InnoDB from implementing it internally as a select or from creating a backup, but the command name should correspond to what the command does.

Generated at Thu Feb 08 10:34:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.