Re: Power failure and innodb
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.
Subject
Views
Written By
Posted
8138
January 27, 2014 09:45AM
2717
January 28, 2014 08:42AM
2761
February 24, 2014 06:21AM
2216
March 05, 2014 08:15AM
2072
March 05, 2014 10:42PM
1937
March 07, 2014 07:46AM
Re: Power failure and innodb
1438
February 02, 2015 09:33AM
1413
February 02, 2015 11:43AM
1520
February 03, 2015 05:09AM
1842
February 03, 2015 05:40AM
1472
February 03, 2015 07:04PM
1890
February 04, 2015 01:03PM
1160
June 04, 2015 07:35AM
2591
March 31, 2014 06:16AM
1455
March 18, 2015 11:12AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.