Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What does a case-insensitive grep for "error" show in the mysql log? It doesn't matter if the log file is large at this point. –  ekeyser Mar 19 '12 at 14:10
    
I've updated my question –  jessica Mar 19 '12 at 17:23
1  
@Matt please in the future don't turn code lines into blockquotes. –  jcolebrand Mar 20 '12 at 21:08

4 Answers 4

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

share|improve this answer
    
Thanks, I am good on backups, and I'm not having any trouble repairing the table, I just want it to stop crashing. :) –  jessica Mar 20 '12 at 22:58

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 !!!!

share|improve this answer
    
Thanks for the advice, but the MySQL server is rarely restarted, so I know that crashes are not happening from restarts. I've also had no trouble just doing the REPAIR TABLE manually when I discover it's crashed. Repairing on restart doesn't really help solve my problem as it's not preventing the table from crashing. –  jessica Mar 20 '12 at 23:00

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.

share|improve this answer
    
I'm using CentOS 5 and MySQL 5.0.22. Probably should upgrade MySQL, huh! I am the only one who accesses the server, so no one else is moving files around. There is nothing related to mysql in /var/log/messages. Workarounds would be cool if the table data was under my command, but it's a client's table, so I don't know when they'll be updating it in order to compress it. I need to use MyISAM so I can use a fulltext index. –  jessica Mar 21 '12 at 18:58

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.