How-to fix MyISAM table after disk full

Recently, one of MySQL host run into disk full issue. This make one of the table corrupted.

mysql> show create table poller_output;
ERROR 1017 (HY000): Can't find file: 'poller_output' (errno: 2)

[cacti]# ls poller_output*
poller_output.frm

As you can see, the MYD, MYI file of the table are missed.

We can simply truncate the table to fix it:

mysql> truncate table poller_output;
Query OK, 0 rows affected (0.00 sec)

--probably you can use below commands to fix them if you need the data there.
SQL> check table poller_output;
SQL> repair table poller_output;

The table is back 🙂

mysql>  show create table poller_output;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                             |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| poller_output | CREATE TABLE `poller_output` (
  `local_data_id` mediumint(8) unsigned NOT NULL default '0',
  `rrd_name` varchar(19) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `output` text NOT NULL,
  PRIMARY KEY  (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from poller_output;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

[cacti]#  ls poller_output*
poller_output.frm  poller_output.MYD  poller_output.MYI