HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to recover a crashed innodb table...?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
crashedinnodbrecoverhowtable

Problem

I Have a Innodb table i got the error 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)


I don't have changed anything in my.cnf.I don't know how it get crashed.
How can i recover it
...

Content of error log are as

120207 11:50:47 InnoDB: highest supported file format is Barracuda.
120207 11:50:47  InnoDB: Waiting for the background threads to start
120207 11:50:48 InnoDB: 1.1.8 started; log sequence number 5713326
120207 11:50:48 [Note] Event Scheduler: Loaded 0 events
120207 11:50:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.20-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
120207 11:50:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: './world_innodb/City.frm'


EDIT

Someone has opened the City.frm file and modified it's content.So How will I recover it.? I am using innodb_file_per_table.

UPDATE: Adding my.cnf as asked by some peoples.

Innodb Specific variables in My.Cnf

```
innodb_file_per_table
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_log_at_tr

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

Code Snippets

CREATE TABLE `test2` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into test2 values(1);
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'
CREATE TABLE `test3` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frm

Context

StackExchange Database Administrators Q#12370, answer score: 3

Revisions (0)

No revisions yet.