Last night’s upgrade to Movable Type 3.17 went fairly well. The upgrade from 2.6 to 3.17 was trivial–it installed and ran without problems. I didn’t really expect that, but I wasn’t about to complain. Once I verified that 3.17 was working, I started the process of moving Movable Type from using the Berkeley DB onto PostgreSQL, and then things started breaking.

Movable Type, like most open-source software, was really designed to use MySQL, with Postgres support added later. I’ve found a lot of software that’s supposed to work with Postgres, but actually fails in a number of fundamental ways, simply because there are a number of MySQL-related assumptions lurking in the code, and no one actually tested the system against Postgres.

In Movable Type’s case, the BDB-to-SQL conversion script is able to create the right set of tables in Postgres, and it’s even able to populate the tables with all of the right data. Unfortunately, it fails to update the sequence counters for any of the tables, which causes new articles and comments to show up as ID #1. If you already have a #1, then posting new articles and comments will silently fail.

It took me most of an hour to track that back and figure out why new comments weren’t being posted. Then it took about 15 seconds to fix–just do a select max(comment_id) from mt_comment to determine the maximum comment ID, then do select setval('mt_comment_id',9999), replacing 9999 with the number from the previous select. Then repeat for every table in the DB.

So, once that was done, everything worked, right? Ha. I was now able to post new comments and articles, but they didn’t show up on my site. Hitting ‘rebuild’ didn’t actually rebuild anything. So what happened this time?

Ah, the conversion script failed to populate the template table, so MT didn’t know which pages to build. Fortunately, I keep all of my templates in text files, not in the DB, so it only took 15 minutes to re-enter all of the template data; once that was done I was able to add comments and articles without problems, and hitting ‘rebuild’ correctly rebuilt the whole site.

So, everything appears to be working now. On the plus side, comment posting is way faster–it used to take around 30 seconds from the time users hit ‘publish’ until the browser reloaded. Now it’s down to 5 seconds or so. Hopefully that’ll cut down on the spate of duplicate comments that I’ve seen, because people won’t be so tempted to keep hitting ‘publish’ every ten seconds until it works.

Now that that’s done, I’m going to move on to testing out Typo. I noticed that the MT-to-Typo conversion script has a couple obvious MySQL-isms in it, but the script is so short that it shouldn’t be hard to work around.