Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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_trx_commit  = 1
innodb_lock_wait_timeout        = 50
innodb_force_recovery           = 4 
share|improve this question
    
Do you have a backup? –  gbn Feb 7 '12 at 7:56
    
@gbn: No i do not have backup thats why i have asked. –  Abdul Manaf Feb 7 '12 at 8:38
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
    
owner:group is as mysql:mysql –  Abdul Manaf Feb 7 '12 at 12:11
    
+1 that was my initial thought but didn't have time to test it as you did. –  Derek Downey Feb 7 '12 at 16:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.