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

Synopsis: In this post, know the best methods to resolve MySQL table is marked as crashed and last repair failed.

If you are using the MySQL database then you encounter an error related to the MSQL table. So, this article will help you to fix this blunder. However, 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.

Moreover, there are distinct methods to fix this mistake and it depends on the root cause of the correct technique. So, without wasting any more time, read ahead!

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

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

Methods to resolve MySQL table is marked as crashed and last repair failed: 

Let us go through some of the most effective methods to fix this MySQL error. These are also some of the tricks that you can use to make your Mysql Server work again to its basics.

Method 1: Find and fix the damage Tables

If this mistake occurs, it shows that one or more tables are corrupt in the database and need to be fixed. The first step in finding the tables that trigger the mistake is in this solution. Here are the measures that you can implement.

Execute these orders to locate the table marked as collapsed and need to be repaired.

• Myisamchk -s / var / lib / mysql/*/*.MYI 
• MyISAM-table ‘ /var / lib / mysql / dbname / table name. MYI ‘ should be rebuilt and marked as crashed.

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

• Run the command # myisamchk -r / var / lib / mysql / dbname / table name. MYI to repair the table.

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

• After stopping the MySQL web service, you 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

You need to restart the MySQL service after you have updated the status of each repaired table. Run the following order to begin the MySQL service.

• Start service MySQL

If this method does not work for you, then you can try the other methods given below.

Method 2: Use myisamchk to repair the table

1. First of all, Login to your server using SSH command such as–ssh root@example.com.
2. Stop the MySQL daemon/service by executing the MySQL stop command service directory to your MySQL database. 
3. Usually the directory is in /var / lib / mysql. The command to use is cd / var /lib / mysql/YOUR DATABASE NAME.
4. After that, execute the myisamchk command–myisamchk-r table name.
5. Restart the MySQL service by executing the MySQL command.

As a result, you can fix the MySQL table and can use MySQL CLI to query tables in the database. Moreover, If your database gets corrupted and you can’t access them so you can use the different methods to recover corrupted MySQL Database.

Method 3: Different Ways to repair Corrupt tables

As discussed in the previous solution, repairing corrupt tables is the simplest way to fix the mistake. While we’ve talked about a way to discover and repair corrupt tables, there are other methods you’d like to explore. As a result, I have addressed various methods of finding and repairing corrupt tables here.

1. Check Tables

Run * .MYI or myisamchk -e * .MYI. To remove unnecessary data, you can also use the silent option. The syntax of the command is myisamchk * .MYI

As I have explained above what can be the symptoms of the corrupted tables. So, the first step is to check the table by the below command.

Use the run error nnn, where nnn is the number of errors, to get more data about the error.

1. Safe Repair

After finding corrupt tables, you need to try the command

• Use myisamchk -r -q tbl_name

The -r -q implies “fast mode of recovery.” This command attempts to repair the table index file without touching the information file. If for some reasons you are not able to repair the table, perform the following steps.

1. Use myisamchk-r tbl name to create an information file backup before proceeding. In this case -r implies “mode of recovery.” 
2. The command removes from the data file inaccurate rows and deleted rows and recreates the index file. However, if this phase fails, use myisamchk –safe-recover tbl name to perform the command below. 
3. This is an old technique of recovery used in instances where ordinary recovery mode fails.

Since the Safe Recovery method is slow while recovering the data, you need to be patient. Set key buffer size values and sort buffer size variables to approximately 25 percent of your accessible memory when running myisamchk to speed up the retrieval process.

3. Difficult Repair

Recovery is not easy for all tables. The index file is missing in many cases or it destroys the first 16 KB block in the index file or includes inaccurate data. These are classified as hard to repair and require a distinct strategy.

Finally, you need to generate a fresh index file to fix such instances. Here are the measures that you can take.

Steps:

1. First, shift data files to a safe place. 
2. Using the command below creates a new empty data file and index file MySQL db_name

• SET autocommit=1;
• TRUNCATE TABLE tbl_name;
• Quit
• Copy old data file into the newly generated data file. Now go back to Safe Repair and perform the orders to recover tables.

What is the Alternative solution?

If still, you are not able to fix the issue then you can use this alternative solution. Hence, the MySql Recovery utility is the best and a reliable solution for the error “MySQL table is marked as crashed and last repair failed”. You can opt for this solution to get a quick resolution.

Conclusion

To Sum Up, I hope the above solutions work to repair corrupt tables and solve the database issue. Specifically, you can resolve MySQL table is marked as crashed and last repair failed by using the above methods. The MySQL command-line client should be able to solve the database issue and obtain the necessary outcomes by these methods.

Related Post