When you try repairing a MySQL database table, you may encounter "the storage engine for the table doesn't support repair" error. This error usually occurs when you attempt to repair an InnoDB table. This happens because the InnoDB storage engine doesn't support repair operation.
You can check which storage engine your table is using by running this command:
| SHOW TABLE STATUS LIKE 'tblname'; |
What You Can Do to Fix MySQL Error 'the storage engine for the table doesn't support repair'?
You can try to recover data from a corrupted InnoDB table manually or repair the table by using a professional MySQL repair tool.
Solutions to Fix the InnoDB Storage Engine MySQL Error Manually
Solution 1 – Restore Database Table from Backup
Restore database backup to recover corrupted table. If you don't have backup or it is not updated, try the next solution.
Solution 2 – Switch the Table Engine from InnoDB to MyISAM
You can change the database engine of a table from InnoDB to MyISAM to repair the table. This can be done by using the following ALTER command:
Note: Take a backup of your database before running this query.
| ALTER TABLE tbl_name ENGINE=MyISAM; |
This command will help you convert a specific InnoDB table to MyISAM table. Replace the tbl_name with the name of your table.
To convert several tables from InnoDB to MyISAM, run the following SQL statement from command line or phpMyAdmin:
| SET Database_name = 'yourdb'; SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=MyISAM;') As sql_statements FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourdb' AND ENGINE='InnoDB' AND TABLE_TYPE='BASE TABLE'; |
In this command, replace 'yourdb' with the name of your database and 'TABLE_NAME' with the name of your InnoDB table that you want to convert into MyISAM.
After running the above script, try repairing the tables that you have converted to MyISAM engine. Once repaired, convert the table(s) back to InnoDB. If this method doesn't work, try the next one.
Solution 3 – Restore Table Data from Dump File
Another way of retrieving table data is to run the InnoDB recovery process by using the 'innodb_force_recovery' option.
Running innodb_force_recovery prevents background operations from running, allowing you to dump your table. You can dump your table into a new file, delete the old file, and restore the data from the new dump file.
The detailed steps are as follows:
- Open MySQL configuration file (my.cnf). In the [mysqld] section, add the following:
| [mysqld] innodb_force_recovery=1 |
In this command, you may need to set the value of innodb_force_recovery to 3 to dump your tables. The value can increase from 4 to 6 to dump the tables, but this involves data loss risk.
- Save and close the my.cnf file.
- Restart the MySQL service.
- Try accessing the corrupt InnoDB table, dump it into a SQL file by running 'mysqldump' command as follows:
| mysqldump -u user -p dbname tblname > tblname_dump.sql |
This command will dump a single table. You can also dump multiple tables by adding whitespace between the tables in the above command.
Drop the corrupt table from the database:
| mysql -u user -p –execute="DROP TABLE dbname.tblname" |
Stop MySQL service. Once it is stopped, disable InnoDB recovery mode. To do so, add the following line in the [mysqld] section:
| #innodb_force_recovery=… |
Save all the changes you have made to the MySQL configuration file and restart the MySQL service:
| service mysqld start |
Now restore the table from the newly created dump file by executing the below query:
| mysql -u user -p > tblname_dump.sql |
If you cannot restore the table data, using a SQL repair tool may help.
Use a Professional Tool to Repair InnoDB Table
If troubleshooting the above methods fail to restore your InnoDB table, using a MySQL repair tool can help you repair the corrupted InnoDB table and recover all its data without any modification. Stellar Repair for MySQL is one such tool that helps repair InnoDB tables and restores table properties, keys, relationship constraints, etc. The software reduces downtime associated with manual solutions to repair MySQL database and all its objects like tables, stored procedures, views, etc.
Key Benefits of Using Stellar Repair for MySQL Software
- Repair tables of both InnoDB and MyISAM database engines
- Restore tables of MySQL database created on Windows and Linux systems
- Can repair several databases in a single batch process
- Save repaired file in multiple file formats like MySQL, HTML, CSV, or XLS
- Fix MySQL corruption errors like 'Corruption of InnoDB tablespace, 'Could not find file wtlicensemanager.dll', 'Index file is crashed', etc.
Check out this video to know how the MySQL database repair software works:
FAQs
Q. How can I convert all InnoDB tables for multiple databases to MyISAM tables?
A. Run the following command to convert all InnoDB tables for multiple databases to MyISAM tables:
CONCAT('ALTER TABLE ', table_name, ' ENGINE=MyISAM;') to CONCAT('ALTER TABLE ',@DATABASE_NAME,'.', table_name, ' ENGINE=MyISAM;') |
-in-MySQL-to-Restore-Corrupted-Databases-1746.jpg)
5 min read
