MySQL Error Incorrect key file. myisamchk repair works but table corrupts again soon

Question

my first question on stackoverflow so I hope I have followed all the guidelines.

I have a website with MySql db on a Digital Ocean droplet.

I found mysql had stopped running and after looking found corrupt tables. After repairing them with myisamchk -r I thought the problem was solved. However it kept occurring. I changed innodb_buffer_pool_size = 20M in my.cnf and a have created some free space on /dev/vda/ after reading this MySQL, Error 126: Incorrect key file for table

Filesystem     1K-blocks     Used Available Use% Mounted on
udev              239664        4    239660   1% /dev
tmpfs              50176      380     49796   1% /run
/dev/vda1       20511356 12346408   7099992  64% /
none                   4        0         4   0% /sys/fs/cgroup
none                5120        0      5120   0% /run/lock
none              250872        0    250872   0% /run/shm

I have these errors in the mysql/error.log

[ERROR] /usr/sbin/mysqld: Incorrect key file for table './websitename/wp_options.MYI'; try to repair it
[ERROR] Got an error from thread_id=178, /build/mysql-5.5-heGK82/mysql-5.5-5.5.55/storage/myisam/mi_update.c:226
[ERROR] MySQL thread id 178, OS thread handle 0x7f4544010700, query id 69124 localhost th3489075 Updating

When I run myisamchk db_name.MYI on the corrupt table I get

Checking MyISAM file: wp_options.MYI
Data records:     375   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size    
myisamchk: error: Size of datafile is: 1059228           Should be: 1059276
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
myisamchk: error: Keypointers and record positions doesn't match
myisamchk: warning: Found        375 key parts. Should be: 377
MyISAM-table 'wp_options.MYI' is corrupted
Fix it using switch "-r" or "-o"

Repairing the table with myisamchk -r or -o options seems to work as running myisamchk *.MYI immediately after reports no problems but later on it returns the error above.

I have various backups of the db but am unsure how different they are from the current ones.

I understand the warning myisamchk: warning: 1 client is using or hasn't closed the table properly occurs if mysql is running.

When mysql is stopped myisamchk *.MYI reports all ok but mysql still crashes.

Answer 1

Every time there is a power failure or other abrupt termination of mysqld, MyISAM tables may be corrupted. This is a strong reason to switch to InnoDB, which does a better job of protecting itself and automatically repair upon restart.

Meanwhile, you could try OPTIMIZE TABLE as a way of forcing a rebuild of the table. This may or may not lose the data that seems to be missing, but it will probably get rid of the error message until the next crash.