Mysql – thesql crashed and lost table descriptions “show table status” all null

MySQLserver-crashes

Due to a air-con failure the SAN with our mysql database files crashed.

When it came up there were problems with queries taking many seconds to run, further investigation revealed that the *.frm files were corrupted;

mysql> show table status;
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------------------------------------+
| Name                    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment                                                                          |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------------------------------------+
| STATUS_REPORT    | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Incorrect information in file: './discovery_prod/STATUS_REPOR | 
| bp_discovery            | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Table './discovery_prod/bp_discovery' is marked as crashed and last  | 
| bp_feedbacks            | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Incorrect information in file: './discovery_prod/bp_feedbacks.frm'   | 
| bp_language_code_master | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Incorrect information in file: './discovery_prod/bp_language_code_ma

also trying to compare seems to confirm that;

[root@db01 discovery_prod]# mysqlcheck -hlocalhost -r discovery_prod
discovery_prod.STATUS_REPORT
error    : Incorrect information in file: './discovery_prod/STATUS_REPORT.frm' 

repair table SOMETABLE output as follows;

 mysql> repair table STATUS_REPORT;
    +-------------------------------------------------+--------+----------+----------------------------------------------------------------------------------------+
    | Table                                           | Op     | Msg_type | Msg_text                                                                               |
    +-------------------------------------------------+--------+----------+----------------------------------------------------------------------------------------+
    | discovery_prod.STATUS_REPORT | repair | error    | Incorrect information in file: './discovery_prod/STATUS_REPORT.frm' | 
    +-------------------------------------------------+--------+----------+----------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

Is it possible to restore the frm files from the data files? are there any utilities that can work backwards, as the database is up and serving queries? Just really slowly…

Best Answer

Have you tried running repair table $TABLENAME; ? That will likely resolve any issues with bp_discovery.

.frm files contain the table definitions, the schema. If you are using the MyISAM engine and know your schema one potential last resort solution is as follows:

  • Backup all /data/$TABLENAME.* files
  • Drop the table.
  • Recreate the table.
  • Remove $TABLENAME.MYD and $TABLENAME.MYI files and copy your backups in place.
  • Verify table status.

At this point, you should be seriously considering restoring from backup.