1. How to handle wrong page type in external pages

How to handle wrong page type in external pages

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

InnoDB page type is a two bytes integer stored in the header of a page. For MySQL data recovery two 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.

stream_parser reads a stream of bytes, finds InnoDB pages and sorts them per type, per index or page id. It applies sophisticated algorithms tailored for particular page type. Of course, it assumes that page type in the header corresponds to the content of the page, otherwise it will ignore 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 not everything was recovered – the most important table missed BLOB fields. Total number of recover was close to the true value, but BLOB fields were truncated. 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, the data couldn’t be overwritten, the customer stopped MySQL immediately after the accident. I decided to investigate why external pages were not 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 index page:

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. 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 InnoDB page if MySQL lies about its type? I believe the solution of this problem does exist, but for now there is a workaround.

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

To read external pages from a file (e.g. ibdata1) option -i is introduced:

After this trick the table was successfully recovered.

Have a question? Ask the experts!

Previous Post Next Post