MySQL Tables crashing randomly

Question

This is one of the many random Tables that get corrupted. Any ideas why and what would be causing this?

How do I keep MySQL tables from crashing and MySQL from crashing?

Repairing USR_wp537

USR_wp537.rev_commentmeta                     OK
USR_wp537.rev_comments                        OK
USR_wp537.rev_links                           OK
USR_wp537.rev_options                         OK
USR_wp537.rev_postmeta                        OK
USR_wp537.rev_posts
Error    : Incorrect key file for table './USR_wp537/rev_posts'; try to repair it
Error    : Incorrect key file for table 'rev_posts'; try to repair it
error    : Corrupt
USR_wp537.rev_term_relationships              OK
USR_wp537.rev_term_taxonomy                   OK
USR_wp537.rev_terms                           OK
USR_wp537.rev_usermeta                        OK
USR_wp537.rev_users      

Eventually the only way to fix is to do a mysql> REPAIR TABLE <tbl> USE_FRM;

This is also mysql 5.5

top - 20:17:11 up 4 days, 8:57, 1 user, load average: 0.41, 0.37, 0.36 Tasks: 204 total, 1 running, 203 sleeping, 0 stopped, 0 zombie Cpu0 : 11.0%us, 0.3%sy, 0.0%ni, 88.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 5.0%us, 1.0%sy, 0.0%ni, 93.4%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st Cpu2 : 10.4%us, 0.7%sy, 0.0%ni, 89.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 1.3%us, 0.3%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 16288440k total, 15292940k used, 995500k free, 1398928k buffers Swap: 8191992k total, 0k used, 8191992k free, 9351404k cached

my.cnf

[mysqld]
core-file
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 40
key_buffer = 500M
myisam_sort_buffer_size = 32M
join_buffer_size = 256M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16K
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 160M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
myisam_repair_threads=4
[mysqld_safe]
core-file-size = unlimited
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

EDIT: Looks like MySQL for some reason did a restart?

130131 20:30:59 [ERROR] Got an error from thread_id=817995, /builddir/build/BUILD/mysql-5.5.28/mysql-5.5.28/storage/myisam/mi_write.c:223
130131 20:30:59 [ERROR] MySQL thread id 817998, OS thread handle 0x7f8589e2a700, query id 24842525 localhost ahmad_wp Waiting for table level lock
SELECT option_value FROM wp_options WHERE option_name = 'widget_momizat-posts-images' LIMIT 1
130131 20:30:59 [ERROR] MySQL thread id 817995, OS thread handle 0x7f858692f700, query id 24842524 localhost ahmad_wp update
INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:91:{s:27:\"typename/([0-9]{4})/(.+)/?$\";s:30:\"index.php?typename=$matches[2]\";s:47:\"category/(.+?)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:42:\"category/(.+?)/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:35:\"category/(.+?)/page/?([0-9]{1,})/?$\";s:53:\"index.php?category_name=$matches[1]&paged=$matches[2]\";s:17:\"category/(.+?)/?$\";s:35:\"index.php?category_name=$matches[1]\";s:44:\"tag/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:39:\"tag/([^/]+)/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:32:\"tag/([^/]+)/page/?([0-9]{1,})/?$\";s:43:\"index.php?tag=$matches[1]&paged=$matches[2]\";s:14:\"tag/([^/]+)/?$\";s:25:
    130131 20:33:42 mysqld_safe Number of processes running now: 0
    130131 20:33:42 mysqld_safe mysqld restarted
    130131 20:33:42 [Note] libgovernor.so found
    130131 20:33:42 [Note] All governors functions found too
    130131 20:33:42 [ERROR] Governor not connected
    130131 20:33:42 [Note] All governors lve functions found too
    130131 20:33:42 [Note] Plugin 'FEDERATED' is disabled.
    130131 20:33:42 InnoDB: The InnoDB memory heap is disabled
    130131 20:33:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    130131 20:33:42 InnoDB: Compressed tables use zlib 1.2.3
    130131 20:33:42 InnoDB: Using Linux native AIO
    130131 20:33:42 InnoDB: Initializing buffer pool, size = 128.0M
    130131 20:33:42 InnoDB: Completed initialization of buffer pool
    130131 20:33:42 InnoDB: highest supported file format is Barracuda.
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    130131 20:33:42  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer...
    130131 20:33:42  InnoDB: Waiting for the background threads to start
    130131 20:33:43 InnoDB: 1.1.8 started; log sequence number 5726524860
    130131 20:33:43 [Note] Event Scheduler: Loaded 1 event
    130131 20:33:43 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server (GPL)

EDIT: Drive was told to be good by my datacenter EDIT2: Currently running mcelog to check RAM

Answer 1

Generally, tables will become corrupt only when a write operation is interrupted. MySQL uses a write() system call, so even if the server crashes, as long as the OS is still operative the data will be safe.

If many different tables are getting repeatedly corrupted, I would suspect hardware problems first. Have the system taken down and RAM and the hard drives tested. Bad RAM cells or disk sectors can lead to this type of table corruption.

After the above hardware checks, before you start up mysqld, I would make sure you are starting with a clean slate by running myisamchk --silent --force */*.MYI first. Then start mysqld. If corruption occurs again, check the error log for a corresponding system crash. If you find corruption even in the absence of a mysqld crash, you may have found a bug: Try to isolate a particular query that occurs just before (you'll need to have the general query log enabled). If you can kill the server with a specific query repeatedly, you have found a bug and should submit it.

If you are running multiple instances of mysqld, or running myisamchk while mysqld is running, it might be that the lock manager for your system is not working properly. If you are NOT running multiple instances, try using the --skip-external-locking option to remove the lock manager from the mix of possibilities.

Some versions come with testing tools. They are in a directory call mysql-test and/or mysql-bench or similar. Try those out. Also try to emulate your application on a different system to see if you can replicate the crashes/corruption.