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 withbp_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:
/data/$TABLENAME.*
files$TABLENAME.MYD
and$TABLENAME.MYI
files and copy your backups in place.At this point, you should be seriously considering restoring from backup.