Restoring the WordPress Database – my journey #WordPress

Restoring the WordPress Database - my journey #WordPress

This is a technical post. So a big sorry to those of you who like my reviews, travel posts or art posts. This post is born of the pain of a crashed site. There were actually multiple issues, but I will address each in it’s own post so that anyone searching for individual answers can find them without having to scroll through heaps of unrelated stuff.  If you do read this, then just skip through my error message dumps: they are only there to catch Google searches in case this is of help to someone.

So yes, it all began when I tried to log into WordPress. It would not let me. A bunch of other crap happened and this message happened (you can read other subsequent posts if you are interested in the “other crap”):

wp_options: Table is marked as crashed 

I was given the option to let WordPress automatically repair it and ended up with more error messages:

The wp_users table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_users table. Error: 1 client is using or hasn’t closed the table properly

The wp_usermeta table is not okay. It is reporting the following error: File ‘./blog/wp_usermeta.MYD’ not found (Errcode: 2). WordPress will attempt to repair this table…
Failed to repair the wp_usermeta table. Error: File ‘./blog/wp_usermeta.MYD’ not found (Errcode: 2)

The wp_posts table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_posts table. Error: 1 client is using or hasn’t closed the table properly

The wp_comments table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_comments table. Error: 1 client is using or hasn’t closed the table properly

The wp_links table is okay.

The wp_options table is not okay. It is reporting the following error: Table is marked as crashed and last repair failed. WordPress will attempt to repair this table…
Failed to repair the wp_options table. Error: Table is marked as crashed and last repair failed

The wp_postmeta table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_postmeta table. Error: 1 client is using or hasn’t closed the table properly

The wp_terms table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_terms table. Error: 1 client is using or hasn’t closed the table properly

The wp_term_taxonomy table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_term_taxonomy table. Error: 1 client is using or hasn’t closed the table properly

The wp_term_relationships table is not okay. It is reporting the following error: 1 client is using or hasn’t closed the table properly. WordPress will attempt to repair this table…
Failed to repair the wp_term_relationships table. Error: 1 client is using or hasn’t closed the table properly

The wp_termmeta table is okay.

The wp_commentmeta table is okay.

Some database problems could not be repaired. Please copy-and-paste the following list of errors to the WordPress support forums to get additional assistance.

wp_users: 28 when updating keyfile
wp_usermeta: 28 when updating keyfile
wp_posts: 28 when updating keyfile
wp_comments: 28 when updating keyfile
wp_links: 28 when updating keyfile
wp_options: Table is marked as crashed
wp_postmeta: 28 when updating keyfile
wp_terms: 28 when updating keyfile
wp_term_taxonomy: 28 when updating keyfile
wp_term_relationships: 28 when updating keyfile
wp_termmeta: 28 when updating keyfile
wp_commentmeta: 28 when updating keyfile

Blah! (Actually my expletives were a lot worse than this word that looks vaguely like something Count Dracula would say).

A quick Google search later and the general response was “Sorry Dude, the only way to fix this is to restore a backup.”

Sadly it turned out that this is what I finally resorted to. Finally, but not before doing my own experiments. So before you read further, don’t bother unless you have a backup of your WordPress site. Backing up WordPress is easy (unless you have never done so and find yourself here reading this). It is a tool on your WordPress dashboard (under tools). You can either manually backup to your computer (takes about 5 minutes for me) or have a scheduled backup that emails the file to you (my file is about a megabyte in size) on a regular basis of your choosing.

So I needed to look at the database. The site is hosted by Aabaco, Yahoo’s small business subsidiary. It has a familiar interface:

Now the thing I wanted was the database manager thingy (thingy being a technical term as we all know). So I scrolled down a bit and saw what I wanted.

I clicked on that and found myself on a page with this (by the way, note how I am not showing you anything with software versions on it – keep these secret):

What I had to do was click the install so that I could install something called PHPMyAdmin. This is a database management tool. It sits on your site, preferably in a folder of its own. I did not take any screenshots of the installation as it is nothing special. Next thing was to create a user who can administer the tool So I clicked on the “Database Administrator” link, made up a fake name and a clever password and voila!

So then I logged into PHPMyAdmin and took a look at the tables displaying problems. Here is a screen shot of me looking at wp_usermeta:

In case you cannot tell, I clicked the SQL tab and wrote a very simple little query: select * from “wp_usermeta”. This is programmy speak for “show me everything in that table”. Personally I found the response about the table being marked as crashed to be very inconsiderate of the system. As it turns out, nothing that I did got these tables to awaken from their slumber. I even tried renaming them and individually restoring them, but to no effect.

Huh? Why rename them?

A good little rule of thumb with databases is “Don’t delete tables until you have replaced them successfully. Just rename them and restore the backup. If it works, then delete the old renamed tables.” Why? Because sometimes backups are corrupt too. maybe even worse. I’ve seen it happen. best to be completely paranoid when dealing with databases.

Anyway, there was no help for it. I had to do a complete restore of everything. So I renamed everything (bah humbug!) and followed the WordPress restore regime. This is really, really easy.

You see, at the top of the PHPMyAdmin page is an Import tab:

So I clicked on that and I saw:

So I chose my most recent WordPress backup file and made sure that the format at the bottom was selected to SQL. I then clicked the “GO” button (which I neglected to show in the screenshot but which is right underneath).

For about 5 minutes my site would not work (well, due to other crap earlier in the day it had not worked for 6 hours at one point). Then there it all was. Well, almost all. After my last post I had been lazy and had not bothered to make a WordPress backup. Stupid! But I had made a whole of site backup and from that was able to reconstruct my last post. Yippee!

I guess that the important take away from this is that we must all be consistent in backup up our WordPress sites. You never know when something can go wrong.

Thanks for reading.

regards

Greg

 

 

 

11 Comments


    1. Yep. I reckon that is a plan. An important plan.


  1. Sorry this happened, but thanks for documenting the fix! That could be a real lifesaver.


    1. As long as any of my online regulars backup their sites I would also be happy to help them restore them if it ever came to it. Even with all my experience (I have run this site since 1996 and I am a computer professional by occupation) I still had a few hiccoughs on the way.


    1. Run like the wind!


  2. I saw your site was having data base errors yesterday. It also seems the like and comment problems i was suffering have been cured too.


    1. Yep. Funnily enough I am now having problems with comments. I am not notified about them when viewers post them to my stories. :(


  3. Thanks for the heads up Greg. Disaster can strike at any time and there’s no such thing as a quick fix if my experience is anything to go by. It’s fortunate indeed that you know your way around your system. I’m still in never never land but at least I back up to Apples Time Machine every hour or so. Good luck with everything and keep the hammer and the axe locked in the shed.


    1. Thanks old mate :) You’re correct – no such thing as a quick fix. Amazing how many people don’t seem to appreciate that. Back in the day I was a bit of a database whiz, so I am lucky that so much of the WordPress back-end relies upon a database. It was still a bit of a pain though as the initial error was at the server end outside of my control. SO I had to get my host’s support team to fix the server before I could weave my magic. At least it is all working now :)

Comments are closed.