So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

With more elaborate details in error log:

081217 15:59:11 [ERROR] Cannot find or open table test/test_innodb 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.

I would much rather see MySQL to report some more reasonable error message, something like Storage Engine could not open table or something like it.

So what we can do is to make sure Innodb has something in its data dictionary so it allows MySQL to succeed displaying .frm file contents:

mysql> create table test_innodb(i int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> Aborted
[root@test3 test]# cp /tmp/test_innodb.frm .
cp: overwrite ./test_innodb.frm'? y
[root@test3 test]# mysql test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table test_innodb;
+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE test_innodb (
a int(11) DEFAULT NULL,
b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+————-+—————————————————————————————————————————-+
1 row in set (0.00 sec)

Note: I have created the table which have a different definition from original table. It is good enough to get SHOW CREATE TABLE but do not try to use it any other way, as nasty things may happen:

Recovering CREATE TABLE from .frm file for MyISAM Table

We do not really need this that frequently but I decided to cover this for completeness anyway.

With MyISAM table MySQL comes with another creative error message if .frm is the only file which exists:

This is closer to the truth though file name is wrong – there should be test_myisam.MYI or test_myisam.MYD in the error message. The file with name “test_myisam” does not need to be exist.

The intuitive way to rebuild MyISAM table would be REPAIR TABLE test_myisam USE_FRM, however it does not work… just yet.

You need to create the .MYI and .MYD files for the table to make it work:

[root@test3 test]# touch test_myisam.MYI
[root@test3 test]# touch test_myisam.MYD
[root@test3 test]# chown mysql:mysql *

So it is not at all that complicated.

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Alex

Peter,
I’ve got a MySQL 5.0.75 server with a couple of orphaned InnoDB .frm files. I tried using the process you described above, but the “show create table” gives a “Lost connection to MySQL…” and subsequently reboots MySQL!
Is this something that only works on 5.1+?
Thank you!

0
john

I have MySQL 5.0.27 server. I had similar error and tried your method but get error that table already exists.

mysql> show create table wp_63_posts;
ERROR 1146 (42S02): Table ‘comwordpress.wp_63_posts’ doesn’t exist
mysql> create table wp_63_posts(i int) engine=innodb;
ERROR 1050 (42S01): Table ‘wp_63_posts’ already exists

So ‘show create table’ says table doesn’t exist. But ‘create table’ says it already exists.
I’m confused ???

0
Helogat

I have the tables and databases in the mysql datafolder, but i get this when restoring from frm: http://helogat.homeserver.com/webhosting/rex/upload/570832zooi.png

0
Farshad

when I run this query to recover my data :

mysql> insert into test_innodb values(1,2,3,4,5,6,7,8,9);

it returns

ERROR 2013 (HY000): Lost connection to MySQL server during query

and no row recovered from .frm file

0
Mike Northrop

When I run the repair on my table it says “Can’t open table”. I have permissions 660 in linux, do I need something else?

0
Jonathan Petersson

Thank you, save my day!

0
Xavier Hienne

Great tip, worked for me (mysql 5.0). Thank you!

0
biz

Thanks a lot for this! Worked like a charm and saved me from having to transfer an unneeded 16GB .MDY file over a slow network.

0
wilf1rst

Thanks !!
I search a lot about that kind of errors and once again percona save me.

0
rodmi714

Thank you

0