My MySQL Upgrade Battle; The Short Version
Posted by Mitch Mitchell on Dec 24, 2009
Goodness, what I’ve been going through with my business blog!
It all started when I tried to upgrade it to 2.9. My business blog is my oldest blog, which I began in March 2005. It was created in MySQL 4.0. To use WP 2.9, you have to be on MySQL 5.0. This meant I had to upgrade my blog to the new database.
The first thing I did was to export a file of all my posts. I did it straight up instead of as a zip file; do both, which I eventually did, just to cover yourself. Then I went to my host, 1&1, and I exported my current database to my computer, then created the new database. All good thus far.
They I tried to import the new database, but there was a problem; my file was too big. You can only upload a maximum of 2 MB on most hosts, and my file was 3.5MB; lots of data over 4 1/2 years.
So I had to contact my host to ask them to do it. They were hesitant, and instead walked me through a process of uploading it myself through a special SSH program. That process didn’t work, though they didn’t know why and I did. I had to use the unzipped file to upload and it only allowed a maximum size of 16MB, but my file was 29MB; you’d think they would have known that.
The next step was on their end. They ended up finding a way to get my old database files into the new database, though it took them half a day to get it done. I went in, changed the WP-Config.PHP file to the new database settings, and all was a go.
Nope, not quite; nothing works that easily. All my posts suddenly didn’t work anymore, and that was problematic obviously. Nothing was coming up, but the strange thing is that I could get into my dashboard without any problems, and I could get into the editing area of each post. This was freaking me out.
My blog was down for two days, as tech support looked at it and determined they didn’t know what to do. They told me they had gone as far as they were allowed, and that maybe I should hire someone to fix my database. Hey, this is me!
The first thing I did was go online to see if anyone else had my problem. I couldn’t find that specifically, but I did come across this video called How To Fix WordPress MySQL Crashed Tables, on a blog called WonderHowTo. I thought that might be the issue, so I logged back into my host control panel and went through that process. One of my tables, WP-Options, had a lot of stuff that I had to run the “fix tables” process on, but it worked almost immediately. I went back to the blog; nothing.
I decided to see if upgrading to 2.9 would work. I tried the automatic upgrade, which has never worked before, and viola, it worked! Well, the upgrade worked; now I have at least one blog where that works. I still didn’t have any of my posts, though, and that stunk.
Still thinking, I decided to try deactivating all my plugins to see what happened. Nothing. However, something stuck out in my mind that I had seen while going through my searches. There was a lot of conversation about permalinks and checking the structure of those things. I went there and didn’t see anything out of place, but then remembered that, on that blog, I had a unique plugin called Dean’s Permalink Migration. I had added it to that blog to remove all the dates from my posts, which get in the way of SEO efforts.
I reactivated that plugin and went to check its settings. Then I hit update options, and all my posts were back; whew!
Then I went back to the plugins page and added the most important ones first and tested; all worked perfectly with the dashboard. Then I added the other plugins one at a time; all worked perfectly once more. Then I thought I’d try something and I added the WP Database Backup plugin, which used to shut all my blogs down; works perfectly.
I decided to go one step further. I went and found All In One SEO to see what would happen. And, of course, it loaded, my dashboard works perfectly, and all is right with the world.
At least on that blog. It seems that by upgrading the database that blog works the way it’s supposed to. As for my other two blogs, I’m still confused because they’re already on 5.0, so I shouldn’t be having any problems with anything. For now, I’m not going to worry about it.
What are the important lessons here? First, always make sure you back everything up, just in case. Export whatever you can to your computer for your protection. Second, if you have to, contact customer service and let them handle some stuff, even if they seem somewhat irritating. I’ll be writing a post about that on my business blog. Third, don’t panic if things don’t go correctly. Fourth, verify to see if you have your blog database first. If you can actually access your information in some way, you’re still running good. Five, test and question everything you have, and you’ll probably figure it out.
And if none of that works, go through forums, or, of all things, mention it on Twitter, and you might get a response or an offer of help. I’ve licked this one; whew! Since I probably won’t be posting on Christmas Day, I want to wish everyone a happy holiday!