Summary: Are you getting ‘the storage engine for the table doesn’t support repair’ MySQL error? Read this article to understand the reasons behind such an error. Also, find solutions to fix the issue manually or by using a professional MySQL repair tool. The tool can help save your time in repairing and restoring the database table without executing any query. Download the free demo version of the MySQL repair tool to preview the repairable InnoDB table before saving it.
Table of Contents:
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';|
You can try to recover data from a corrupted InnoDB table manually or repair the table by using a professional MySQL repair tool.
Restore database backup to recover corrupted table. If you don’t have backup or it is not updated, try the next solution.
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.
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:
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.
|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:
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.
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.
Check out this video to know how the MySQL database repair software works:
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:
In certain situations, like database corruption and data loss, moving...
Table of ContentsAn Overview of mysqldump CommandBacking Up and Restor...
Situations may arise when you need to restore your MySQL database, inc...
Table of Contents: Solutions to Fix MySQL Error ‘the storage...
When MySQL database and tables become damaged or corrupt, the first th...