A few days ago, I was trying to update a record in my database and encountered an error message in the form of “Incorrect key file for table xxx.MYI”. Since the update was not really that important, I decided to ignore that warning but this was a very big mistake that I would regret later. I went out for the day and when I came back at night, I found that my traffic has plummetted and when I investigated, I found that the table that was having problems earlier has crashed. So I decided to find reasons why MySql tables would crash and they are as follows:
- Unstable operating system
- Problems with the hardware
- Power failure
- Corrupted data or index files
- MySql server host was killed during an update
- External program manipulating the data or index files at the same time as MySqlId without locking the table properly
There may be other causes for MySql to crash but the reasons above are more common.
How to repair a crashed MySql table?
Fortunately MySql tables which have been crashed can be repaired quite easily. All you have to do is use PhpMyAdmin or another MySql client program and run the repair command on that table (REPAIR TABLE employee). This will bring back the table in a consistent state. You can also use CHECK TABLE employee to see if there are any problems with the tables in your database. There are additional options that you can use when repairing/checking your tables but using the syntax without them should work just fine.
How detrimental can it be to have crashed MySql tables?
The MySql table in my database crashed around 15:00 and it was not until 22:00 that I noticed the problem. That’s the problem when you’re on shared hosting – you don’t get any notifications of problems and when you email support, it takes time for them to get the problem fixed. My website is database driven which means that if the database is down, the whole website is brought down. Running the repair command took less than a minute though and everything was back up after the table was repaired.
However I lost half of my daily traffic within that time and lost half of my revenue as well. Of course, having a website that’s not working is not good for your visitors and losing money is not something that you want but those are not the biggest problems. For the duration of the downtime, search engine spiders (GoogleBot) may be crawling your website and when they encounter problems with the site, they can quickly remove your webpages out of their index and that will cost you even more than 1 day of downtime. Your rankings might suffer as well as a result of the downtime.