How to resolve MySQL table is marked as crashed and last repair failed?

  Databases, Linux

If you are using the MySQL database and you encounter an error like table is marked as crashed and last repair failed ?
If this is the case this article will help you to fix it.
This error can occur due to many reasons for instance, zero disk space on the server, difficult server reset, compelled MySQL shutdown, or MySQL crashing process owing to some reason.

Some of the corrupted table’s symptoms are shown below:

  • Unexpected end of file.
  • Can’t discover file tbl name. MYI (Errcode: xxx).
  • Record file crashed.
  • Table handler xxx error.
  • tbl name.frm locked against change.

Find and fix damages tables.

The first step in finding the tables that trigger the error i

Execute these commands by order to locate the table marked as collapsed.

myisamchk -s / var / lib / mysql/*/*.MYI 

MyISAM-table ‘ /var / lib / mysql / dbname / table name. MYI ‘

The above command lists all tables marked as collapsed and must be repaired

myisamchk -r / var / lib / mysql / dbname / table name. MYI

For each table that is labeled as collapsed and needs to be repaired, we need to perform this command. Every time the table name’s value is distinct.

After stopping the MySQL service, we need to update the status of the table so that MySQL CLI understands that fraudulent tables have been checked and repaired. Execute the following order to update the table status.

myisamchk -r –update-state /var/lib/mysql/dbname/table_name.MYI

We need to restart the MySQL service after you have updated the status of each repaired table.

systemctl start mysql.service

If this method does not work, lets try the next one.

Use myisamchk to repair the table.

Login to your server using SSH command such as–ssh root@netostech.com.

Stop the MySQL daemon/service by executing the MySQL stop command service directory to your MySQL database.

systemctl stop mysql.service

Usually the directory is in /var / lib / mysql. The command to use is cd / var /lib / mysql/YOUR DATABASE NAME.

After that, execute the myisamchk command

myisamchk -r table-name.

Note : This process can increase the space in your harddrive !!!!

Restart the MySQL service by executing the MySQL command.

systemctl start mysql.service

As a result, you can fix the MySQL table and can use MySQL CLI to query tables in the database.