Surviving a corrupt WordPress database
Yesterday morning, the first thing I did after opening my browser was try logging into my WordPress dashboard. But neither the blog would load nor the login page. During such an event, I check my server status at status.asmallorange.com (name’s Sam). But seeing all three oranges lit, I immediately suspected that something was wrong with WorPress.
After allowing the page load for a few minutes, I was redirected to a page telling me that a database upgrade was required. But it concluded with fatal errors.
Next I logged into the Control Panel provided by my hosting provider and poked my nose there. I found a button that said Repair DB and since that was what I wanted, I pushed it.
The repair completed successfully and I was finally able to log into the WordPress Dashboard. The home page also loaded fine but it was later in the day when I discovered that the post pages were not rendering. The so called “Repair” made things worse !
Backing-up and reinstalling WordPress
Deciding that I needed to clean-install WordPress, I exported all my posts and comments using the Export panel from within WordPress. I periodically do a back up and store the generated XML file on Windows Live SkyDrive.
Many argue that simply replacing the database with a back-up copy would re-create the magic but frankly, I have no idea how that works. The XML file has always worked for me so I don’t bother with anything new. But if you are really interested, check out Kikolani where Kristi points us to a plugin that e-mails a copy of the database everyday. Note especially some of the comments and suggestions left by the readers.
Clean installing WordPress requires removing and/or creating a new database and deleting all WordPress related files and folders. One very important folder to remeber is the wp-content folder, which contains the themes, plugins and uploads. Deleting or replacing the wp-content folder will result in loosing all your themes, plugins and uploaded content – and starting from square-one !
Earlier in February, I covered how to clean upgrade your WordPress installation. Wordpress 2.7 which is just around the corner however, is expected to feature automatic upgrading of a WordPress installation, and that could make my article obsolete.
Re-importing data
After logging into a fresh WordPress installation and changing the password, the next obvious step was to retrieve things from the Import panel.
It didn’t work.
Why ? Because the Import panel will only accept an export file no larger that 2.0 MB. Check it out for yourself ! See who else is complaining.
Now this is not WordPress’ fault. The 2.0 MB limit is set by the default PHP installation performed by your hosting provider. This value is set in a file called php.ini and it is not always accessible.
My export file was 2.1 MB. That’s right, two point and a (bleep) one.
Now the following numbers will be of interest to my regulars. I only have less than a hundred posts (yeah I’ve been lazy) and a few hundred comments (not my fault). If that alone takes up more than 2 MB, I don’t even have a guess for those blogs with hundreds of posts and thousands of comments !
I even tried to trim my export file manually by removing posts that were next to garbage. But do you know what that involves ? Take a look at my export file and you tell me !
Fixing the upload limit
The most obvious way to get around the file size limit is to edit the php.ini file I mentioned earlier. But you may or may not find this file in your home directory (and you are not supposed to create one either). To find out where your php.ini file is located, create a file called phpinfo.php in your home directory and simply insert this line of code: <?php phpinfo(); ?>
Point your browser to phpinfo.php. You’ll see a page displaying all information regarding your PHP installation. Here is my phpinfo.php.
Scroll down or search for the term upload_max_filesize and you will see that both the Local value and Master value are set to 2 MB.
Notice that my php.ini file is located outside my home directory, so I cannot access it.
So, I had to get in touch with my hosting provider, requesting them to allow me upload my large export file and they happily agreed to help. But before I even got back to them, I found a better solution !
Googleing took me to this article which suggested getting around the file size limit by editing the .htaccess file instead. This is a hidden file that resides in your home directory and you can always create one if it does not already exist.
All you need to do is add these two lines of code into the .htaccess file:
php_value upload_max_filesize 10M
php_value post_max_size 20M
upload_max_filesize is the maximum size per file you can upload.
post_max_size is the maximum size of all he files in each posts you make.
Change the values 10M/20M to whatever you want, just make sure the post_max_size is bigger than upload_max_filesize.
Save your work and go back to check your phpinfo.php file. Notice that the Local value for upload_max_filesize has changed. The Master value remains at 2MB.
Now go to the Import panel inside your WordPress dashboard and see that it too has changed ! And it works !
Closing the can of worms…
Don’t expect smooth sailing from here yet ! I don’t know if it was my Internet connection but I got time-out errors when uploading my export file. If this happens to you, simply upload again. WordPress will check for duplicate posts and comments and deal with them accordingly.
It took me at-least three tries to import everything.
The only data I lost was my blog-roll (which I had not backed up) and my About page (strange !).
There is still some work to be done but overall, I am glad this blog made it back online !
To Kathy, Firas and Abdul Rahman: Thank you very much for your concern and support even if it was from the side-lines. It helped !
| « Blogblast for Peace – Memeing the Movement | Fuchsias on dinnerware » |
There are 33 comments
Leave a Reply
Please be considerate and on topic. Your e-mail address will not be published.
Ohh too bad to hear that happening. I think Wordpress has a bug with the import feature. I don’t think they are concerned about fixing it as most people just keep uploading until everything is imported.
Jaffer, this is my worst nightmare. Now I know who to call if this happens to me. Don’t think I’m kidding. I remember having a sweating-bullets moment when doing my WP auto-upgrade. Things locked up and I had to abort. Luckily, it worked the second time around when I skipped the step that made it hang up the first time. Oh, boy. I’m sweating again just thinking about it.
I’m thrilled for you that it all worked out in the end.
Kathy’s last blog post..How to Make a Grown Woman Cry
Somehow I imagine you spend more time working “for” your blog than “on” your blog (as in the content of the posts).
This is perhaps one of the biggest reasons that’s keeping me off custom hostings/templates and on safe terrain where the only things I worry about are my posts and the comments.
In effect I prefer to be a writer, rather than writer + Database administrator + manager + CR guy.
I let blogger do all the dirty work instead.
Noor’s last blog post..Lonely
@Curtis – Is it really a bug ? I think the import is the most important feature out there. I make sure I always use third-party websites for pictures, videos and audio.
@Kathy – Well, you’ve got my number ! And er… know where I live ! WP-auto upgrade freaked me out too when I used it the first time. But now that a future Wordpress will have a built-on auto upgrade, I think things will be looking good !
@Noor – I like doing the dirty work ! And I do a lot of fiddling and experimenting with the code – and if something exciting happenens, I’ll blog about it.
I do wish I had the dedication and inspiration to write as much as my other blogger friends do. But that’s probably because I don’t see myself as a writer – heck I don’t even see myself as a blogger most of the time. For me, blogging is now more about socializing and keeping in touch rather than pouring my thoughts out.
Well you know how I am with Linux – I do the same with Blogging platforms.
Now I don’t know if this is just because I’m Mac but all I do is back up my ‘Home Directory’ and ‘MySQL Databases’ on to my external drive. I once lost my complete simplycats blog! All I did was drag the folder (from the Home Directory back-up) up to my server and everything was as before. It took me less than 5 minutes to do, plus the upload time involved.
If you only need part of it, that’s just as simple to do. I tinker quite a bit so have often over-written folders or files that have been damaged in some way.
Babs – beetle’s last blog post..To Meme or not to Meme?
@Babs – beetle – I always work with WordPress files from Dreamweaver stored locally on the hard-drive. So I always have the most up-to-date versions with me.
Regarding my database, I don’t see my hosting service provide an Import/Export feature. I can log into phpMyAdmin and actually work within my database but there’s way too much technical goobledygook.
Based on what you just said, I am going to look into the plugin that e-mails daily Database backups right now.
Thanks Babs !
Yikes Jaffer! Sounds very complicated to me… I think I’ll stick with Blogger…
So glad you are up and running again!
Maureen’s last blog post..Back From The Big City
Im calling you if I ever need help. I could never do this on my own!
meleah rebeccah’s last blog post..Tagged By Roshan
Yeah, when you and Firas start talking I don’t know where to run!
But I guess its pretty simple if you keep it straight forward:
Blogger: A person who runs a blog to share his thoughts on.
Writer: A person who uses blogs also to share his ideas.
You: blogger+Writer+DBA+Linux freak+CR guy…and blogs about it.
@Maureen – Sorry to sacre you this way but I had to write about it !
@meleah rebeccah – The scary thing is, this can happen to anybody ! Well, you know where I live to get in touch !
@Noor – You give me such a high honour !
Sounds like a nightmare and I do hope it will not happened to my WP blog. If so, I will know where to go for help then
Btw1: Thanks for the visit and comment on my Olga in Norway post. I’m glad you can learn something about my country and you are welcome back for more.
Btw2: I use WP 2.2.1 and doesn’t use any extra plugin to replay in comment, I just edit them.
RennyBA’s last blog post..Olga the Travelling Bra at Oslo Royal Castle
There are good directions for backing up your database on the WordPress site. Look for upgrade instructions which include information on how to do the backup. It is pretty quick and not too complicated. I added a plugin that allows me to do automated installs of new versions of WordPress, and part of that process is to create a backup. I think there are also plugins specifically for backups.
Lee’s last blog post..Sunday Scenery
Okay, Im holding you to that!
meleah rebeccah’s last blog post..Bring Out Your Bears
This was by far the most terrifying Halloween post I’ve read today. Brrrrr! Are you sure Ronald didn’t have something to do with it? Anyway, I’m glad to hear you made it thru OK and are willing to help all your friends when it happens to them!
JD at I Do Things’s last blog post..Gus Does Things so I don’t have to
@RennyBA – Welcome to ManiaRavings ! The nightmare part was not being able to upload all my posts ! And thankfully there are clever people out there who are also clever to share solutions to problems people may come across !
I am enjoying your posts about Olga in Norway and about Norway too !
@Lee – I’ve now installed the WordPress Database Backup Plugin which e-mails me a backup of my database every morning.
Now I never came across the Database Backup instructions before ! Thanks a million Lee ! And it looks pretty straight forward – as long as you’ve a backup that is !
@JD at I Do Things – Oh ! And here I was thinking of writing a Halloween post ! Thank You !
I’m sure Ronald had nothing to do with this – he didn’t know computers !
This post scared the crap out of me and I don’t even use Wordpress! But it did remind me to do a total blog backup NOW! Thanks Jaffer.
Jeff’s last blog post..The Post Halloween Report
Oh Jaffer! You’ve faced every blogger terror movie. I’m glad that your blog is back so keep up the good work and thanks a lot for the informative tips.
C’est la vie? Eummm yeah, however No Pain No Gain!
Hicham’s last blog post..The Butterfly Award
@Jeff – Glad it was some help ! I think I oughtta write scary tech posts from now every Halloween
@Hicham – “A blogger terror movie” – How Interesting !
Well, there are still a few things I need to re-pair and some I need to rebuild ! Thanks for the good words !
So this where all my Blokesters hang out.
Hi there Jaffer. Been through this a couple of times myself. Regarding the comments about Blogger, the nice thing with Wordpress is at least we are able to have hands-on access to our database and files.
God knows where Google stores it, and when it all goes wrong we can’t get a hold a live person to help. It could be days or weeks before it gets fixed.
Service depends on your Wordpress host. A corrupt database is usually a problem on their server side (unless it’s a rogue plugin gone amuk) and they should be responsible for fixing it.
In most cases something like this is simply a matter of clicking the “repair” button on the CPanel. It works very well.
By the way, I notice that your far-right sidebar has been bumped below in IE and needs a-fixin’.
Glad to see everything else is Ok now Jaffer.
Cheers!
Blog Bloke’s last blog post..Blog Tips and News Clips 2008-11-13
Hey Bloke !
Well, we get around, sometimes here and sometimes there.
I also think my host-server is to blame for the file corruption. Unfortunately it was the repair button in CPanel made things worse !
I’ve followed the advises by Babs and Lee and also I’ve installed the daily back up plugin. I’m glad things are back to normal and I feel better prepared !
I know that the right-most sidebar bumps below in IE when the browser is re-sized to 1024×168 and below. I’m not inclined to fix it though unless IE users are facing other serious issues !
Thanks for dropping by ! It’s always nice to see your comment !
Is it the Filosofo plugin version?
http://www.ilfilosofo.com/blog/wp-db-backup
I’ve thought of using it myself.
Blog Bloke’s last blog post..Blog Tips and News Clips 2008-11-13
@Blog Bloke Yep ! That’s the one. I’ve set it to send backups to a separate GMail Account.
Within GMail, I set things so that those backups are immediately sent to trash – no worrying for space and I’d be deleting old backups anyway !
You’re up late tonight. Celebrating something?
Blog Bloke’s last blog post..Blog Tips and News Clips 2008-11-13
@Blog Bloke – Yes ! Thank God it’s Friday !
Great write up! BOOKMARKED for future reference! Like another poster said, this is my absolute worst fear. Quick question what can cause a database corruption in wordpress? Look forward to hearing from you.
@Justin – My friend BlogBloke can explain it better. There can be many possibilities but the most common by far is problems with the storage media on the server – it’s just like how a file can be corrupted on Windows or when burning a CD !
All I can suggest is regular backups !
I think at last the problem was solved.Thanks for providing good info on wordpress installation and recovery.Wordpress came up with many themes that you can download it for free.
balinbandara’s last blog post..Private Adoption
Thank for your sharing.
Pdesign’s last blog post..Corelle Dishware Impressions 16-Piece Dinnerware Set, Service for 4, Watercolors
Geeze, a wordpress database corruption is my worst fear and I really didn’t even know that it was possible. I’ve got a blog for my students with YEARS worth of resources that simply could not be replaced. I’m going to send this over to our school tech and have him take a look at my blog so that this won’t happen.
Thanks for dropping a line Franklin. Good luck with the tech support !
Backup, backup, backup, I think I’m going to go do that right now!
I would backup on an external hard drive to be save. In the past I used my internal hard drive for backup and it died on me and also my valuable information was gone overnight.
Couldn’t your hosting provider revert to a back-up for you? Usually they do daily back-ups.