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
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

2 Responses to How-to fix MyISAM table after disk full

  1. Andreas says:

    None of the above worked for me, what can I do?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: