What I Learned Upgrading to WordPress 2.9

by on December 22, 2009 · 95 comments

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!

Like what you read?

If so, please join over 10,000 people who receive exclusive weekly online business and blogging tips, and get a FREE COPY of my eBook, 10 Steps to Free Website Traffic! Just enter your name and email below:

Subscribe Now

Article by »

Don is an entrepreneur based in Silicon Valley. He founded Expand2Web and is the publisher of the Expand2Web Blog, the SmallBiz WordPress Theme and the GetFiveStars Customer Feedback and Reviews service.

Don has written 279 articles on Expand2Web

Author Connect » Twitter | | Facebook |

How The SmallBiz Theme Makes Your Business Website Way Better

If you're building a business website these days, you're in luck.

WordPress has revolutionized the ease and power of what a website can do and be. Now you can quickly create a website that is optimized for top search results, and update it yourself whenever you want.

As you'll see in a moment, the SmallBiz Theme is much more than a mere WordPress theme. It's a complete solution for your business web presence including a Website, Blog, Facebook Page and Mobile Website.

Let's take a look at each of these, and how they help you generate new business from the web…

Click to continue...

See my disclosure about advertising and affiliate links

{ 95 comments… read them below or add one }

Adrian February 14, 2010 at 5:03 am

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:)

Reply

digitallife February 18, 2010 at 5:02 pm

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?

Reply

johngreg February 22, 2010 at 1:08 pm

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?

Reply

dfb February 27, 2010 at 11:36 pm

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.

Reply

voiceover March 13, 2010 at 8:44 am

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!!!!

Reply

Julia Grace March 13, 2010 at 12:27 pm

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!

Reply

Bob Cowell March 14, 2010 at 10:48 am

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.

Reply

Farrin March 20, 2010 at 6:31 pm

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!!! :D

Thanks again–your step-by-step is awesome. I’m glad it worked for some.

Reply

Jean April 10, 2010 at 12:48 am

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!

Reply

Shane Curcuru April 17, 2010 at 5:55 am

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.

Reply

Travis April 28, 2010 at 5:06 pm

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

Reply

nonobadkitty April 30, 2010 at 4:23 pm

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. ;)

Reply

Mike May 24, 2010 at 10:15 pm

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.

Reply

ChantelleJoy June 5, 2010 at 5:18 pm

Just wanted to add my “Thank You” in the pile, you’re a life (or at least Blog) saver!

Reply

Vassan June 19, 2010 at 4:45 pm

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.

Reply

Vassan June 19, 2010 at 4:47 pm

left out “fun” reading

alive & well from the above comment.

Reply

Don Campbell June 20, 2010 at 10:43 am

@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.

Reply

Vicki Berry July 6, 2010 at 3:00 pm

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

Reply

Kyle Sorkness July 9, 2010 at 11:00 am

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.

Reply

Brandon July 11, 2010 at 1:07 pm

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.

Reply

Jeff September 21, 2010 at 2:09 pm

Thank you soooooo much!!!! I was pulling my hair out over this one!! (figuratively…I’m bald! lol)

Reply

David Stembridge October 6, 2010 at 8:08 am

Hi Don, so happy I stumbled across this great little post, and have used it several times. I do have a question though, some of my characters got converted during the transfer process, and I was wondering if there was a way to correct this site wide, rather than individual posts
The character I acquired is: “?” and I think it replaced the apostrophes as well as some spaces.

Reply

Johnny Chan October 22, 2010 at 1:43 pm

Thanks for the info and including pics. I’m not sure why I’m still with 1and1, I have other hosting accounts (all with cpanel) and it’s so much easier.

Everything just seems so much harder with 1and1 – and their support does not solve anything easily!

Reply

Durks December 3, 2010 at 8:52 pm

Suhweet!
I too had a 1and1 package that only allowed a single database so I had to export, delete, create a new ver5 database, and was a little gunshy after receiving the error that it couldn’t import my existing SQL dump.
But your explanations (those screenshots with the little yellow arrows ROCK, by the way – because that was exactly my hangup) had me back up and running in maybe 15 minutes.

T H A N K Y O U !

Reply

Carla January 13, 2011 at 3:06 pm

Thank you! I’m having the exact same problem with 1&1 and a wordpress upgrade right now. Hope your instructions work for me as well.

Reply

Marly Manning February 19, 2011 at 2:30 pm

Excellent! Very helpful, many thanks!

One update for people using 1and1.com – when my Sql4 to SQL5 migration failed, 1and1 tech support said I needed to put the name of the new data base and host name in the header of my file before pasting it in and uploading.

This means changing both the db123456 and the db1234.perfora.net names before you upload it into the new Sql5 database.

Be careful about your data base passwords. The “Error establishing a data base connection” error message I kept getting was due to my password in the the new SQL5 file not matching what I had in the old file.

Reply

Vassan February 19, 2011 at 3:36 pm

Read Don’s note on how to upgrade several weeks ago & I didn’t or couldn’t upgrade as my database file was larger than what was permitted. After I renewed my 1& 1 registration last month, called 1&1 for help. One of their techs’ , a gem of a person uploaded the large database file to the newly created database running on mysql 5.0, on my behalf! I was pleasantly surprised. I was considering leaving 1&1. Not now.

Reply

Juriste July 17, 2011 at 7:21 pm

Thx mate, It’s almost 3 month that one of my wordpress website is down and I didn’t have the time to fix it…
I had the same issue you gave me the solution!

Reply

Emory Flannery August 24, 2011 at 11:17 am

Man I love this article and it was so fabulous and I am definetly going to bookmark it. I Have to say the Superb analysis you have done is greatly remarkable.Who goes that extra mile these days? Well Done. Just one more tip you shouldget a Translator for your Worldwide Readers

Reply

Kevin October 23, 2011 at 11:56 am

Thank you so much for your article. I was finally able to update WP to the latest version after giving up for over 2 years.

Reply

FincaBlog January 17, 2012 at 4:27 am

Many thanks for this post. Deleting the old database info in the mySQL backup file was very useful to know.

Reply

Marcus January 20, 2012 at 5:13 pm

Anyone out there? This article has been hugely helpful and I really appreciate Don’s willingness to share. I’ve followed all the steps carefully and finally got a “Success” from the PHPMyAdmin. (I did follow Marly’s advice, as well, and updated the new host name in the header in addition to deleting the old db’s info.)

However, everytime I’ve tried these steps, I do the final wp-config file update, refresh my site in the browser, and I’m greeted with a brand-spanking new WP Install screen (…url.com/wp-admin/install.php). Just like it was the first time I was installing WP.

Has this happened to anyone else? Is there an easy answer that I obviously am missing?
Thanks in advance.

Reply

Don Campbell January 20, 2012 at 6:03 pm

Is your wp-config.php pointing to your new database? It sounds like there might be a problem there.

Reply

Marcus January 20, 2012 at 6:33 pm

Thanks a bunch for replying so quickly.
Re: wp-configThat’s what I’ve thought. But I’ve checked it over and over and everything is good. So frustrating. I’ve lined up my old (MySQL 4) and new (5) wp-config.php files side by side to compare just to make sure that I didn’t leave traces of old in the new. Everything is clean and should be fine. And I definitely changed the hostname, even though WP says, “99% chance you won’t need to change this value.”

As a test, I’ve gone into my root folder and re-uploaded my original wp-config.php file and, not surprisingly, my old site is back. I’ve then gone back to the new config file (renaming the old one, then, renaming the new one to the proper “wp-config.php”) and still no dice.

It’s weird as the file s are identical save for all the DB info changes.

Aargh.

Reply

JT January 8, 2013 at 1:02 am

Thank you so much for this useful guide. I’m so glad I stumbled upon it in my search on how to upgrade to WordPress 3.5 (MySQL 4.1 to 5.0).

Reply

Leave a Comment

{ 20 trackbacks }

Previous post:

Next post: