You may encounter the “MySQL Table crashed and repair failed” error when accessing the database after the computer crash or when you forcefully shut down the MySQL application. It can occur in both InnoDB and MyISAM tables. However, it is often seen while accessing the tables created using MyISAM storage engine as it stores indexes separately, which increases the risk of corruption in comparison to InnoDB. The error can appear with different error codes (see the below examples).
ERROR 144 – Table ’employee3′ is marked as crashed and last (automatic?) repair failed
1194 – Table ’employee3′ is marked as crashed and should be repaired
As you can see, the above error message indicates that the database table is crashed and MySQL Server fails to read it. However, MySQL Server has an inbuilt feature to recover the crashed tables by itself, but sometimes an automatic attempt can also fail. In this post, we will discuss the reasons behind the “MySQL Table crashed and repair failed” error and the solutions to fix it.
Causes behind ‘MySQL Table Crashed and Repair Failed’ Error
This error can occur when your MySQL table crashes due to any of the below reasons:
- Heavy load on the server.
- Improperly closing the MySQL tables.
- Low disk space on the system containing MySQL tables.
- MySQL database is damaged or corrupted.
- Corruption in MySQL table.
- Power failure while working on the MySQL table.
- Improper shutdown of MySQL Server.
How to Resolve ‘MySQL Table Crashed and Repair Failed’ Error?
To resolve this error, follow the fixes mentioned below.
Fix 1: Check Disk Space
The “MySQL Table crashed and repair failed” error can occur if your system is running low on storage. You can check and ensure that enough storage space is available on the drive where MySQL tables are located. To perform this, follow steps cited below:
- Right-click on the drive where the tables are stored and select Properties.
- In the Properties window, check the Free space.
- If the disk space is low, then free up some space. You can delete old or unwanted files from the disk.
- You can also delete temporary files using the Disk Cleanup. Open Disk cleanup, select the drive from where you need to clean up files, and click
Fix 2: Restore Tables from Backup
The MySQL tables can get crashed due to corruption in database. You can restore the crashed MySQL tables from the backup. For this, you can use the mysqldump utility. To use this utility, first make sure you have all the permissions, like SELECT, VIEW, SHOW, TRIGGERS, and CREATE on the dump files. Follow the step-wise instructions given below :
- First, use the Create statement to create an empty database where you need to save the restored database.
mysql > create db_name
- Once you have created the database, then run the mysqldump utility to restore the database (see the below command).
mysql -u root -p db_name < dump.sql
- This command will restore all the data in database. You can check the recovered data by using the USE and SHOW commands as given below:
mysql> use db_name;
mysql > show tables;
Fix 3: Restore MySQL Tables using myisamchk Command
The tables in MySQL are stored in .MYI files. If your backup file is not readable or available, you can repair the MySQL tables using the myisamchk utility. To run this utility, follow the below steps:
- First, use the following command to stop the MySQL Server:
service mysql stop
- Next, execute the below command to recover the table:
myisamchk –recover TABLE_name.MYI
- After this, use the below command to start the MySQL Server:
service mysql start
Note: This command can restore MyISAM tables but it doesn’t support repairing partitioned tables.
Fix 4: Use Binary Log File
If binary logging is enabled in MySQL, then you can perform point-in-time recovery to restore MySQL database. To check this, you can run this command:
mysql> SHOW VARIABLES LIKE ‘log_bin’;
If the command shows it is ON, then you can use binary log file or event positions in binary log file to restore database. However, point-in-time recovery works only if you have readable backup file and binary log file. If backup is not available, then you can take the help of any professional MySQL repair tool to repair the tables.
Fix 5: Use a Professional MySQL Repair Tool
To quickly repair MySQL tables without any data loss, you can use a professional MySQL repair tool, like Stellar Repair for MySQL. This DIY tool can recover all the data, including foreign keys and partitioned tables, from corrupt database with complete integrity. It is a safe, convenient, and effective tool for repairing both InnoDB and MyISAM tables. You can use the tool’s demo version to preview the recoverable data and ascertain the tool’s capability. The tool supports restoring data from MySQL database created on Windows and Linux operating systems.
Conclusion
The ‘Table is marked as crashed’ error can occur when the table is crashed due to corruption, low disk space, or any other reason. If the error has occurred due to corruption in tables, you can use MySQL repair tool – Stellar Repair for MySQL, to repair the corrupt tables. It supports repairing tables of both the InnoDB and MyISAM storage engines. It can recover all the data, including queries and tables, from the corrupt database. It allows the recovered data to be saved in a new database file and other file formats with complete integrity.