How to Fix MySQL Error Code 144?

The MySQL error code 144 indicates that a table is crashed and last repair has failed. This error can arise for various reasons. Typically, it may occur when the MySQL storage engine stops writing into the physical files (MYD and MYI). For example, it may happen that the server hosting the MySQL is suddenly shut down. It may also happen due to a lack of storage space. But not only that, even a bug could crash MySQL, corrupting the data. In such a situation, the data becomes accessible. Therefore, you need to recover the data as soon as possible. Below, we’ll be discussing the solutions to fix the MySQL error 144 and recover the data.

Solutions to Fix the Error 144 in MySQL and Recover the Data

Here are some solutions you can try to fix the MySQL error code 144 and recover the data.

Method 1: Use the MyISAMchk Command

The error 144: Table is crashed and the last repair failed can be fixed using the MyISAMchk command (as given below) in MySQL. The MyISAMChk command analyzes and retrieves information about MyISAM tables (MYD and MYI).

MyIsamchk warehouse.MYI
MyIsamchk *.MYI

The above command will provide a list of corrupted tables, with the relative errors.

You need to mark the table as checked if MySQL server is not running due to a crash.

This could be done by adding the parameter -update-state.

If you perform the check on more than one table, then it will show the relative errors for the corrupted ones.

Now, each corrupted MyISAM table must be fixed. For this, execute the same command specifying the parameters -r –q.

The –r parameter means “recovery mode” and the –q means “quick recovery mode”.

MyIsamchk –r –q warehouse.MYI

This will attempt to fix the index file without modifying the data.

If the data file contains everything and the “delete links” point to the correct locations within the data file, this should work and the table will get fixed.

Otherwise, execute the MyIsamchk command specifying only the –r parameter.

MyIsamchk –r warehouse.MYI

This time the incorrect lines and the deleted lines will be removed from the data file. The procedure will rebuild the index file. 

If this step also fails, then run the command with the “–safe-recover” parameter.

MyIsamchk –safe-recover warehouse.MYI

This is a slower option and only handles some particular cases of data corruption.

Method 2: Use the TRUNCATE TABLE Command

If the above method fails, then you can use the TRUNCATE TABLE command. You can use this method if the index file is missing or the first 16 KB block in the index file contains invalid information.

Caution: Before proceeding, make a copy of the data file (.myd).

You need to create a new empty index file (.myi). To create a new .myi file, enter the following command in the MySQL shell:

$> mysql db_name

Now, execute the following commands:

mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit

Copy the saved data file to the created data file.

Now, again execute the MyIsamchk –r –q command.

This time the recovery procedure should work.

Method 3: Use the REPAIR TABLE Command

Another option is to use the REPAIR TABLE tbl_name command, specifying the USE_FRM parameter. If the .MYI index file is missing or the header of the file is corrupted, this option tells the MySQL database engine not to trust the information in the .MYI file header and to recreate it reading data from the data dictionary.

However, it is be noted that using the USE_FRM option could lead to negative consequences.

The current AUTO_INCREMENT value and the link to the deleted records are lost. The latter point has led to the fact that deleted space records subsequently remain unoccupied.

Since the information contained in the index file, such as the fact that the table is compressed or not, is not considered using this option, you may experience data loss by using the USE_FRM option.

Method 4: Use a MySQL Repair Tool

You can use a powerful MySQL repair tool to repair a corrupted MySQL database. It can fix the error “error 144 and many other errors related to database corruption.

Stellar Repair for MySQL can repair and fix corrupt MySQL databases and restore all the database objects, such as tables, primary keys, views, triggers, etc., without changing the original database structure. This tool saves the repaired files in not only MySQL or MariaDB format, but also in various other formats, such as SQL Script, CSV, HTML, and XLS.

The software supports both MySQL tables – InnoDB and MyISAM. It also supports the repairing of MySQL and MariaDB databases created on Windows and Linux.

To Conclude

The MySQL error code 144 means that the index file (.MYI) contains errors. Above, we have mentioned different ways to fix this error and recover the data. However, if the MySQL database is corrupted, then the best option is to use a powerful MySQL recovery software, such as Stellar Repair for MySQL. This software can easily repair the corrupted database and recover all the data with complete integrity.