asked on

InnoDB data recovery

I am to attempting to recover data from a schema that had both MyISAM and InnoDB tables. I can recover the MyISAM tables without any problem, however I am running into difficulty trying to get the InnoDB tables back.

Steps taken:
I have backup of the schema folder in the data directory containing the .frm .myd and .myi files for the MyISAM tables and .frm for the InnoDB table. I also have the log files and the ibdata1 file. I managed to peek at the ibdata1 file with a hex editor and it clearly has references to my tables. I am starting the mysqld process with both -innodb_force_recovery=4 and -innodb_force_recovery=6 and recieve the following:

[ERROR] Cannot find or open table 'table' from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html how you can solve the problem.

From what I have read I feel I have enough to make a proper recovery, however everything I have read so far has not helped.

Thank you in advance for any help.

Umesh

Can you paste exact error.. I see table name as simply 'table' instead of actual schema name/table name.. can you post that???

Try this...

[I assume that you have back up]

1. Bring down MySQL server
2. Delete the orphan table which it's telling ( you can cd to the schema dir and instead of delete pls move it to some safe loction)

3. Bring up mysqld with innodb_force_recovery with non-zero (4 or 6 shud do)

Then check what it reports..

Umesh

Please check this once.. (I feel this one is safer compared to my first commentt)...follow the steps described here for the similar issue..

http://www.planetmysql.org/entry.php?id=16631

hemisphere24

ASKER

Thank you for your quick response.

1. I shut down the server.
2 I deleted the orphan table
3. I brought the server back up

If I understood you correctly this is what you wanted me to and nothing happened. The table do not show up at all using mysql quer browser before it would read the table structure from the .frm file, but I would get a message "Cannot fetch columns"

I have copied the error message verbatim from my original question below:

081223 12:24:46 [ERROR] Cannot find or open table captial_main/venue_type from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

Open in new window

Umesh

Please try this one...

Login to MySQL.. from mysql prompt

Create a table in some other schema with the same name as that of "venue_type".. I'm creating the table in test schema (you may create in any schema).. I'm creating with dummy strucure.. if you have the actual definition of captial_main/venue_type then you can use that instead..

mysql>use test

mysql>create table venue_type(i int) engine=innodb;

mysql>\q

Now, change directory to mysql data dir / test and copy the test/venue_type.frm to captial_main/venue_type

And make sure you change the ownership of these files to mysql..

hemisphere24

ASKER

I tried the steps you mentioned above and was not able to get the link established between the ibdata1 file and the newly created table. A quick select statement does not return any results, but I do not get any errors this time.

Here is the output:
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
081223 13:07:43 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
081223 13:07:43 [Note] Event Scheduler: Loaded 0 events
081223 13:07:43 [Note] mysql\bin\mysqld: ready for connections.

Umesh

>>081223 13:07:43 [Note] mysql\bin\mysqld: ready for connections.

That means the problem is resolved... :-)

Now what you have to do is.. just restore "captial_main/venue_type" table from the backup(schema : captial_main under that table: venue_type).. this way you will have old table..

Thanks,
Umesh

ASKER CERTIFIED SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Umesh

I think I have tried to provide the solution & it might have resolved the issue.

Thanks,
Umesh