Very small MySQL table keeps crashing

Question

I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per hour at peak times, and each pageview queries this table 1-2 times.

How can I figure out why this particular table keeps crashing, and how I can prevent it from crashing again in the future? After it crashes, most of the pages on my site break.

I have access to the mysql logs, but I'm not sure what exactly to look for.

EDIT: here are a couple snippets from the error log:

120317 12:35:18 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './db/tablename.MYI'; try to repair it

and then later:

120318  4:29:00 [ERROR] /usr/libexec/mysqld: Table './db/tablename' is marked as crashed and should be repaired

Answer 1

What is the OS and MySQL version that you are using?

You need to investigate to see if anyone renames or moves files manually in the data directory, as it could be the cause of corruption.

You could run a script with CHECK TABLE command on a frequent basis that would check when the table gets corrupted and maybe give you additional clues as to what causes the corruption.

Check /var/log/messages to see if there are any system errors that could be responsible.

Some possible work arounds if you don't have time to investigate:

1. Since the table is rarely updated, you could compress it making it read-only using myisampack. To prevent down time, you could work on a copy of the table in another MySQL instance or issue FLUSH TABLE command on a copy of the table before running myisampack.
2. Convert table to InnoDB engine as usually it is less prone to corruption then MyISAM.

EDIT
Yes, with such an old version there are bound to be some bugs. At least upgrade to the latest 5.0.x version (5.0.95 is the latest at the moment). Better yet, upgrade to MySQL 5.5.x.

Answer 2

The first—and most important—thing you can do before repairing a corrupted table is to back it up. Never try running a repair operation on a table without backing it up; if you fail, your data might be in worse shape than originally and may even become un-salvageable. Backing up the original table file allows you the option of trying a different technique if your first attempt doesn't work.

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Remember, don't just terminate the server with a call to "kill"; instead, use the mysqladmin SHUTDOWN command to ensure that MySQL closes all open tables gracefully and doesn't further compound your problem.

myisamcheck will be enough to get details regarding what is causing table to crash for more details you can refer this http://dev.mysql.com/doc/refman/5.0/en/myisam-check.html

Answer 3

Here is something interesting you may want to consider

If this table is very small, you can run this:

REPAIR TABLE db.tablename;

If no rows disappear after this, then it was most likely the problem of open file handles agsint the MyISAM tables. MyISAm keeps tabs on how may file handles have been opened against the table. If mysql has been shutdown and you query the table for the first time since mysql startup and the MyISAM reports it already has open file handles when you are the first to access it, the table is marked as crashed.

You can actaully run that repair on startup as follows:

STEP 01) Make a one-line script to repair the table

echo "REPAIR TABLE db.tablename;" > /var/lib/mysql/mysql-startup.sql

STEP 02) Add this file to /etc/my.cnf using the init-file option

[mysqld]
init-file=/var/lib/mysql/mysql-startup.sql

That's it !!!

Every time mysql is restarted, the repair is executed. This should be fast since there are only 16 rows.

Give it a Try !!!!

Answer 4

Switch to InnoDB. It will repair itself when your system crashes. MyISAM leaves a mess waiting to be manually REPAIRed.