beta.blog

When operating a MySQL server (or a similar server such as MariaDB), one may encounter an error message after launching the server reading:

ERROR 144 – Table (TABLE_NAME) is marked as crashed and last (automatic?) repair failed

Usually this kind of error wouldn’t occur as it would have been fixed automatically, however, the error message suggests doing so had failed previously.

In order to resolve this problem we should stop the mysql server:

sudo service mysql stop

Afterwards we change directory (cd) to the respective folder containing the mysql database (db) files:

cd /var/lib/mysql/DATABASE_NAME/

We’re now going to use a tool called myisamchk. As stated on mysql.com: The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes).

myisamchk -r TABLE_NAME

The -r flag will tell the myisamchk tool to repair the database. We might as well append the -v flag in order to request verbose output for debugging purposes while at the same time repairing the database:

myisamchk -r -v TABLE_NAME

If the tool fails with an error claiming the repair process was cancelled due to unresolvable errors, one may apply the -f parameter to force repairing the table. However, keep in mind that this may cause data corruption (!)

myisamchk -r -v -f TABLE_NAME

Another issue we might stumble upon is an error reading:

myisamchk: error: myisam_sort_buffer_size is too small

This may happen particularly for tables containing huge datasets. An example would be the web forum software MyBB. We can increase the buffer size used by myisamchk by applying the –sort_buffer_size parameter:

myisamchk -r -v -f –sort_buffer_size=512M TABLE_NAME

The command above will a buffer size of 512 MB (!) instead of the default buffer size of 2 MB. It is even possible to use way larger buffer sizes such as –sort_buffer_size=2G. Keep in mind, though, that you might hit hardware limitations when playing with this parameter.

After repairing the corrupt tables we may restart MySQL:

sudo service mysql start