InnoDB data recovery tool behaviour

[sgzz]

Hi all.

I am working on IDB files recovered from a VM image that was itself recovered after being accidentally deleted. Said VM was running win7 with mysql 5.6.23 and innodb_file_per_table=1 .

Unfortunately mysql fails to start when using the recovered files so I would like to extract as much data as I can from the IBD. Database structure is not an issue.
I have built the recovery tools version "release-0.5" on a linux system, generated include/table_defs.h and ran constraints_parser against the IBD file: some of the data appears to be there and looks good (the first 4 INT columns which are table key/index) but all the DATETIME columns are messed up:

t_orelavorate    144    3    125    4    "2111-44-88 34:84:12"    "9921-41-38 19:60:73"    "3292-82-73 49:02:98"    "some_text€    -2147482752    -2147483264    -2147483263    25264470    -1334282368    -128    -128    NULL    NULL    NULL    NULL    -127    0    NULL

Besided the possible file corruption, I think there might be some issue because the database was created on a Windows system.
I have created a new table on DBs  on different machines running windows and linux:

CREATE TABLE `t_fatture2` (
  `NumeroFattura` varchar(25) NOT NULL,
  `DataFattura` datetime NOT NULL,
  PRIMARY KEY (`NumeroFattura`),
  UNIQUE KEY `NumeroFattura` (`NumeroFattura`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


and inserted some simple data:

INSERT INTO `orelavoro`.`t_fatture2` (`NumeroFattura`,`DataFattura`) VALUES ("test1", str_to_date('1970,01,01 10,00,00', '%Y,%m,%d %H,%i,%s'));
INSERT INTO `orelavoro`.`t_fatture2` (`NumeroFattura`,`DataFattura`) VALUES ("test2", str_to_date('1970,01,01 10,00,00', '%Y,%m,%d %H,%i,%s'));
INSERT INTO `orelavoro`.`t_fatture2` (`NumeroFattura`,`DataFattura`) VALUES ("test3", str_to_date('2015,12,16 15,41,38', '%Y,%m,%d %H,%i,%s'));

If I run the InnoDB recovery tool on linux files the result is correct (except for the trailing garbage, but that's OK):

root@VBXDEBIAN:~/percona-data-recovery-tool-for-innodb-0.5# ./constraints_parser -f /var/lib/mysql/db1/t_fatture2.ibd -5
t_fatture2      "test1" "1970-01-01 10:00:00"
t_fatture2      "test2" "1970-01-01 10:00:00"
t_fatture2      "test3" "2015-12-16 15:41:38"
t_fatture2      "test1" "8852-15-65 43:47:40"
t_fatture2      "test2" "0000-00-00 00:00:00"
t_fatture2      "test3" "0000-00-00 00:00:00"

When I do the same on the files from windows system, the dates are all messed up:

root@VBXDEBIAN:~/percona-data-recovery-tool-for-innodb-0.5# ./constraints_parser -f /root/recovery/test/t_fatture2.ibd -5
t_fatture2      "test1" "1679-33-52 50:32:96"
t_fatture2      "test2" "1679-33-52 50:32:96"
t_fatture2      "test3" "8993-84-89 22:93:12"
t_fatture2      "test1" "8852-15-65 43:47:40"
t_fatture2      "test2" "0000-00-00 00:00:00"
t_fatture2      "test3" "0000-00-00 00:00:00"


What's going on?

[James Wang]

Vaguely remember that InnoDB file is platform independent.

I tried before:  

discard/import tablespace

flush table

innodb force_recovery to 4 (in my.cnf)

And managed to recovered a single ibd file on 5.6.  Maybe I was just luck.  Chance is that you may have to restore whole database from a backup (or copy from another server if you have one).

[sgzz]

That's what I thought too but I guess there must be some difference on how DATETIME columns are stored. I can see no other explanation for the inconsistent results.
I have tried the force_recovery method (using parameters from 1 to 6) but none seem to be working, database is still unable to start correctly.