Just Creative

I’m Jacob Cass, the founder of JUST™ Creative. I’m a multi-disciplinary graphic designer, working with clients all around the world. My specialty is logo & brand identity design. JUST™ Get in touch.

Love


How to manually restore a corrupt table in PHPAdmin

Posted on

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_comments and wp_commentsmeta tables. 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 table_name.

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_comments and all the INSERT INTO wp_comments lines after that along with the CREATE TABLE wp_commentsmeta and all the INSERT INTO wp_commentsmeta after that. I would put those two sections into a new file called restore.sql and add DROP TABLE IF EXISTS wp_commentsmeta  before the creation of that table and DROP TABLE IF EXISTS wp_commentsmeta before 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!



SUBSCRIBE: RSS, Twitter, Facebook



8 JUST™ Creative Comments

  • GingerWench Reply

    Thanks for including the screen shots, they do help a lot :) I’ve bookmarked this article, just in case I ever need it, thanks for sharing it with us!

  • Kierin.M Reply

    Hey Jacob,

    Thanks for this article, i was having a few problems with my page not showing up a while ago. Luckily i solved the issue, but i know where to look if i ever encounter the same problem you just encountered.

    cheers
    btw was that why the blog was offline a few days ago?

  • Jacob Cass Reply

    You’re welcome!

    Kierin,
    I’ve had an issue with the site going down the past few months, usually on Tuesdays & Wednesdays. Doing my best to get it back to normal.

  • Kierin.M Reply

    Jacob,

    Oh okay, i hope it will work again smoothly.
    Good luck with with that, it must be hard to do everything together: Writing articles, Doing the technical stuff plus the job.
    Keep up the good work

    Cheers
    Kierin M

  • Jacob Cass Reply

    Klerin,
    It’s all a learning curve, but yeah it does get a bit tricky at times. Luckily peeps on the net are such a caring & helpful bunch!

  • Orlando Reply

    Thanks for sharing the codes to restore a corrupt table in phpadmin. Your stuff provides the solution for many developers. Great.

  • Eye Bridge Reply

    Thanks for this post.keep posting

  • Arianna Reply

    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.

  • NO TRACKBACKS


Submit A Creative Comment

Your email address will not be published. Required fields are marked *
Please use your real name and do not use keywords. All comments are moderated by myself and I reserve the right to edit or not publish your comment. Thank you!

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">



© Copyright JUST™ Creative 2007-2013. Designed by Jacob Cass. Powered by Wordpress using the Skeleton Framework. Fonts served by Typekit. Site hosted by (mt).

x