4

While performing a conditional DELETE operation in one of my InnoDB tables, which apparently required some temporary table to be created in ibdata1, the hard disk filled up and mysql crashed. I couldn't get it to start again until I deleted the ibdata1 file (~30 GB).

Now mysql starts again, but all the tables in the database seem to be corrupt (when I do a REPAIR TABLE tablename EXTENDED I get: 

+-----------------------------------+--------+----------+---------+
| Table       | Op     | Msg_type | Msg_text                      |
+-----------------------------------+--------+----------+---------+
| mydb.table1 | repair | Error    | Unknown table engine 'InnoDB' |
| mydb.table1 | repair | error    | Corrupt                       |
+-----------------------------------+--------+----------+---------+

I was using innodb_file_per_table option so that all of my .frm and .ibd files (which are supposed to contain metadata and data, respectively) are all intact (with the same filesizes they had before the crash), in the directory: /var/mysql/data/mydb/. Does anyone know how I could get mysql to recognize these tables with the appropriate data once again?

Thanks!

atp
  • 30,132
  • 47
  • 125
  • 187
  • did you do a full backup before the corruption? – northpole Aug 28 '09 at 17:24
  • I only did a backup on table structures (using --no-data) since the rest of the data is enormous, ~100GB and can be repopulated through another script. But I still have these multi-GB .ibd files with respective .frm files, so I think the data is still there. Just not sure how to get MySQL to read it once again. – atp Aug 28 '09 at 17:27

2 Answers2

3

No guarantees, but you may want to look at: https://launchpad.net/percona-data-recovery-tool-for-innodb

dwenaus
  • 3,206
  • 2
  • 27
  • 27
RC.
  • 27,409
  • 9
  • 73
  • 93
3

even when you use table namespaces, the ibdata1 file still contains data that those namespaces depend on, such as the multiversion index number and the transaction log. you can't just delete that file and expect it to work.

if you're extremely lucky, you can restore/undelete the ibdata1 file and start mysql with the --innodb_force_recovery=3 option. this will allow mysql to start without attempting to rollback/rollforward any transactions.

if you still hav eproblems, you need to post your mysql server log from startup.

longneck
  • 11,938
  • 2
  • 36
  • 44