A tiny glitch for those of us with old databases.
One of the things I love about the most recent releases of WordPress is the automatic upgrade feature. Not only does the software tell you when a new version is available, but it offers a one-click upgrade through the use of the Upgrade Automatically button in the Upgrade WordPress administration panel. I’ve been using this feature regularly since it first appeared — after backing up my WordPress database and content files, of course — and have never had a problem.
Until yesterday.
Last night, when I attempted to upgrade my main blog, An Eclectic Mind, I got the following error message:
I knew immediately why this error had appeared. My blog is so old that MySQL 5 wasn’t available when it was created. The available version was 4.0.27, which is what I installed. But the new version of WordPress needs a newer version of MySQL. Upgrading wouldn’t be possible until I upgraded my MySQL database.
I called GoDaddy, my hosting company, and spoke to someone in technical support. She said that the only way to upgrade the database was to back up my database, create a new database with version MySQL 5.0, and restore the old database to the new database file. I could then point my config.php file to the new database and, with a lot of luck, everything would work out fine.
So I initiated a backup last night using GoDaddy’s backup utility. (I usually use the WordPress Backup Plugin.) I got tired of waiting for it to finish, and went to bed. This morning, I’m restoring that database into a new file created with MySQL 5.0. And now, as I type this, I’m modifying the config.php file to point to the new database, user name, and host name.
Drum roll please….
As I open the home page for my blog…it works!
The upgrade should now go off without a hitch — which it does, as shown here.
Unfortunately, if you’re in the same boat I was in, you’ll need to find out how to update your MySQL database file. Talk to your system administrator or ISP’s technical support department. Every system is different — I use GoDaddy.com so that’s the only system I know how to update. Providing detailed step-by-step instructions for that system would only help other GoDaddy users — and might not work after GoDaddy’s next interface revision.
So I’ll let you track down instructions for your server or ISP on your own. Once you get those instructions, it shouldn’t be difficult to complete the task.
Good luck and enjoy the newest version of WordPress!
I was able to export my MySQL 4 database to a new MySQL 5 database, edit my wp-config.php file, and upgrade to WordPress 2.9. However, I’m noticing “junk” in the form of random question marks appearing on various posts, but not all. Any thoughts on how to fix this?
Jon: Unfortunately, I’m seeing the same thing. It’s mostly weird characters just before special characters such as ° and ¢. Apparently, MySQL 4 handled special characters differently than MySQL 5 does.
I’m sure the problem could be fixed by querying the database with some sort of find and replace command. I’ve done this in the past for simpler chores. If you know how to do queries try that. If you don’t know how, you could research it and learn — but not from me; I don’t know enough about it to instruct.
My solution is to simply reupload all the posts. I use an offline editing tool which was not affected and have all 2,000 of my posts stored within it. Of course, this doesn’t solve the problem if it appears in comments.
Whatever you do to resolve it, I’d appreciate a quick comment here to let me know. In the meantime, if I find a foolproof query solution and it works for me, I’ll likely blog about it here. I’m sure we’re not the only two WordPress users dealing with this right now.
Thanks for the response. Unfortunately, I too am no guru when it comes to MySQL. It looks like there’s a long list of incompatibilities between MySQL 4 and MySQL 5: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-previous-series.html. This issue is probably buried there somewhere.
I’ll keep my eyes and ears pealed for a solution.
Jon: Good luck. If I get time this week, I’ll hunt around a bit. I distinctly remember doing a find/replace in my database a long time ago, but can’t remember HOW I did it. Maybe I’ll find something online to refresh my memory.
Just had exactly the same problem with my GoDaddy-hosted WordPress blog. Thanks for putting up this note. It was very helpful and I got my blog running on mysql 5.0 quite easily. Phew!
Jay: Have you also had weird characters appear alongside special characters in your text? That’s one of the side effects of upgrading to 5.0, I think.
Thanks, Maria. A couple of additional tips:
1) I needed php.ini file in each of my blogs to increase the memory allocation for PHP up to 64M. I used this code in the php.ini file with success:
;; set memory limit for cache.php
upload_max_filesize:20 M
max_execution_time:300
memory_limit:64 M
post_max_size:8 M
SMTP:localhost
2) For updating from mysql v4 to v5, I ran SQL exports to a file of the v4 database, but then hit a wall in the “import file” option. It only allowed 2mb file so I had to break up my SQL export into 6 partial files. Pretty straight forward though one has be careful on breaking the file. Also remember to remove the “create database” line a the top of the file since all one wants is to create and populate tables.
Overall, fairly straight forward once I got my plan and was careful on the steps. Remember to also keep your v4 databases and old wp_config.php file safe in case you need to return to it.
Okay, a couple things to add on the weird junk characters appearing. I’m told that (1) I may have exported the database incorrectly (although I used WordPress’s built-in backup tool, so I’m not sure what I could’ve done differently); and (2) the weird characters are showing up at space characters that didn’t get translated properly, and that there should be a query to run to clean all of those up. Unfortunately, I haven’t figure out what that query is yet!
Merry Christmas!
Here’s a website which seems to offer a solution to the junk character issue: http://www.realestatebloglab.com/real-estate-blogging/restore-your-wordpress-database-from-mysql-4-to-mysql-5/. If I’m reading this correctly, if you have to import the database using an ASCII character set, and then use the search and replace plugin on his site to replace the “??” characters. The kicker seems to be that by importing with the ASCII character set, “??” is created instead of the question mark inside a black box (which is what I’m getting) and search and replace can replace those characters.
This is probably one of many solutions. I’m not at my home computer at the moment with my database backup so I can’t try this, but will do so when I return (unless I find a better solution).
Jon: I’ll definitely review the article. I’ve noticed a slowdown in my database’s response time, especially for administrative tasks. But I didn’t do the export/import. I did a backup and restore, using my host’s MySQL tools. That might be my problem. Perhaps I should have exported from within WordPress.
I have another site to update — it’s still running a VERY old version of WordPress — and I’ll experiment a bit with that. If I have success, I’ll redo my main site and (of course) blog or comment about it here.
Thanks again for the link.
Maria,
I’m pleased to report that the instructions in the website I posted here worked, and I have successfully removed the ? characters. Let me know if you have any questions and I’d be happy to help out.
Best,
Jon
Jon: Glad to hear it and thanks for the offer. Right now, I’m more concerned about performance than the characters. But I’ll give it a whirl and see if it helps. Also, for another site on 4.0, I exported from within WordPress and then imported into a new database within a new WordPress installation. That worked well, but I did lose some of the databases (not related specifically to WordPress).
Thanks for updrading wordpress tutorial.