Last week I logged into my WordPress Admin and to my surprise, found that all of my site’s comments (12,000+) were missing. After some searching around, I narrowed down the problem to a corrupt wp-comments file. How the file became corrupt I do not know, but thankfully the comments are restored now.
I thought it would be worth sharing how I went about fixing it, just in case it happens to some one else. The solution not only works for the wp-comments file, but all WordPress / PHPAdmin SQL files so basically this is a tutorial on how to manually restore a corrupt table / file in PHPAdmin, rather than just the specific comments file used in the example below.
After I noticed my comments were gone I sent out a tweet to figure out how to fix a corrupt comments file. I got a few suggestions (thanks!) such as turning off all plug-ins and repairing the database table, but none of these solutions worked. Another suggestion was to upload my full sql backup to restore the comments. I tried this but PHPAdmin blocked the import due to duplicate database entries. I was then pointed to this article, which I later found, was the right solution.
Although the article had the solution, I didn’t really have the database skills to do what was needed, however, thankfully Josh Betz kindly sent me an email & video explaining how to do it. I also had help from another friend who tested the files on a local DB first.
Here are Josh’s accompanying instructions for the video. Take note that this tutorial is for the comments & commentsmeta tables but you can use this method for any table in PHPAdmin.
Make sure you checkout these screenshots (1, 2) before you actually do anything. It will clear up the instructions below.
To restore all your comments, you probably want to replace the
wp_commentsmetatables. I’ve got two screen shots here of both of those sections. In the video I explain that there is code to create each of the tables and then there is code to populate the tables. The creation of the tables is obviously where it says CREATE TABLE
table_name. They are then populated with the sections of INSERT INTO
Now, the plugin that I use adds a part that will drop the table that it’s trying to restore before it creates the table because SQL isn’t going to let you create a table that already exists, which is why you were getting that error.
What I would do if I were you would be to copy the CREATE TABLE
wp_commentsand all the INSERT INTO
wp_commentslines after that along with the CREATE TABLE
wp_commentsmetaand all the INSERT INTO
wp_commentsmetaafter that. I would put those two sections into a new file called restore.sql and add DROP TABLE IF EXISTS
wp_commentsmetabefore the creation of that table and DROP TABLE IF EXISTS
wp_commentsmetabefore the creation of that table.
Then you can go into phpMyAdmin and click on the database you want, then import your sql file.
Another learning experience & just another reason to back up often!