Fix: MySQL table is marked as crashed and last repair failed

If you are using MySQL Command-line client to query your database, you may come across an error which says:

?MySQL Table is marked as crashed and last (automatic?) Repair failed?.

The error can be caused due to many reasons including zero disk space available on the server, a hard reset of server, forced shutdown of MySQL, or MySQL process crashing due to some reason.

There are different ways to resolve this error and the right method depends on its root cause. First, we will discuss a common solution that can be used in several cases.

Solution One – Repair table using myisamchk command

1. Login in your server using SSH command like – ssh root@example.com
2. Stop the MySQL daemon/service by executing the command- service mysql stop
3. Change directory to your MySQL database. The directory is usually located in /var/lib/mysql. The command to use is cd /var/lib/mysql/YOUR_DATABASE_NAME
4. Now, you have to simply run the myisamchk command by executing – myisamchk -r table_name

Note – In the above step you have to replace the actual table name with ?table name?. The output of the command will mention the recovery of the table and also fix any corrupt records.

5. Start the MySQL service
   again by executing command – service mysql start

The MySQL table problem would be resolved now and you can query tables in the database through MySQL CLI.

Solution Two – Find and Fix broken tables

 If you are getting this error, it indicates one or more tables in the database are corrupt and need to be fixed. The first step in this solution is to find the tables that are the cause of the error. Here are the steps to follow.

To find the table marked as crashed and needs to be repaired, execute these commands

1. # myisamchk -s /var/lib/mysql/*/*.MYI
2. MyISAM-table '/var/lib/mysql/dbname/table_name.MYI' is marked as crashed and should be repaired

The above command will list all tables that are marked as crashed and need to be repaired

3. To repair the table, execute the following command – # myisamchk -r /var/lib/mysql/dbname/table_name.MYI

You need to execute this command for every table that is marked as crashed and needs to be repaired. Every time the value of table_name would be different.

4. The above solution will resolve the error. If it doesn?t you need to stop the Stop the MySQL daemon/service by executing the command- service mysql stop

After the MySQL web service is stopped you need to update the state of the tables so that MySQL CLI knows the corrupt tables have been checked and repaired. Execute the command given below to update the state of the table.

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

After you have updated the state of every repaired table, you need to restart the MySQL service. Execute the command given below to start the MySQL service.

6. service mysql start

Solution Three ? Different ways to repair corrupt tables

As discussed in the earlier solution, the easiest way to resolve the error is repairing the corrupt tables. While we have discussed one way to find and repair corrupt tables, there are alternative ways too that you would like to know. Here we have discussed different ways to find and repair corrupt tables.

Stage One ? Check Tables

Run myisamchk *.MYI or myisamchk -e *.MYI. You can also use the silent option to suppress unnecessary information. The command syntax is myisamchk –silent *.MYI

Some of the symptoms of the corrupted table manifest as queries that are aborted unexpectedly, and you get errors such as :

• Unexpected end of file
• Can’t find file tbl_name.MYI (Errcode: nnn)
• Record file is crashed
• Got error nnn from table handler
• tbl_name.frm is locked against change

To get more information about the error, use the run perror nnn, where nnn is the error number.

Stage Two ? Easy Safe Repair

After you have found tables that are corrupt, you need to try the command

1. Use myisamchk -r -q tbl_name

Here the -r -q means ?quick recovery mode. This command will attempt to repair the index file of the table without touching the data file. If the table is not repaired due to some reason, execute the commands given below.

2. Create a backup of the data file before proceeding
3. Use myisamchk -r tbl_name. Here -r means ?recovery mode?. The command removes incorrect rows and deleted rows from the data file and reconstructs the index file. If this step fails, execute the command below
4. Use myisamchk –safe-recover tbl_name. This is an old recovery method used in specific cases where the normal recovery mode fails

Since the Safe Recovery mode is slow, you need to be patient while data recovery happens. To speed up the recovery process, set the values of key_buffer_size and the sort_buffer_size variables each to about 25% of your available memory when running myisamchk.

Stage 3 – Difficult repair

Not all tables? recovery is simple. In many cases, the index file is missing or first 16KB block in the index file is destroyed or contains incorrect information. These are categorized as difficult repair and need a different approach.

To resolve such cases, you need to create a new index file. Here are the steps to follow.

1. Move data file to a safe place
2. Create new empty data file and index file using the commands given below
   • mysql db_name
   • SET autocommit=1;
   • TRUNCATE TABLE tbl_name;
   • Quit
3. Copy the old data file into the newly created data file.
4. Now go back to Stage 2 and execute the commands and they should work to recover tables.

What to do if none of these solutions work?

If you?re unable to resolve the error by using the above-mentioned methods, do not despair. You can use an automated solution to the problem. Stellar Repair for MySQL is a worthwhile and reliable solution to the ?MySQL table is marked as crashed and last repair failed? error.

The software can remove tough errors from MySQL and make the data available for restoration. It has several striking features and an interactive GUI, which makes it easy for operation. If all the available manual methods have failed, you can place your trust in this software without hesitation.

Conclusion

We hope the above solutions will work to repair corrupt tables and fix the problem in the database. After you have repaired corrupt tables, the MySQL command-line client should be able to query the database and get the required results.

Related Post