1. How to Handle Wrong Page Type In External Pages

How to Handle Wrong Page Type In External Pages

Mainly, the first step to successful MySQL data recovery is finding InnoDB pages with your data. Let’s call it the first step, because prerequisite steps are already done.

An InnoDB page type is a two bytes integer in the header of a page. For MySQL data recovery two of them are important:

  • FIL_PAGE_INDEX. Pages of this type are nodes of B+ Tree index where InnoDB stores a table.
  • FIL_PAGE_TYPE_BLOB. So called external pages, where InnoDB keeps long values of BLOB or TEXT type.

Therefore, stream_parser reads a stream of bytes, finds InnoDB pages and sorts them by type, index, or page id. It applies sophisticated algorithms tailored for a particular page type. Of course, it assumes that page type in the header corresponds to the content of the page, otherwise it ignores the page.

Recently, I worked on a data recovery case that proved I was wrong in my assumptions. The customer dropped their database. They ran MySQL 5.0 with innodb_file_per_table=OFF. This is one of the easiest recovery scenarios, however, we couldn’t recover everything – the most important table was missing BLOB fields. Total number of recovers was close to the true value, but BLOB fields weren’t complete. Excessive “— #####CannotOpen_FIL_PAGE_TYPE_BLOB/0000000000000XYZ.page”” errors in the standard error output proved that stream_parser failed to find external pages.

Something went wrong, overwriting data was impossible, the customer stopped MySQL immediately after the accident. I decided to investigate why external pages couldn’t be found. Page_id is a file offset in 16k units. dd can extract particular page X:

Here’s the header of page id 8, which is the index page:

The FIL_PAGE_INDEX constant is defined as 17855 in MySQL. In hexadecimal, it’s 0x45BF. It’s at position 0x18 in the example above.

And here’s an example of the external page:

Page type is 0x0A, as it should be.

When I extracted a page that stream_parser couldn’t find, it became clear why. The page type was 0x45BF! The page was a BLOB page, but the page type in the header was FIL_PAGE_INDEX.

How can you detect an InnoDB page if MySQL lies about its type? I believe the solution of this problem does exist, but for now there’s a workaround.

c_parser by default reads external pages from directory specified by -d option:

The -i option helps read external pages from a file (e.g. ibdata1):

After this trick, the table was successfully recovered.

Have a question? Ask the experts!

Previous Post Next Post