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:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
2. +----------------+
3. | Tables_in_test |
4. +----------------+
5. | queue          |
6. | test_innodb    |
7. +----------------+
8. 2 rows IN SET (0.00 sec)
9.  
10. mysql> SHOW CREATE TABLE test_innodb;
11. ERROR 1146 (42S02): TABLE 'test.test_innodb' doesn't exist

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:

PLAIN TEXT SQL:

1. mysql> INSERT INTO test_innodb VALUES(1,2);
2. Query OK, 1 row affected (0.00 sec)
3.  
4. mysql> SELECT * FROM test_innodb;
5. ERROR 2013 (HY000): Lost connection TO MySQL server during query

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:

PLAIN TEXT SQL:

1. mysql> SHOW CREATE TABLE test_myisam;
2. ERROR 1017 (HY000): Can't find file: 'test_myisam' (errno: 2)

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 *

PLAIN TEXT SQL:

1. mysql> repair TABLE test_myisam USE_FRM;
2. +------------------+--------+----------+----------+
3. | TABLE            | Op     | Msg_type | Msg_text |
4. +------------------+--------+----------+----------+
5. | test.test_myisam | repair | STATUS   | OK       |
6. +------------------+--------+----------+----------+
7. 1 row IN SET (0.00 sec)
8.  
9. mysql> SHOW CREATE TABLE test_myisam;
10. +-------------+----------------------------------------------------------------------------------------------------------------------------+
11. | TABLE       | CREATE TABLE                                                                                                               |
12. +-------------+----------------------------------------------------------------------------------------------------------------------------+
13. | test_myisam | CREATE TABLE `test_myisam` (
14.   `a` int(11) DEFAULT NULL,
15.   `b` int(11) DEFAULT NULL
16. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
17. +-------------+----------------------------------------------------------------------------------------------------------------------------+

So it is not at all that complicated.

Entry posted by peter | No comment

Add to: | | | |