Re: Power failure and innodb

[Marko Mäkelä]

Rick James Wrote:
> If you can create a reproducable loss/corruption,
> file a bug at bugs.mysql.com

Bug#75697 Add a consistency check against DB_TRX_ID being in the future

I remembered this thread when someone emailed me about a similar problem last week. And finally I found the likely explanation: InnoDB has the wrong idea of the latest assigned transaction identifier. So, it is pretending that ‘too new’ records are not visible. With

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

or with --innodb-read-only you would be able to see the data.

> > innodb_flush_log_at_trx_commit | 1
>
> That's probably the ultimate in durability.

Right. But with any setting, InnoDB should not forget more transactions than those that were committed after the latest write to the redo log file.

Note: When I reproduced the problem, I did not test with UPDATE or DELETE. I would expect InnoDB to crash in interesting ways when it tries to fetch the ‘previous version’ of a ‘too new’ record, by dereferencing DB_ROLL_PTR. That field would be pointing to garbage (the undo log bytes have likely been reused for something else).

There are two types of InnoDB undo log records: insert_undo and update_undo. The insert_undo is for inserting records. In every other case, and also when inserting on top of a delete-marked primary key value, update_undo will be used instead.

The InnoDB undo log records are freed as follows:

insert_undo will be freed at transaction commit.
update_undo must be preserved as long as older transactions could see the old data (MVCC)
update_undo will be freed when purge has processed the records (and possibly removed the delete-marked records).

Freed undo log pages will be reused for logging modifications by newer transactions.

Because of this reuse, it is technically impossible for InnoDB to reliably go back in time by several weeks. It would require too much undo log to be preserved. Only with insert_undo records, it is safe to ignore ‘too new’ records without accessing the undo log.