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.
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
Turns out I ran into this 5-yr-old bug: http://bugs.mysql.com/bug.php?id=54828 in combination with a leftover (no idea from where) zero-byte file.
How I debugged it: I set up the query log:
which turned out to write to a file far below /tmp/systemd-private... because I'm running systemd. tail -f that file showed the crash happened when the application I'm running executed "OPTIMIZE TABLE".
Invoking that manually showed error message:
and that happened because a month-old zero-byte file already was in that place. I deleted it, and the problem went away.