How do I Fix a Corrupt MySQL Table?

mysqldump Can’t open file: ‘tablename.MYD’. (errno: 145)

This usually means the table has become corrupt. Most commonly this happens when the partition that contains your MySQL files is full, but I have had it happen at other times. Especially on tables that get a lot of access like web sessions that get created and deleted often.

Now before you start kicking yourself because the last time you backed up was three years ago when you had your last strange crash you can try executing the following SQL command…

1
REPAIR TABLE MyTableName

That usually does wonders. Of course you will may need to resolve the underlying issues first, delete some files if your drive is full.

If it works for you (or even if it doesn’t) go set up a cron job right now to automatically back up your database regularly. Believe me, the piece of mind you will get from that when a problem arises is more than worth the time to set it up and the space to store it.

Another thing you might want to think about at a time like this is your storage engine. Yeah, I know, just what you want to think about. But the thing is, the default engine for older versions of MySQL “MyISAM” was screaming fast but the trade-off for that speed was less fault tolerance than might be expected from a modern DB. There are other options you might want to choose especially if you don’t have a UPS on your server, but you should be aware of the tradeoffs before you make the change. I’ll talk more about this in the near future.

Category(s): Database, MySQL, Server Administration
Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

 

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Spam protection by WP Captcha-Free