After a Mac server crashed, I'm scrambling to recover records from an InnoDB table. All tables shared a single data file. I've copied ibdata1 and the two log files over to a new mysql server hosted on Windows for recovery, along with the single .frm file for the table I need. So far, recovery has failed miserably.

Trying various innodb_force_recovery options in combination with lower_case_table_names options on copies of these data files does not solve the problem. Even a normal start using copies of these files initially detects the crash and repairs it, and further startups give no errors. Deleting the ib_logfiles and having Mysql recreate them on restart does not solve the problem. Changing the .frm file name to all lower case and restarting does not solve the problem.

SHOW TABLES query shows the table, but any query on the table itself fails with error 1146. When running a innodb_table_monitor, in the output I can clearly see the table definition (more or less) and the row count. Viewing the contents of the ibdata1 file, I see all record data in plain text, but there's no usable pattern to parse it.

From what I can tell, the problem all seems to boil down to "error 1146 - Table 'db.my_table' doesn't exist" when I try to reference the table in any command or query. You see, the table was created with uppercase characters on the Mac, and according to Mysql, Windows can't handle that.

Anyone know of a solution to extract these records?


try installing Subsystem for UNIX-based Applications Thsi either installs or makes available the ability to install a *nix version of mysql that might solve your problem. Technically speaking the problem isn't that windows can't handle the upper case charactors(windows can certainly display them), the problem has to be that mysql is using case on windows which, by default, doesn't care about case and often returns filenames in all lower case (or sometime in all upper case - it depends on the particualr call).

  • I'm taking your idea a step further and installing a Linux VM. There's a few more recovery options not available on Win I'll be trying out if the Linux version of Mysql can't restore it. – bob-the-destroyer Mar 18 '11 at 6:18
  • Thanks! Though I didn't use Subsystem, I'll still choose your answer since you were technically correct. After installing Virtualbox and setting up a Linux VM complete with Mysql, I was able to simply transfer all my innodb files over to the VM, replacing any default innodb files. After adjusting the innodb_data_file_path path for my file size, the VM Mysql server started up without any issue, and I just grabbed a sql dump. – bob-the-destroyer Mar 18 '11 at 9:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.