The other day I noticed that my WordPress site (WPSaviour) started responding slow and at times it was giving error connecting database issue. At many instance it was giving me following error “Connection lost. Saving has been disabled until you’re reconnected.
It was unusual, as my traffic was consistent and there was n change in server configuration. For the first few time I simply ignored this issue and today I ran into another issue with comment moderation. Even after accepting or trashing the comments, it was re-appearing.
With that I was sure something is wrong with the database and I thought of repairing my database. Instead of using WP DB-manager plugin or WP-optimize plugin, I straight away used PHPMyAdmin to repair and optimise my database.
- Read: How To Optimize WordPress Database Size To Improve Performance
For the first time I encountered something very unusual as I noticed few of my WordPress database tables couldn’t be repaired. Here is what error message looked like:
wp_se_blc_instances: The storage engine for the table doesn’t support repair
wp_se_blc_links: The storage engine for the table doesn’t support repair
Notice the “The storage engine for the table doesn’t support repair” and when I checked my WordPress database overhead, I was stunned as overhead size was about 5320GiB.
I straight away Pinged my hosting support team but they couldn’t any help as this was an issue with database and not the hosting. So I started searching for a solution to this issue and finally I figured out what could be done to fix error “The storage engine for the table doesn’t support repair“.
Convert InnoDB MySQL Engine to MyISAM – SQL Query
I researched about the issue and understood that InnoDB and MyISAM are two popular MySQL Engine. MyISAM is the one which supports mysqlcheck’s repair feature. InnoDB engine is recommended for busy database but in this case I was facing issues with excessive database overhead, so I decided to move my tables back to MyISAM engine.
If you are also facing the similar issue, the solution is to convert the InnoDB to MyISAM engine. Here I’m sharing the SQL query which you need to run to convert the tables one by one.
Note: Do take a complete backup of your database before running this SQL query. If something goes wrong, you should be in a condition to restore your database.
ALTER TABLE tablename ENGINE=MyISAM;
Replace table name with your database table name which you want to be converted into MyISAM. After repeating that for all the tables, do repair and optimise your database.
- Read: How to delete custom field value from WordPress database
For me, this fixed my problem of WordPress database. If you ever run into similar kind of situation, this would help you. Don’t forget to take your database backup before running any SQL query.