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!
{ 12 trackbacks }
{ 64 comments… read them below or add one }
I need help on WordPress and your information is beneficial to me.
Thank you and God bless you.
Thanks for posting this, it has helped many people who are having problems with the Wordpress 2.9 – MySQL issue.
Ugh, what a nightmare. I’ve had trouble upgrading Wordpress in the past, but it’s always been user-error, never hosting-company-error. Glad you were able to get through it. Happy holidays, Don!
Man, I have so many questions. For starters, how did you know what export settings to use? I’m quite lost — and 1&1 only lets me have one database, so I’m terrified of deleting the one before the MySQL 5 version is up. Just installing Wordpress uses all my tech-know-how, so this whole upgrading / database business is way over my head. I wish Wordpress had said something like “CASUAL USERS BEWARE, UPGRADE LIKELY TO FUBAR EVERYTHING” so I would’ve known to stick with the last version.
I had the same problem with 1and1.com and went through the same steps some days ago. …I thought everything was fine until I checked my site! All quotation marks where now looking like this : ???BA
Had to do a search and replace through out the files
Geoato,
I have the same problem — what files do you have to search through? I guess I don’t understand why it’s doing this, so I’m unsure how to go about correcting it exactly.
Thanks for rescuing me from a potential Wordpress diaster in my time of need with this tutorial. Got it right on the first try.
@Kwan, @Everett – you bet. I’m glad it helped. I know the panic you felt when you got 3/4 way through the install and had that error!
@David Mihm – thanks for stopping by, and Happy Holidays to you too
@Kevin – I’ve got a developer’s package so I can create 100 databases. I wouldn’t do this without being able to create a new MySQL 5 db to import into. I wonder if support would help you, or if you could upgrade to a package that gives you more than 1 database?
@Geoato – I did not have the strange character problem. I’ve heard of some other people who ran into that but they exported in a different way than I did… Good thing you were able to correct it.
@Kevin, BTW, I love that avatar!
Thanks so much for your help, I finally got it working. I’m too cheap to upgrade my package and 1&1 wouldn’t give me an extra db temporarily, so I quell my nerves and just backup & delete… not smart but it worked out in the end. I was really excited, but it was basically all your effort that did it. My backup was 8mb so your tip about editing the file and then pasting it was what helped the most. Thanks again.
Don I get the same above error but when I go into my godaddy phpMySQL and check, I do have the MySQL 4.1.22 supposedly required for WP 2.9. How do I get my wordpress automatic upgrade to recognize that and not 4.0.27. Any ideas? Thanks a lot for your contributions.
Ugh, this is infuriating me to no end — after tons of “no mysql query” errors, now it’s telling me that I’ve had a successful import, but that there are no tables in the database. I’ve followed this guide and several others, but none are helping.
By the way, I’m looking at the screen where you imported the files — and whereas you appear to have selectable Search, Query, and Export items, these are greyed out on mine. I’d assumed that was because I was starting with a blank database, but theoretically so are you… so do you have any idea why our displays would differ there?
@Wes – yes I was working with a brand new MySQL 5 database in that screen too. I don’t know why those tabs aren’t selectable on yours, but I didn’t use them for the import. the only one I went to was the SQL tab which is shown in the screen shot.
In your previous comment you mentioned that the import did not work. How exactly did you do the export? How large is you import file?
@Joe – I told WP Automatic Upgrade to do a “clean up” before re-performing the upgrade. Did you try that?
I tried it several ways to no avail, but for one of them I followed the very same steps you’ve listed above.
Now, I just did get it to work… but since I did pretty much the same thing I’d been doing before I’m not exactly sure how or why. Here are the main changes, though, in case anyone else is still having trouble:
– I kept getting consistent errors using the myphpadmin export file, so I used the straight up backup file from WordPress. (My sql file was roughly 5MB and I kept getting wicked lag/timeouts with the pasting method, so maybe the WP backup lacks whatever problems the myphpadmin file had and the process was aided by the gzip format.)
– Instead of selecting “autodetect,” I manually specified that the import file was gzipped. I’m pretty sure I’d already tried using a WP backup, so this is really the only new change! Except…
– I waited a good 45 mins or so after creating the database to give this a whirl. The control panel kept telling me that the database was “ready” within a few minutes of creation, but maybe it really needed more time.
Anyway, we’re good now. Thanks for the response and help, Don!
@Wes – that’s great, I’m glad you got everything up and running again!
-Don
Thanks for posting this information.
It was very helpful in showing me what the problems were and pointing me in the right direction in overcoming them.
The only difficulty I had was at the end putting Wordpress into maintenance mode. I got around this by downloading the wp-config.php file, changing the database information to the new database and uploading the updated file. Piece of cake from there.
@Bill – I’m glad it helped, and it’s good to know you got everything working.
I’m still having probs, mainly b/c I refuse to create new databases.
I’m using 1and1 as well, but have been using MySQL 5.0 for months, if not more than a year at this point. So I’m having a hard time believing that this error has anything to do with the version of MySQL and is some other bug in the upgrade process.
Your databases didn’t automatically upgrade to SQL 5.0 when 1&1 upgraded – I would check to make sure they are not using an older version of SQL. I had the same problems as Don and his instructions were implemented to fix those problems. Another thing for 1&1, you will need to turn on PHP 5 compatibility or else the dashboard and editing will be bugged. Instructions: http://faq.1and1.com/scripting_languages_supported/php/9.html
Thank you SO much for this blog – I basically hyperventilated when I saw that error message. Even though it took me like 3 hours to fix this, I am glad I was finally able to! THANKS.
Phew! It worked. Thanks so much for the great instructions.
t
How do you Import once you have the MySQL 5 database created? I can not get this to work for the life of me and get a very strange error.
There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:
I’m glad to have found your step-by-step advisory, cause I managed to upgrade only every second blog (hosted at 1&1 Servers) cause of the size of my old databases. Now I know, what to try. Thank you!
Markus
Endless thanks for putting this together, Don. No doubt you’ve helped more Wordpress users on this issue than, well, Wordpress.
Interesting, possibly helpful side note: I couldn’t get past the SQL backup file error message when I was on Firefox, even though I followed your instructions faithfully. Instead of editing the backup file any more (a dangerous move for me), I tried switching to Chrome, and the upload went through the first time. Hmm.
Don, I’d just like to add to the chorus of those you’ve helped by posting this. Thank you for taking the time to do so! Thanks to you I was able to complete my upgrade. Happy New Year!
Thank you so much for your post! Very helpful.
Don,
Many thanks for sharing your knowledge, this has been a nightmare to rectify until I came across your tutorial.
Big thanks
David
Thank you, thank you, thank you! Worked like a charm.
Thank you so much for your post! Great help!
Thanks for the upgrade tip. 1and1.com really needs to make it easier to not only do fresh installs of WP but also upgrading the outdated DB apps as well.
Thanks for writing the tip – lazy 1and1.com actually sent me here for help upgrading!
Have a great 2010
Hi! I tried to follow your instructions, but got stuck after a certain point. I’ve created a new database and tried to paste in the code, but it won’t paste. I’m not sure why. If I copy just one chunk, like the ‘create tables’ code in your own screenshot, it goes in just fine. Maybe it won’t let loads of text get pasted? I’m not sure, but I was wondering how it went for you in that step. Did you paste the code in its entirety?
Wow, this is like a total recap of everything that I’ve been trying to do as well. In fact, I saw some of those same wacky errors that you did as well.
Worked for me – step by step through the process on 1&1. Thanks very much for the idea, especially around manually inserting the SQL export in the new database.
Interestingly, in setting up a new MySQL5 database, I ended up inadvertently creating two. Probably fat fingers on my part, but I’m curious if that happened to anyone else. Easy enough to delete the second.
Finally, I’m in 100% agreement – it is a mistake to advise running the automatic upgrade without checking the database version first.
Thanks for the help. I wasn’t sure how I was going to go about upgrading MySQL.
I don’t remember the clean up step in the automatic upgrade. Are you referring to the clean up option in automatic upgrade that comes with WP 2.7 or the extension that you have to add on? Right now it doesn’t matter which since I can’t get out of the upgrade database required screen. Pretty much hosed.
@Trisha – my database backup file was 4.6mb and I copied and pasted the whole thing. How big is your backup file? Which browser are you using?
@Joe – it sounds like your Wordpress is not pointing to a MySQL 5 db. Did you point to the new db you created in your wp-config.php?
@Don – My file size is over 70 MB (!!). So, today I thought I’d just copy and paste in chunks. I sat there for about an hour and a half copying and pasting and I still had barely cracked the surface of the file.
Does -anyone- have patience for something like that? I don’t.
I found another post like yours online that suggested exporting as a gzipped file instead and uploading that. For some reason, when I try to export in any zipped format, the corresponding files comes out as 0 bytes and corrupted.
I thought maybe I could compress the SQL file myself, and after much time figuring that out, when I go to upload it, it says ‘No SQL’.
In other words, I see absolutely no workaround to my problem. My database is just too darn big.
Trisha,
I tried that first too – I tried gzip and then zip formats, neither of them worked on the upload. So that’s why I went to the copy and paste.
Here’s another method you can try, but it requires SSH access to your server:
myShell:~ > mysql -h dbxx.domain.net-u xxxxxxxx -p xxxxxxxx < dump_file.sqlWhere the xxxs after -u is your username and the xxxs after -p is your password.
Thanks so much for the post.
I also had issues with pasting over 5 mb of text. I took the chunking approach. Took too long – maybe 15 minutes or so…. seemed like a really long wait at the time.
I like WP, but every upgrade, edit, etc. seems to just take way longer than it should, and the higher the version number, the greater the issues of file corruption and other nags – at least in my experience.
That being said, your post certainly saved me the time you spent with 1&1. Thanks!
Have you tried using the gzipped backup created from within WordPress? That’s what ultimately ended up working for me! I guess that’ll only help if the file ends up being less than 2MB, though.
I concur that 1and1 should get up to speed and update their software, yet the real boot-to-the-head goes to the upgrade author for not checking the version. In the past I have been amazed at the quality of the upgrade tool (yes, I even donated money to him!), but he was asleep at the wheel on this one.
Thanks for locking me out of my blog… Yes, I DO have the backups, but I also have dialup internet access!
Im having a major problem creating this new database. I followed your steps but anytime I try to paste in my text it crashes any browsers I use. The text file ends up 4.4 MB and the gzipped version ends up 727 KB. But I cant get the text for the text copy and paste way because if i copy all and paste all it just crashes.
And the gzip way gives me an error saying :
Error
SQL query:
– phpMyAdmin SQL Dump
– version 2.6.4-pl3
– http://www.phpmyadmin.net
–
– Host: dbxxx.perfora.net
– Generation Time: Dec 31, 2009 at 06:13 PM
– Server version: 4.0.27
– PHP Version: 4.3.10-200.schlund.1
–
– Database: `db123456789`
–
CREATE DATABASE `db123456789`
MySQL said:
#2013 – Lost connection to MySQL server during query
________________________
can you help me?
Ok, so went ahead and copied small amounts of text at a time (which i realized I could do while making my first comment here, lol) and that worked. Thanks, I did get a few weird character errors in a few posts but nothing major. Appreciate the help!
Don, you’re right on. My blog doesn’t point to the new database. And in researching this problem, I’ve discovered that my blog has been pointing to a database that I can’t administer with the godaddy phpmysqladmin tool nor can godaddy tell me where it exists. So now I can’t make changes to any of the mysql tables and I’m still locked with the “upgrade database screen.” Here’s where my wp-config.php points to. Do you have any ideas how I can still copy this database and at least start over? Godaddy claims its some kind of private server. I’m totally confused.
// ** MySQL settings ** //
define(‘DB_NAME’, ‘user_xxxxxx’);
define(‘DB_USER’, ‘xxxxxx_xxxxxxx’);
define(‘DB_PASSWORD’, ‘xxxxxxxxxxx’);
define(‘DB_HOST’, ‘10.0.11.xxx’);
Thanks
Joe: You should not post that information online, even with the password voided out. A good hacker could have fun with that.
Just ask GoDaddy to send you the database file (.sql).
Good point Chris – I edited Joe’s comment to remove any details that a mean spirited hacker might look for
@Joe – is your site actually hosted at GoDaddy? Or just the domain?
If it is hosted at GoDaddy I would call back until you get someone who can give you the sql file for your database. If it is hosted somewhere else and GoDaddy is just your domain registrar, then that’s why they can’t give you the info. You’ll have to contact your hosting provider.
Chris, Thanks for the hacker tip, but I wasn’t too worried about it as I’m going to have to take the site down and start from scratch somewhere else with a different host than godaddy. They have been a total pain for customer service. Don, the site and domain are supposed to have been hosted by godaddy. You shouldn’t have to go through endlessly long phone calls just to get a simple thing like this taken care of.
I agree. GoDaddy is not the way to go for hosting WordPress. Since you are doing a WordPress site you should use BlueHost.
As I mentioned above, 1&1 is good too. They don’t have the one-click installs for WordPress like BlueHost does though, you have to install it manually.
Thanks a million for posting this and happy new year!!!
Hi Don,
Just wanted to send my thanks for these instructions! I’m glad I decided to search before trying to upgrade to MySQL 5 myself. I had an 8MB database file to import. Your instructions worked perfectly. I think I had my site in maintenance mode for less than 60 seconds while I made the necessary changes. You are my hero!
Cheers!
UGHHH!
Been fighting with this for over an hour. Your a lifesaver.
1and1 has something in their help files about adding a line to the .htaccess file – that wasn’t working. Finally I bit the bullet and called tech support and they said to build an entire new database and import the old one. I figured that would be a piece of cake – I guess wronged.
Now that I’ve seen what you found out I’m frustrated that MySql didn’t clarify what the errors were. Even after I deleted the old database info it kept looking like it was uploaded and OK but the data hadn’t changed. Glad you found the copy/paste text process. Otherwise I would have just kept uploading my file over and over again to no avail.
Thanks again!
Thanks! I just ran into this while trying to upgrade my site. Luckily I used the built in Upgrading tool which told me I couldn’t upgrade because of the db version before it started the upgrade.
Since I use 1&1 your instructions are perfect.
Thanks again!
Don, can I just add my thanks. It took a while but I’ve managed to upgrade to 2.9. I suspect I couldn’t have done it without you.
Thanks and best wishes from snowy Wales
Wow! This was exactly what I needed.
Thanks for putting this online. I had no idea what to do before seeing this.
Thank you. This is exactly what I needed.
Thank you so much for this tutorial! I’ve been struggling with these same issues for days. I followed the steps you laid out and everything is running like a Swiss watch.
Thanks again!!
This helped me out big-time. Thanks!