INNODB crash recovery possibilities

MySQL & MariaDB Other MySQL® Questions
1

Hi Folks,

Is there a comprehensive list of cases the INNODB detects & does crash recovery?

one case I know is : when a snapshot is taken for backup and trying to restore that , it detects there are dirty pages yet to written to disk from redo logs and invokes crash recovery.

I am interested to know the complete list of cases that INNODB looks for in deciding whether Crash recovery has to be started OR not.

2

May I ask what the reason is?

From a developer/DBA point of view the only thing you should need to know is that InnoDB is Durable (from ACID) and makes sure to keep it’s own files in a proper state.

The snapshot backup scenario that you are referring to is because you are actually “stealing” a copy of the files from under InnoDB’s nose and since InnoDB due to performance reasons have caching built in, all data is not written to the tablespace data files directly (in contrary to the transaction logs which must be written directly or else a power outage can cause you to loose data).

So the copy that the snapshot retrieves is not in a consistent state and that is what InnoDB detects and fixes by using the transaction logs that contains the missing data.

3

Thank you for the reply Sterin.

There is no specific reason, I am trying to understand how crash recovery is detected. At times I have to understand why it did crash recovery when I restart my server.

The cache that is built is “double write buffer” that ensures ACID compliant for INNODB tables, isn’t it?

I am sure most of the senior DBAs would have encountered lot of cases where Recovery process is invoked.