After nearly every recovery case the same question arises: How many MySQL records were recovered and how many were lost.
Until now there was no way to answer the question without manual investigation. As it turned out a small change can make a big difference.
There are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS – this is a number of records the page stores. Obviously it doesn’t count any deleted records. The second method to confirm how many records are in the page is to follow records pointers – and count them.
As you might know, records inside an InnoDB page are organized in an unidirectional list. The list is sorted by primary key and starts with the internal record infinum
and ends with another internal record supremum
. If you follow record pointers from the infinum and end up with the supremum record then two conclusions can be make:
- The page structure is not corrupt
- You know how many records to expect
This is what constraints_parser
does before it tries to find any records in the page:
./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V ... Checking a page Infimum offset: 0x63 Supremum offset: 0x70 Next record at offset: 0x7F (127) Next record at offset: 0xA8 (168) Next record at offset: 0xCE (206) ... Next record at offset: 0x70 (112) Page is good Format: COMPACT, Records list: Valid, Expected records: (200 200)
Here you can see “Expected records”: 200 in the header and 200 records were found by pointers.
Then the contraints_parser tries to recover the records. It checks whether fields have permitted values, etc. In the end it knows how many records were actually recovered.
This statistics are printed along with the recovered records:
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (200 200) 000000001917 DB000001720110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33" 000000001917 DB00000172011B actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33" 000000001917 DB000001720126 actor 3 "ED" "CHASE" "2006-02-15 04:34:33" 000000001917 DB000001720131 actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33" ... 000000001917 DB0000017209E4 actor 200 "THORA" "TEMPLE" "2006-02-15 04:34:33" -- Page id: 3, Found records: 200, Lost records: NO, Leaf page: YES
From the output above we know that 200 records were expected, 200 – recovered. So, no records were lost.
It’s worth noting that records are stored in the leaf pages only, so if an index has more than one page no records will be found in the root page.
For the root page it will print “Leaf page: NO” and it can be ignored.
The post Finally. How to verify if all MySQL records were recovered appeared first on MySQL Performance Blog.