Troubleshooting MySQL Error 145 (HY000)

You may encounter the “error 145: Table ‘/dbname/tablename’ is marked as crashed and should be repaired,” while accessing the records in the MySQL database. MySQL Server usually throws this error when it fails to read the tables in the database. It indicates corrupted or damaged data in the database. Due to this error, you may fail to access the tables and other data in the database, leading to downtime and service disruption. Below, we will discuss how to resolve the MySQL error 145 (HY000) on both Windows and Linux operating systems. But before that, let’s take a look at the causes behind this MySQL error.

What Causes MySQL Error 145 (HY000)?

The MySQL error 145 indicates corruption in database tables. The tables can get corrupted due to one of the following reasons:

• Sudden power failure.
• Interruption in the data transfer process while migrating from one server to another.
• The application is abruptly terminated or killed in the middle of a write operation. 
• Internet failure on the system hosting the MySQL Server.
• Lack of disk space.

Methods to Fix MySQL Error 145 (HY000)

You can encounter the MySQL error 145(HY000): Table is marked as crashed and should be repaired, while connecting to the MySQL Server. It can occur due to network issues. If there are network connectivity issues, you can troubleshoot the connection problems. If this is not the case, then follow the below methods.

Method 1 – Restore MySQL Database Tables from Backup

The error message itself indicates issues with the MySQL table. If you have created a logical backup of the database using the mysqldump utility, then you can restore the tables from the backup database file.

To identify the crashed table, you can use the below T-SQL query:

SHOW TABLE STATUS;

Search for the table with the “Crashed” status, under the Data_free column.

Once you identify the affected table, you can restore it.

First, create an empty database by using the following command:

‘mysql > create db_name’

Then, use the below command to restore the table from the backup database.

mysql -u root -p db_name < dump.sql --tables table name

If multiple tables are corrupted, then you can restore the entire MySQL database from the backup. Here is the command:

mysql -u root -p db_name < dump.sql

After restore, it is important to check the restored objects in the database. For this, use this command:

‘mysql> use db_name;

mysql > show tables;’

If you’re running MySQL on Linux system, then execute the below command to restore the database from backup:

mysql -u [user] -p [database_name] < [filename].sql

Method 2 – Check and Repair the MySQL Database Table using mysqlcheck Command

If you don’t have an updated backup or your backup file is obsolete, you can use the mysqlcheck command. This command is used to check the tables and repair them if they’re corrupted. It is also used to analyze and optimize the tables. Here’s how to use this command:

• Open the command-line terminal on your system which is hosting MySQL Server.
• Run the below command to check a specific table in the database:  

mysqlcheck -c database_name table_name

• If it shows error for the table, then repair it by using the below command:

  mysqlcheck --r database_name table_name

Method 3 – Repair MySQL Tables using myisamchk Command

MySQL supports two types of storage engines – MyISAM and InnoDB. If you’re using MyISAM storage engine, then you can use myisamchk command to recover the corrupted table. Here’s how to use this command on the Windows system.

First, you need to stop the MySQL Server. For this, use the below command:

mysqld stop

• Then, use the myisamchk command, as given below, to fix the corrupt table:

myisamchk –recover TABLE

• Now, again start the MySQL Server by using this command:

mysqld start

If you are running MySQL on Linux system, then follow these steps to repair the tables:

• Stop the MySQL server using the below commands:
• For CentOS and Fedora

service mysqld stop

• For Debian and Ubuntu

service mysql stop

• Next, change the directory with the directory where the affected database table is saved.

cd /var/lib/mysql

• Use the below command to check the corrupted table in the database.

myisamchk table_name

• If you want to check all the tables in the database, use this command:

myisamchk *.MYI

• After identifying the damaged table, run the below command to repair it:

myisamchk –recover table

• After this, restart the MySQL Server using the below command:
• service mysqld start For CentOS and Fedora
• service mysql start For Debian and Ubuntu

Note:

If you’re using InnoDB storage engine, you can enable the Innodb_force_recovery option to repair the corrupt tables.  

What If Nothing Works?

If the above repair methods fails to repair the MySQL database tables, then you can use  an advanced MySQL database repair tool, like Stellar Repair for MySQL. It can repair corrupt MySQL database, including tables and other objects with complete integrity. It can also help recover deleted records from the corrupted database. The tool allows you to save the repaired data in MySQL, MariaDB, SQL Script, HTML, CSV, and XLS formats. It can help you fix corruption issues in MyISAM and InnoDB tables. The tool supports both Linux and Windows operating systems.

Closure

The error 145 (HY000) in MySQL usually occurs due to corrupt database tables. You can follow the methods discussed above to repair the tables. One of the easiest methods to repair database tables without data loss is to use a professional MySQL repair tool, like Stellar Repair for MySQL. It can fix corrupt MyISAM and InnoDB tables, and restore them to their original state. The tool can help fix complex corruption-related errors in MySQL database, including the error 145. It supports MySQL Server 8.x, 6.x, 5.x, 4.x, and 3.