Or “How to move your blog to a new server and upgrade to WordPress 2.1 at the same time.”

I was spending an inordinate amount of time spinning my wheels (and losing traffic) for a while there because my server kept going down on me. I finally started getting quite vocal with my Web host, and they agreed to point my domain to another of their servers. They admitted to me that there’s a user on my old server who frequently causes the database to crash. (Too much activity, it seems.)

So Friday afternoon, I was off on another big blogging adventure. Since I was moving servers anyway, I decided that I would simultaneously upgrade my WordPress from version 2.04 to version 2.1. It turned out to be quite the exercise in frustration. It went easily enough, at first. I uploaded the WordPress 2.1 files to the new server and installed them. Easy enough. Then I backed up the database on my old server (using wp-db-backup) and stored the .zip file locally. Then I created a database via my new vDeck’s phpMyAdmin and imported the backup data.Everything seemed to be going swimmingly. Until I logged on to my WordPress admin panel, that is, to find that I had a bunch of comments but no posts. None. Nada. Zilch. (Damn….) I knew then that it was time to go fishing around in the database. So I wrote a couple of throw-away posts (“test, test, test” affairs) in WP2.1 and saved them. Then I did another database backup. That way I had copies of the database structure for both WP2.04 and WP2.1. I unzipped both of the .zip files and put them up side-by-side on my text editors so I could compare database structures. Sure enough, the kind folks at WordPress had made changes to the post table structure (’wp_posts‘ table). Here’s what the WordPress 2.04 wp_posts table structure looks like:

In the database for 2.04, the ‘post_status’ field was used to distinguish between blog posts and static Pages. The values used were ‘publish’ for blog posts and ‘static’ for Pages. The ‘post_type’ field wasn’t used.  In the database for WordPress 2.1, the wp_posts table structure changed in a couple of subtle ways. Here’s what it looks like:

In 2.1, the value in the ‘post_status’ field is always ‘publish’. The differentiation between blog posts and static Pages is made in the ‘post_type’ field, where the value can read either ‘post’ or ‘page’.So using my text editor, I cut out the wp_posts table structure (CREATE TABLE ‘wp_posts’) for version 2.04 and inserted the wp_posts table structure for version 2.1. I then browsed through the data contents for wp_posts until I found and amended all the entries for my static pages. I wasn’t hard, because I only have four static pages. A little judicious help with the text editor’s “find” function helped a lot.

When that was done, I very carefully used the “find and replace function” on the text editor to amend all the data contents for my blog posts. Having more than 200 posts, it would have been a tedious job without the help of “find and replace”.Having achieved all that, I went into my vDeck, logged onto phpMyAdmin, and killed the existing database. Then I recreated it and imported the amended data.Keeping my fingers crossed, I logged back on to WordPress 2.1. Voilà, I had my posts back!

Update: I forgot to mention that due to the strange category-link relationship monstrosity in WordPress 2.1, my post-to-categories relationships were all messed up, as well. I manually went into each and every one of my 222 posts and reset the categories. I don’t know if I had to do it that way, but after the post/blogroll fiasco, I was in no mood to be messing around further with phpMyAdmin and the database. Did anyone else out there lose their post-category relationships, or was I just being hard headed?

But then there was the problem with links (under the “blogroll” tab in the version 2.1 admin panel). For some reason, the WordPress people made some fundamental changes to the way links are handled. They’ve associated links with categories (which I don’t care for at all) and totally screwed up the blogroll function for anyone upgrading from an older version. I started playing around with it and tried to store a link, but was rewarded with this nasty little error message:

WordPress database error: [Table ‘wordpress.wp_link2cat’ doesn’t exist]

Oh, great! I thought. More database work. I went into the WordPress Codex and found this neat little diagram outlining the table structure for wp_link2cat:

Which is fine and dandy, if you’re schooled in MySql, which I most definitely am not. I logged onto phpMyAdmin anyway and messed around with creating a new table for the database. I was too afraid that I would really mess things up, though, so I abandoned that idea. So I jumped onto Google to see if I could find the actual MySql syntax for the wp_link2cat table structure. Lo and behold, I found a Spanish language site that had just what I was looking for! Here’s what it looks like:

I went back into my text editor and dropped this little bit of code into the end of the database:

Back into phpMyAdmin to dump and reload the database. Sure enough, I was able to log on to my WordPress admin panel and make blogroll entries without getting any annoying error messages.I can’t guarantee that the blogroll fix is going to be fully functional. With the bizarre changes that were made to the way links are handled in 2.1, I can’t display my blogroll on my sidebar (widget-enabled) for the time being.Update (2/12/07 3:29 AM Mountain): It looks like my blogroll fix is going to be stable. And I found a fix for displaying the blogroll in my sidebars. Go here to read about the great widget I found.

That’s what I’m going to be working on next. Anyway, I’m sure there will be more WordPress 2.1 adventures ahead! In the meantime, I’ve successfully moved to my new server and I have an upgraded WordPress blog….

Next time I’ll post about Anna Nicole Smith. NOT!!!!!

For more information on this topic:

Technorati Tags: blog, new server, upgrade to WordPress 2.1, traffic, server, Web host, domain, user, database, blogging, moving servers, WordPress, 2.04, 2.1, upload, WordPress 2.1, install, wp-db-backup, .zip file, vDeck, phpMyAdmin, import, backup data, admin panel, comments, no posts, posts, WP2.1, database structure, WP2.04, unzipped, text editor, table structure, wp_posts, WordPress 2.04, ‘post_status’, blog posts, static Pages, ‘publish’, ‘static’, Pages, ‘post_type’, CREATE TABLE ‘wp_posts’, find, data contents, find and replace, amended data, category-link relationship, monstrosity, categories, fiasco, post-category relationship, hard headed, links, “blogroll” tab, blogroll function, error message, WordPress database error: [Table ‘wordpress.wp_link2cat’ doesn’t exist], WordPress Codex, diagram, wp_link2cat, MySql, Google, MySql syntax, wp_link2cat table structure, code, log on, blogroll entries, can’t guarantee, fully functional, bizarre changes, can’t display my blogroll, sidebar, widget-enabled, blogroll fix, stable, display, widget