2

Ok, in the last two days I thoroughly searched through the internet, and found multiple propositions on how to handle this, but nothing worked so far. Here is what the problem is and what I tried so far:

The company I worked for had a sudden crash of mysql, I do not know any more about this, just that the service was not working when I got on the server. Looking at the mysql log I could see the following line being responsible for the error:

InnoDB: Error: trying to access page number 64 in space 709,
InnoDB: space name db/tb_data,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.

Ok, so its a data corruption. First thing I tried, was to get mysql running again, which finally worked with innodb_force_recovery=4.

Then I connected via command line to dump all the data I could with mysqldump which worked fine except for the huge data-table, which is made up of the columns ID, record_id, field_id, content, changed_user_id, changed_timestamp.

This table links the changeable fields of the applications database and the records to the actual content of the field, and stores metadata for a history-feature. So it is the heart of the application, storing all its data.

Now the first approach I read was to dump the data and recreate the table, but dumping the data crashes mysql again with the same error (as well as check table and optimize table by the way).

So I dumped all the data I could up until the last ID I could select without the server crashing, and got all that data in an sql-file. However it is even less, than the data from the backup of four months ago, so it is not really an option to use this.

Another approach I took was to get the corrupted table out of the database, recreate it with the schema and discard the tablespace, to try and import the old one, which gives me this nice error:

ERROR 1034 (HY000): Incorrect key file for table 'tb_data'; try to repair it

After some googling of maybe how to get it to work, no chance. So also a dead end.

At this point I am simply trying to recover as much data from the ibd file as possible, I'm working on windows machines only, and I can't seem to get percona data recovery to work with cygwin. I am pretty much at the end of my wisdom here and I don't know how I should further approach this situation. The database is fine without the ibd-file, and I have the frm-file as well as the ibdata file and the corrupted ibd-file of course. But I can not seem to find any tool, working on windows, to get the records from the ibd-file.

I would be very thankful for any help on this, even if its with getting percona to work (documentation seems to be limited to the readme-file and all wiki-links are dead ends). I will of course supply as much information as you might need to assess the situation.

EDIT: I was just on the phone with the company I work for, and for what its worth, they think just before mysql shut down the database was repeatedly filled with new records from an import of one of the machines data. It is limited to inserting new rows into the records table and data table. If this info is of any help. The records table is still intact. I will check it immediately at home to see how many records were added before the crash.

EDIT2: There were about 36000 inserted records, over a time of 3 hours, beats me how nobody noticed this. This should have created about 2.3 million rows in the data-table. I don't see a problem there. It was all insert commands. The ibd-file is 6GB by the way. And there was a fulltext-index on the content column. Trying to supply as much info, that could be of interest, as I can collect.

  • If you've already done innodb_force_recovery = 4, you're way out of luck and will need to restore from backup. Per the MySQL documentation, "This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes." I would have started with 1 to get it to skip the corrupt pages. – Randolph West Oct 16 '16 at 3:43
  • It wouldn't start the server until a value of 4 so I couldn't dump them before 4. However I do have a backup of the corrupted data before tampering with the configuration. I am considering professional software at the moment, to restore the data directly from the files. I found out about stellar data recovery and brothersofts solution. But stellar will only show the corrupted table as a view. And brothersoft has problems with the timestamp column, so the demos are not showing any sign for possible recovery. I am very open for suggestions on professional solutions though. – Tschebbischeff Oct 17 '16 at 20:24
  • This is not looking good without professional expertise, and MySQL is out of my pay grade even if soliciting work was allowed. – Randolph West Oct 17 '16 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.