MYSQL ERROR 1194 (HY000)

Operating system file limit size causes MYSQL ERROR 1194 (HY000): Table is marked as crashed and should being repaired problem handling

Colleague Feedback Application Report error when executing query for a table ERROR 1194 (HY000): Table is marked as crashed and should be repaired

Mysql> SELECT * from ims_stat_msg_history where module= ' basic ' and message= ' 0 ' and Createtime>=unix_timestamp (' 2015-03-04 ') LIMIT 0, 10; ERROR 1194 (HY000): Table ' ims_stat_msg_history ' is marked as crashed and should be repaired

The approximate problem caused by network search is that the index page is corrupt or the table is wrong, there are 2 solutions available on the network

• Repair   with the MYISAMCHK command; myisamchk-c -r/var/lib/mysql/wx/ims_stat_msg_history .

• Use the MySQL command   Repair table ims_stat_msg_history   Fix it.

This problem persists by using the above two solutions, the table is found to be exactly 2G (1073741312/1024/1024) * *

-RW-RW----    1 mysql    mysql     1073741312 mar 04 17:59 ims_stat_msg_history. MYD-RW-RW----    1 mysql    mysql      321451008 mar 04 18:22 ims_stat_msg_history. MYI-RW-RW----    1 mysql    mysql           8937 mar 28 2014  ims_stat_msg_history.frm 

With this lead check operating system limit ( /etc/security/limits ) found default has set Fssize to 2097151 and the file has reached the size of 2G exactly the same, here I change this parameter to 1, and then re-switch the user environment after restarting MySQL
After restarting MySQL, perform the repair again, after the repair table ims_stat_msg_history repair test, the table is back to normal!

Summarize:

• For solutions that are available on the network, it is not clear that the operation needs to be confirmed repeatedly, preferably backed up first

• For the use of the repair solution or there is a problem, need to expand the idea and direction to solve

MYSQL ERROR 1194 (HY000)