Wednesday 8 February 2012

How to recover a currupted InnoDB table.

I was really in Problem when one of my InnoDB table get corrupted I posted My Question on dba.stackexchange.com.
The issue was as

mysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error | Corrupt |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)


Then I Created a test scenario and found solution

I created a table called test2

CREATE TABLE `test2` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then inserted one record

insert into test2 values(1);

I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.

I then started MySQL and saw the following

mysql> show table status \G;
*************************** 1. row ***************************
Name
: test2
Engine
: NULL
Version
: NULL
Row_format
: NULL
Rows
: NULL
Avg_row_length
: NULL
Data_length
: NULL
Max_data_length
: NULL
Index_length
: NULL
Data_free
: NULL
Auto_increment
: NULL
Create_time
: NULL
Update_time
: NULL
Check_time
: NULL
Collation
: NULL
Checksum
: NULL
Create_options
: NULL
Comment
: Incorrect information in file: './test2/test2.frm'

I then created another table with the same structure

CREATE TABLE `test3` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then stopped mysql and copied test3.frm over test2.frm

cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frm

I restarted MySQL and had access to test2

mysql> show table status \G;
*************************** 1. row ***************************
Name
: test2
Engine
: InnoDB
Version
: 10
Row_format
: Compact
Rows
: 1
Avg_row_length
: 16384
Data_length
: 16384
Max_data_length
: 0
Index_length
: 0
Data_free
: 25675431936
Auto_increment
: NULL
Create_time
: 2012-02-07 16:24:20
Update_time
: NULL
Check_time
: NULL
Collation
: latin1_swedish_ci
Checksum
: NULL
Create_options
:
Comment
:

Here is the record that I inserted

mysql> select * from test2;
+------+
| t |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one

No comments:

Post a Comment