We have a data loss: the MySQL database is dropped and we have to restore the tables from ibd files. The backup is too old so we have to undelete the deleted database files. Most of the tables are restored but 3 of them are corrupted.
We use InnoDB storage driver.
- I restored the table structure,
- then I dropped the tablespace of the table,
- and then I copied the restored InnoDB files to the database,
- and then I tried to load the original ibd file by
alter table tbl import tablespace
Most of the ibd files can be restored by this way, but 3 of them are corrupted: how can I fix them? I would like to load all of the recoverable data of the table. How can I fix the ibd file to load it?
I got a following error when I tried to load 34GB ibd datafile:
alter table xmlobjects import tablespace;
ERROR 1034 (HY000): Incorrect key file for table 'xmlobjects'; try to repair it
With innochecksum I get the following error:
innochecksum -v /var/lib/mysql/FET_TDB/xmlobjects.ibd
InnoDB offline file checksum utility.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
verbose TRUE
debug FALSE
count FALSE
start-page 0
end-page 0
page 0
file /var/lib/mysql/FET_TDB/xmlobjects.ibd = 36423331840 bytes (2223103 pages)...
InnoChecksum; checking pages in range 0 to 2223102
page 959 okay: 0.043% done
page 6783 okay: 0.305% done
page 12735 okay: 0.573% done
page 18559 okay: 0.835% done
page 24575 okay: 1.105% done
page 30527 okay: 1.373% done
page 36287 okay: 1.632% done
...
page 2213695 okay: 99.577% done
page 2221247 okay: 99.917% done
Error; bytes read (12288) doesn't match page size (16384)
mysql error log:
2017-09-15 08:57:55 1 [Warning] IP address '172.17.0.1' could not be resolved: Name or service not known
2017-09-15 08:57:55 1 [Warning] InnoDB: Table '"FET_TDB"."xmlobjects"' tablespace is set as discarded.
2017-09-15 08:57:55 7f80bbeaf700 InnoDB: cannot calculate statistics for table "FET_TDB"."xmlobjects" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2017-09-15 08:57:55 1 [ERROR] InnoDB: File size 36423331840 is not a multiple of the page size 16384
2017-09-15 08:57:55 1 [Note] InnoDB: Discarding tablespace of table "FET_TDB"."xmlobjects": Data structure corruption
Other tables:
alter table xmlindex__mo import tablespace;
ERROR 2013 (HY000): Lost connection to MySQL server during query
And I see the following messages in the log file:
2017-09-15 08:39:52 1 [Warning] IP address '172.17.0.1' could not be resolved: Name or service not known
2017-09-15 08:39:52 1 [Warning] InnoDB: Table '"FET_TDB"."xmlindex__mo"' tablespace is set as discarded.
2017-09-15 08:39:52 7f33f0570700 InnoDB: cannot calculate statistics for table "FET_TDB"."xmlindex__mo" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
ls -la /var/lib/mysql/FET_TDB/xmlindex__mo.ibd
-rw-rw---- 1 mysql mysql 2759852032 Sep 15 08:39 /var/lib/mysql/FET_TDB/xmlindex__mo.ibd
checksum output seems to be correct:
innochecksum -v /var/lib/mysql/FET_TDB/xmlindex__mo.ibd
InnoDB offline file checksum utility.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
verbose TRUE
debug FALSE
count FALSE
start-page 0
end-page 0
page 0
file /var/lib/mysql/FET_TDB/xmlindex__mo.ibd = 2759852032 bytes (168448 pages)...
InnoChecksum; checking pages in range 0 to 168447
page 9983 okay: 5.927% done
page 26623 okay: 15.805% done
page 32639 okay: 19.377% done
page 38399 okay: 22.796% done
page 44223 okay: 26.254% done
page 49215 okay: 29.217% done
page 60543 okay: 35.942% done
page 66239 okay: 39.324% done
page 72127 okay: 42.819% done
page 86719 okay: 51.482% done
page 92671 okay: 55.015% done
page 98559 okay: 58.511% done
page 105087 okay: 62.386% done
page 139903 okay: 83.055% done
page 159359 okay: 94.605% done
