Club Troppo

The Great Troppo Migration of 2010 continues to be approximately 10,000 times more stressful than planned.

The latest episode of madness was an attempt to more fully bring across user details from the previous database.

The proximate cause of my woes has been that export/import tools built into WordPress are, basically, crap. This is not a total surprise coming from the “programmers” behind this pretty-but-buggy system. It probably worked fine on the trivial little click-around tests they ran on their computers. It is functionally broken in real world use due to combination of PHP limitations and pisspoor design.

What it does in the trivial, tiny migration is fantastic. It brings a user, their posts, their categories, their tags and all the comments for their posts across in a single file without a hitch. However it doesn’t work if that file is too big. By themselves, Ken and Nicholas have posted too much for the facility to handle.

So I turned to plan B, which was an arduous and painful. This reveals the root cause of my woes, which is that MySQL is a piece of poop. Congratulations, WordPress, you’re no longer to worst software in the stack.

Every important item in WordPress has an identity: every post, every author, every comment etc is marked with an ostensibly unique numerical ID. This ID is provided by MySQL with a facility called AUTO_INCREMENT. It seems like a wonderful facility … right until you need to merge databases.

This is because AUTO_INCREMENT does not actually produce unique identities. It just produces a sequence of integers, starting at one and going up by one for each new record. This works fine in a single database, because the numbers are always going up and won’t collide. But it is useless when you try to combine databases, because MySQL will happily trample any existing records with the same ‘unique’ ID when you try to combine records into a single database.

What MySQL should provide is some sort of proper unique ID type. In other databases you can get unique types called IDENTITY, or sometimes, UUID. The later stands for Universally Unique ID. A UUID or IDENTITY column is guaranteed to be unique not just within the database, but unique to all databases, everywhere, for all time (subject to the limits of 128 bit numbers). If in the first place MySQL had offered some UUID or IDENTITY type, WordPress records would be uniquely identified across databases. Moving users from one database to another would not have involved hours and hours of tedious and error-prone numerical substitutions that had to run in a precise order. All this mess might have been finished a lot sooner.

The bottom line

The whole point of last night’s attempt to bring user details across was twofold:

1. To bring across email addresses so that users could reset their password if necessary, and
2. To make it unnecessary to reset passwords by restoring the ones from the original database.

Goal (1) was apparently successful. Goal (2) was an utter failure. I did more than fail, I actually broke other login details. How on god’s green earth I did that is a total mystery to me as my SQL commands don’t even refer to other logins. But there it is.

So the upshot for Ozblogistan login-holders is: you may still need to do the ‘reset password’ dance, but at least now it’ll get to your email address.

I’ll keep looking at it, but at this stage I am not hopeful of a better resolution.