New and improved: Now with BETA-2!!!

Confounded by a seeming lack of ability to accurately migrate users from one or more pre-3.0 WordPress (WP) blogs into a WordPress 3.0 (WP3) blog, I scribbled up some code to take care of the snarky part of the move.

Download WP_UserConvert_1_0_b2.zip

The Problem

WP blogs store their data in MySQL tables. One table, typically named ‘wp-users’, is referenced by the posts table (‘wp_posts’) and the comments table (‘wp_comments’). If you install WP3, you have the same setup if it’s configured to only have one blog site. Where things get interesting is when you take advantage of the multi-site ability of WP3 and create another site, let’s call it “Cameron”. This adds tables such as ‘wp_2_posts’ and ‘wp_2_comments’ for that new site. (In case you’re wondering, there is no ‘wp_1_’ prefix for the first site, that’s assumed with the ‘wp_’ prefixes.) Here’s where we get into trouble: there is no ‘wp_2_users’ table. All the users are still stored in just the one ‘wp_users’ table. Which users have access to which site is managed by adding more records to the standard ‘wp_usermeta’ table.

Confused yet? No? Well, it gets better.

Let’s say that we now create another site in WP3–we’ll call this one “Bob”–so now we also have to track ‘wp_3_posts’ and ‘wp_3_comments’. Some of the users are the same in both “Cameron” and “Bob” but each also has users unique to their own site. How do we merge their ‘wp_users’ tables into the single WP3 ‘wp_users’ table and still maintain sync with ‘wp_2_posts’, ‘wp_3_posts’, and so forth that will contain links to user IDs that will no longer be valid?

Prep Work

We’ll assume you have WP3 installed and working in multi-site mode because, if you don’t, all the rest of this is moot. Make sure that you didn’t forget that within the wp-content folder you must have a blogs.dir folder. Also, make sure you’ve backed up your database as we’re about to muck with it and it could mess things up. Lastly, make a note of users whose access level is other than “subscriber” as the program is currently dumb about that.

Step 1: Create the new site in WP3

Nothing out of the ordinary here. You’re just establishing the ID for the site within the database. Make note of the site ID

Step 2: Export database tables from your old site.

For the purpose of this example, we’ll assume a simple site that doesn’t involve tons of plugins. Managing a myriad of configurations is beyond the scope of this little missive.

You will need to export at least these files (assuming a ‘wp_’ prefix, yours might be different):

• wp_comments
• wp_links
• wp_postmeta
• wp_posts
• wp_terms
• wp_term_relationships
• wp_term_taxonomy
• wp_usermeta
• wp_users

You’ll note that a difference from some other migration guides are ‘wp_usermeta’ and ‘wp_users’.

3: Update the SQL

The SQL file you get needs some work to prepare it for importing into the database. These are all find/replace operations for the entire file, so you’d best load the file into a text editor. (For all of these, assume the double quotes aren’t there. Also, if the # symbol is used, substitute for it the site ID you made a note of earlier.)

• Replace “`wp_” with “`wp_#_” (that’s a left tick mark, not an apostrophe, btw).
• Replace “wpcontent/uploads” with “files”
• Replace your old site URL with your new site URL. For example: if your old site was “http://samplesite.com/blog”, and you are moving to a subdirectory-based scheme, you’d want to replace the old name with “http://blog.samplesite.com”.

Save your updated SQL.

4: Import the Data

Open up the WP3 database. Find the tables with the correct site ID that correspond to the tables you’ll be importing. You can delete these tables, but being the paranoid sort I’d suggest you simply rename them for now (prefix them with “old_” or something) so you can set things back if the update doesn’t work.

Now, import your updated SQL.

The Update Routine

If you haven’t already, download WP_UserConvert_1_0_b2.zip

Upload the userconvert.php file (it should have a CRC of 1D643FE7) to the root directory where you have WP3 installed. This is necessary so that it can access the data contained in the wp-config.php file in the same directory.

Using a browser, run the userconvert.php file.

You’ll be presented with a list of sites it has detected that also have “users” tables associated with them in the database. Select the radio button next to the one you want to migrate at this time and then press the submit button.

If all goes well, the program will print out what part of the process it is in. Assuming you don’t get an error message (which likely means there’s a bug in the code…oops) it should finish with “The program is finished. Exiting.”

At this point, your main users table should have the new additions, and the posts and comments tables should point to the right users in that table. We can only hope.

Last Bits

Next, you’ll need to move your files–particularly your image files–from your existing site’s “wp-content/uploads” directory (or wherever you have it) to your new “wp-content/blogs.dir/#/files” directory (“blogs.dir/#/files” resolves to “files” within WP3).

At present, the user migration is simple and generic. This means that anyone with anything other than subscriber access will have to have their user level manually changed after the move…so you’ll probably need to dive in an manually change them.

Clean-up

If you wish, you can delete the “wp_#_users” and “wp_#_usermeta” tables at this time, though you might delay that until you’re certain that you won’t need them.

For userconvert.php to be most effective, it also created and left behind a table named “um_user-migrate”. It exists solely so that you don’t accidentally overwrite already processed sites (with the program still in beta, the code gets run a lot…it’s a precaution). You can delete it when you are sure your users migrated.

Once you are finished with it, I strongly recommend deleting userconvert.php from your directory so that it can’t be used, maliciously or accidentally, to change your database.

The Disclaimer

The software referred to is “as-is”/”use at your own risk”. No warranty or claim is made that it will work correctly for you. DO NOT use this on a site whose database hasn’t been backed-up. This routine directly writes to and updates the WordPress database and should be considered a potential threat to the stability of the database as well as the functioning of the WordPress installation itself. You are on your own and assume all the risk of running this software (including, but not exclusively referring to: lost time, productivity, monetary costs, reputation costs, site stability, user or administrative satisfaction, exceeding CPU quotas from your server host [and the consquences thereof], etc.). I have tried to add error-checking where important, and have tested it with a variety of configurations. Even so, just because it works for me doesn’t mean it will necessarily work for you. Fair warning.

Last Thoughts

I wrote this to overcome an annoying little migration problem. My hope is that WordPress will address this in the official release and all that happened was that I exercised some long-neglected programming muscles.

The code isn’t overly efficient, does little error-trapping, isn’t terribly robust, and isn’t very elegant. I just wanted something that worked. So far, it has for me. Then again, I’m not trying to break it. This is very much beta software.

I’m making it available so anyone else having the same issues as I was can see if this helps. Perhaps they can tweak the code (it’s GPLv2, just like WordPress).

As for me…I need to work on an associated routine to process the user ID links connected with the Simple:Press Forums plugin. Won’t that be fun?

Releases

WP UserConvert 1.0 beta 1
WP UserConvert 1.0 beta 2