WordPress 2.9 was released a couple of days ago. I read about all the cool new features and said to myself – “hey, I need that version!” so I used my favorite WordPress Automatic Upgrade plug-in to upgrade my primary blog to WP 2.9.
I got almost all the way to the end of the upgrade process, where it told me a “CLICK HERE TO COMPLETE THE FINAL STEP.” When I clicked OK I was rewarded with this nice little message: “ERROR: WordPress 2.9 requires MySQL 4.1.2 or higher”
Apparently, my blog was running a version earlier than that. So now all my files are upgraded to WP 2.9, but my database is not compatible. Now what!?
Damage Control
My blog is officially offline.
I use BlueHost and 1and1 for my WordPress hosting. I primarily use BlueHost now because they’ve got the whole WordPress install and upgrade process dialed in, but this blog was hosted on 1and1. I looked at their help to see how to upgrade my database, and a quick check told me that I had to do a lot of stuff to make that happen.
Luckily, I did perform the backups that are recommended as part of the upgrade process with the plug-in. So I took my backup files and copied them over my the WP 2.9 files that had just been copied into my blog directory. My blog was now restored to a state where it was displaying posts, but the editing controls in the WordPress Admin panel were not working.
Upgrading MySQL Database
So I was faced with the task of upgrading MySQL to 4.1.2 or higher. The instructions from 1and1 were:
1) Export your existing database using phpMyAdmin
2) Create a new MySQL 5 database
3) Import your database file into your new database using phpMyAdmin
4) Update your wp-config.php with the new database info
Ok, I think I can handle this.
So I went into the 1and1 control panel and created a new MySQL 5 database. Then I exported my existing database:
Then I imported it into my new MySQL 5 Database:
This seems easy enough. But wait:
Access Denied.
DAMMIT!
Ok. Keep calm. What the __ do I do next?
Well, after trying it a few more times and realizing I wan’t getting anywhere, I called 1and1 support. The support guy had to keep putting me on hold, but eventually he told me that I needed to delete a couple of lines at the beginning of the MySQL back up file.
These lines specified the old database name, which was causing the permissions error.
Ok, now that’s cool, but my SQL backup file is 4.6mb, and I see that the max file size on the import screen is:
(Max: 2,048KB)
Nice.
What I ended up doing is opening the SQL backup file in a text editor, copying the entire text from the file, and pasting it into the box on the import screen.
I was rewarded with a box that said my import was successful.
Finally… Upgrade Success
I then went and put WordPress into maintenance mode, updated the wp-config.php file with my new database settings, took it out of maintenance mode and verified everything was working fine.
Tip: You can put WordPress into maintenance mode by creating a file in your WordPress directory called .maintenance with the following code in it: <?php $upgrading = time(); ?> to take it out of maintenance mode, just rename or delete the file.
Now when I went through the WordPress Automatic Upgrade process, the WordPress 2.9 upgrade went smoothly.
Man, that was a hassle! I wish WP Automatic Upgrade would have told me up front that my database wasn’t compatible with WordPress 2.9. It would have saved me a lot of last minute panic.
Anyway, I wanted to share this in case anyone else has a similar problem and does a panicked Google search looking for a solution!
{ 80 comments… read them below or add one }
← Previous Comments
Thanks for sharing this. I just had to do this for a friend’s site I’m helping out with and would’ve been completely lost if it weren’t for your guide:)
I am having a world of trouble trying to export my wordpress db from 1and1.com are those the settings you used (the ones in the image or did you also select add drop tables and unchecked the Use hexadecimal for binary fields?
To: digitallife
the export should work with all settings as shown;
have you set up the .htaccess file to run your 1and1 site in PHP5?
Thanks. This also helped me migrate. The 1and1 documentation for upgrading databases is too limited and not all that helpful. I ended up cutting and pasting into the query field as well.
I am SO thankful for this, right now I am at the “Ok. Keep calm. What the __ do I do next?” stage! I agree, I wish I knew my database was not compatible before I upgraded all the wordpress files. My client is hosted by 1and1 as well. Thank you for this useful post!!!!
Thank you, thank you, thank you, thank you!!!! I have never ever been able to do a WP auto update and tried every fix multiple times with no success. I finally got the right code for a .htaccess file and then got the same error you did. Thank you so very much for making this blog. I didn’t know the first thing about trying to do this and you’re steps worked perfectly. Thank you!
Thank you for the instructions. It’s maddening how difficult 1and1 makes it to upgrade. I think it’s time for me to switch.
I received the successful upgrade notification and the website looked fine but about 5 minutes later, the whole installation was toast. Couldn’t even log in. Ah well. Starting from scratch, I guess.
Hi–I don’t know what has happened, but I followed your instructions and nuthin! My db is 6 MB and after I copy the text, I get the “success” screen, but the database is empty. When I go back to the database list its not using any of its MB’s! I don’t know if they’ve changed something recently– since everyone had such success with your method. I’m so glad to have your post to follow–it helped me figure out that this is insane, not me. I’m totally switching hosting companies, if I can figure out how! My site is destroyed, at the moment! Yikes! Anyway, I just wanted to say “1and1′ers” beware! Sometimes its a mess!!!
Thanks again–your step-by-step is awesome. I’m glad it worked for some.
Thank you SO MUCH for this posting this information! I thought I would have to give up and start my blog over from post #1. Love those screenshots!
Thanks – this was a surprisingly useful (and highly Google ranked, heh!) article. It is disappointing that there wasn’t more *obvious* public talk about the WP 2.9x requirement for the new MySQL version – yes, they certainly mentioned it, but it’s definitely not obvious enough.
My annoyance also extends to 1and1 now as well, since while they do support MySQL 5.x, it wasn’t obvious from my setup there that this particular blog I’m upgrading was still using the old version. Big Sigh.
I like Dreamhost for web hosting; their interface is quirky, but it offers good support both for non-technical users (you can auto-host various services at Google Apps/Sites/Mail or using their one-click installs) and for technical users (ssh in and control everything). Let me know if you want a referral to try them out for a domain.
Thank you! Your info saved our church site information and I was able to safely bring us back online after losing even site functionality!
Travis
Thanks, this guide was more useful than the one on my webhost’s help pages. Worked perfectly. Bookmarking it just in case an upgrade goes awry again.
thanks so much for this! it was a big help in bringing my site up to speed. glad you did such a thorough job documenting your ordeal.
Just wanted to add my “Thank You” in the pile, you’re a life (or at least Blog) saver!
Don:
It has been reading your – patiently written article, under standing the simplest possible English and doing everything as per. Thank you very much.
I’m an 1&1.com client as well. I did everything right. But I got this warning:
Warning:
Cannot modify header information – headers already sent by (output started at /homepages/37/d94648496/htdocs/example/wp-config.php:1) in /homepages/37/d94648496/htdocs/example/wp-includes/pluggable.php on line 865
ran back to WP 2.8.6 and the blog is alive well.
any suggestions on how to fix this error. I used MS Word to correct the wp.configure.
left out “fun” reading
alive & well from the above comment.
@Vassan,
That error usually means there was a problem with extra characters in the file. MS Word probably caused that. When editing configuration files like that you’ll need to use a “text” editor like Notepad. Try editing your wp-config.php file again using notepad.exe and remove any extra characters at the end of the file.
Hi,
Thanks for a great post, it is helpful to know that someone’s been through just as much headache with 1and1, although they were helpful and handled my database upgrade on the phone with me (mine was over 4 mb and the upload limit is 2mb..). So they ended up doing the upgrade themselves but all of the quotation marks both single and double came out with 4 to 5 really strange looking characters, and there was this weird looking ‘A’ but with some weird squiggle on top of it or maybe it was a ‘^’ anyway I have gone through and deleted lots of those, fixing lots of quotes. Any remedy that you know of for that issue?
Thanks again for being a great help!
Vicki
I’ve got the same question as Vicki. I recently upgraded to WordPress 3.0 on 1&1. (I wish I would have found this blog post first! I ended up writing my own, very similar instructions on my blog because I could find help like this!) My quotation marks also turned into strange characters.
I tried to look into this a little, and I’m wondering if it’s because we originally input those quotation marks with the Visual editor rather than the HTML editor? If that’s the case, do we always need to use the HTML editor to input non-alphanumeric characters?
Thanks in advance.
Thank you, sir, for your great article that expertly and articulately guided me through the MySQL 4.0 -> 5.0 process on 1and1. Keep up the good work – I’ll be adding your blog to my RSS reader.
← Previous Comments
{ 16 trackbacks }