The Days of Wonder News Center is running Wordpress which until a couple of days used Gengo for multilingual stuff. Back when we started using Wordpress for our news, we wanted to be able to have those in three (and maybe more) languages.

At that time (in 2007, wordpress 2.3), only Gengo was available.
During the last years, Gengo was unfortunately not maintained anymore, and it was difficult to upgrade Wordpress to new versions.

Recently we took the decision to upgrade our Wordpress installation, and at the same time ditch Gengo and start over using WPML, which is actively maintained (and looks superior to Gengo).

So, I started thinking about the conversion, then looked on the web and  found how to convert posts, with the help of those two blog posts:

• Migrating Wordpress from Gengo to WPML
• Converting Wordpress from Gengo to WPML – part 1

Those two posts were invaluable for the conversion of posts, but unfortunately nobody solved the conversion of translated categories… until I did

So here is the most complete recipe to convert from Gengo 2.5 to WPML 1.8, with updated and working SQL requests.

Pre-requisites

You might want to stop the traffic to your blog during all this procedure. One way to do that is to return an HTTP error code 503 by modifying your Apache/Nginx/Whatever configuration.

1. Log-in as an administrator in the Wordpress back-end, and deactivate Gengo.
2. Install WPML 1.8, and activates it to create the necessary tables. I had to massage WPML a little bit to let it create the tables, YMMV.
3. In the WPML settings, define the same languages as in Gengo (in my case English (primary), French and German)
4. Finish the WPML configuration.
5. If you had a define(WP_LANG,…) in your wordpress config, get rid of it.

Converting Posts

Connect to your MySQL server and issue the following revised SQL requests (thanks for the above blog posts for them):

Converting Pages

This is the same procedure, except we track ‘post_page’ instead of ‘post_post’:

Category conversion

This part is a little bit tricky. In Gengo, we translated the categories without creating new categories, but in WPML we have to create new categories that would be translations of a primary category.
To do this, I created the following SQL procedure that simplifies the creation of a translated category:

Then we need to create translated categories with this procedure (this can be done with the Wordpress admin interface, but if you have many categories it is simpler to do this with a bunch of SQL statements):

Bind translated categories to translated posts

And this is the last step, we need to make sure our posts translations have the correct translated categories (for the moment they use the English primary categories).

To do this, I created the following SQL request:

The request is in two parts. The first one will list all the French translations posts IDs that we will report in the second request to update the categories links.

Since a few months we are monitoring our infrastructure at Days of Wonder with OpenNMS. Until this afternoon we were running the beta/final candidate version 1.5.93.

We are monitoring a few things with the JDBC Stored Procedure Poller, which is really great to monitor complex business operations without writing remote or GP scripts.

Unfortunately the migration to OpenNMS 1.6.1 led me to discover that the JDBC Stored Procedure poller was not working anymore, crashing with a NullPointerException in the MySQL JDBC Driver while trying to fetch the output parameter.

In fact it turned out I was plain wrong. I was using a MySQL PROCEDURE:

DELIMITER //
CREATE PROCEDURE `check_for_something`()
READS SQL DATA
BEGIN
SELECT ... as valid FROM ...
END //

But this OpenNMS poller uses the following JDBC procedure call:

{ ? = call check_for_something() }

After a few struggling, wrestling, and various MySQL JDBC Connector/J driver upgrades, I finally figured out what the driver was doing:

The driver rewrites the call I gave above to something like this:

SELECT check_for_something();

This means that the procedure should in fact be a SQL FUNCTION.

Here is the same procedure rewritten as a FUNCTION:

DELIMITER //
CREATE FUNCTION `check_for_something`()
RETURNS int(11)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE valid INTEGER;
SELECT ... INTO valid FROM ...
RETURN valid;
END //

It now works. I’m amazed it even worked in the first time with 1.5.93 (it was for sure).