WordPress and Microsoft SQL Server

I really like WordPress; it’s quick to set up and easy for beginners to pick up and blog with. So I decided to start using it for projects at work. Was I in for a shock…

Our server platform at work is all Windows based. Running various editions of Windows Server 2003 and Microsoft SQL Server 2005 (MSSQL). We’ve got a good robust setup with a high level of redundancy, including our SQL Server cluster; if we’re going to create websites for customers using WordPress, we want to leverage the full capabilities of our platform. This is where I hit a problem; WordPress is tightly bound to MySQL, which is normally all well and good (I’ve got no problem with MySQL), but I don’t want to set up and maintain two RDBMS when we’ve got a perfectly good one as it is.

And so began my quest three-day quest to get WordPress running on MSSQL.

First problem I had, how to get WordPress to use a database library other than PHP’s built-in MySQL support. This was an easy one thanks to the PDO (SQLite) for WordPress plugin; this contains code to intercept the DB calls and rewrite them, but to SQLite’s SQL syntax. Thankfully the SQLite driver provides a good template for producing additional drivers and it didn’t take too much work to write one to rewrite MySQL syntax into T-SQL as used by MSSQL and wire it up to the PHP pdo_mssql extension. This was simple enough to do but it still didn’t work correctly, if we want to send the MSSQL equivalent of IF NOT EXISTS, we need to use a SQL batch, not a single command. Turns out when you send a batch to the library behind the PHP pdo_mssql extension (freetds) it doesn’t like it, not reading all the data back from SQL, causing the “Attempt to initiate a new SQL Server operation with results pending” error. A few tweaks to the C code of freetds changed the behaviour to mimic that often found in ODBC drivers, only returning the last result of a batch.

There’s probably a better way of fixing this, i.e. returning all the result sets in some manner, but I’m not sure if or how MySQL presents such things. I also think the PDO for WordPress plugin could be optimised by stripping out values from the rewritten queries and caching them as templates. Something else to look at when I’ve got some spare time.

Anyway, I was rather happy when WordPress’s install script successfully created the schema within the database along with all the required keys and indicies. Not for long though; as soon as I loged in every page request would be greeted by an error in the code to manage the rewrite rules. Upon closer inspection I found that something was truncating the rewrite_rules value in the wp_options table, preventing the data from being unserialised. Good old Google provided the diagnosis (but not the cure); PHP’s MSSQL PDO extension truncates long text fields to 4096 characters. Setting mssql.textsize and mssql.textlimit in php.ini didn’t help, only the native mssql extension picks these up, not the PDO extension.

After a of hacking around in the C source for PHP, I managed to get it to set the database options required for returning longer values. Almost there…

Apart from yet another error! It seems that Microsoft’s DB lib which is used behind freetds to provide the connection to SQL has another useful strange behaviour, returning empty strings as a single space. Brilliant! The most obvious side effect in WordPress was that every page and post was protected by a single-space password.

After not being able to find a sensible way to fix this, I wrote a horrible hack for freetds to detect result columns containing only a single space and replacing them with an empty string. This isn’t ideal, as unlikely as it is, you may actually want to store a single space in a value, but Microsoft’s aging library appears to offer no way to distinguish between ” and ‘ ‘.

At some point, when things have quietened down a bit, I’ll try to neaten up the modifications to the various libraries and plugins and release a set of patches.