Mysql – Very small MySQL table keeps crashing

myisamMySQL

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

Best Answer

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:

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

Related Solutions

MySQL Tables Require Daily Repairs – Server, Table or HD

You should get away from MyISAM as soon as possible. Why ???

MyISAM caches only index pages in the Key Buffer
InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool

I have mentioned this many times before

May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
Apr 08, 2013 : If MySQL isn't caching, then what is?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?

This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed. A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:

Switch to InnoDB
Increase installed RAM
Configure as much buffer pool as possible

Give it a Try !!!

Mysql – My database keeps crashing on startup how can i repair it without it running

I take it you are using REPAIR TABLE by hand.

I also take it your are doing an automatic table repair for MyISAM. (You have to remove or comment out the option of my.cnf to disable automatic MyISAM table repair).

You can actually repair the table without using MySQL but mysqld cannot be accessing it. How?

You have to use the offline utility myisamchk

Suppose you have the following

datadir is /var/lib/mysql
database is mydb
table is mytable

The table files would be

/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI

You can repair it with something like this

cd /var/lib/mysql/mydb
cp mytable.MY[ID] /tmp
cd /tmp
myisamchk -r mytable.MYD mytable.MYI
cd /var/lib/mysql/mydb
mv mytable.MY[ID] ..
mv /tmp/mytable.MY[ID] .

Afterwards, you can either login to mysql and do this

mysql> flush tables;

or restart mysql

service mysql restart

Give it a Try !!!

UPDATE 2014-01-08 15:46 EST

Based on the last comment

Hi Rolando, It is an Linux OS Virtual Machine. 2 Intel Xeon Core 2048 Mbyte Memory. The tables are MyISAM. I will post the rest off the data in a sec (it's hard because it keeps crashing remember)

I would stronly recommend that you get more memory. Why ?

MyISAM caches index pages to the MyISAM key buffer. MyISAM does not cache data. The mysqld process defers all data caching to the OS. That can be a little dangerous for all open MyISAM tables in a low memory DB Server.

A MyISAM table keeps a count of the number of times a file handle has been open against it. When MySQL is down, a MyISAM table with a nonzero file handle count is considered crashed.

Although I provided the answer to the question in fixing a crashed a MyISAM table without mysqld running, you have a bigger problem with a 2GB VM. It should be more like 8GB. You should also run mysqltuner.pl against the VM by doing this:

# wget mysqltuner.pl
# perl mysqltuner.pl

You can then learn if /etc/my.cnf is underconfigured.

Best Answer

Related Solutions

MySQL Tables Require Daily Repairs – Server, Table or HD

Mysql – My database keeps crashing on startup how can i repair it without it running

UPDATE 2014-01-08 15:46 EST

Related Question