Tuesday, 22 March 2011

Using Wordpress 3.1 on Drizzle

Since the GA release of Drizzle7 I've had several people asking me about how to convert their MySQL sites to use Drizzle instead.  By far the most common one to crop-up is Wordpress.  This is aimed to be a simple guide to starting a new blog using Wordpress 3.1 and Drizzle.

Initial Problems


Wordpress by design is very MySQL orientated, for the most part this is good thing, but when trying to switch to another database for it there can be complications.  An attempt has been made to create a plugin to use Drizzle, but unfortunately it has side-effects such as modifying your content if you happen to blog about anything related to MySQL or Drizzle.  For the purposes of this blog post I have create a patch and will give instructions on how to use it below.  If any Wordpress guru has a way to make this into a good plugin, please get in touch!

Conversions Needed


Almost all the conversions for Wordpress 3.1 revolve around the date.  When creating a draft or any other table entry Wordpress uses the date '0000-00-00' in several columns.  In Drizzle we try to be closer to the SQL standards, and this means that the first valid date is '0001-01-01'.  A large majority of the patch is this particular conversion for the queries throughout the PHP code.  The rest is to do with schema creation, to be specific:

  1. Drizzle has no LONGTEXT, TINYTEXT, etc...  Just TEXT

  2. Drizzle doesn't support multiple character sets, just UTF-8, so we need to drop the character set part of schema creation


The Patch


To patch your wordpress 3.1 source:

  1. Download the patch

  2. Enter the directory of your wordpress installation

  3. Run the following


patch -p1 < wordpress-drizzle.diff

You should now be good to run the install as normal.  Noting that if you are not using the mysql-unix-socket-protocol plugin that you should tell Wordpress to connect to '127.0.0.1' for a local database instead of 'localhost'.

Converting an Installtion


If you already have Wordpress 3.1 installed and using MySQL the patch combined with drizzledump's migration function should still work but I have not tried this, so please backup first before attempting it.

6 comments:

  1. Great to know. I'll look at migrating one of my WP installations.

    It would be good if in your patch you abstracted out the value '0000-00-00 00:00:00' to a config variables, that way this value only has to change in the code in one place. It's then a lot easier to also sell to WP for future version, even just abstracting that value and submitting without mentioning Drizzle, just DefaultDate for Other Databases.

    Your Drizzle patch could be then much simpler.

    ReplyDelete
  2. Hey Ronald,

    Thanks for the feedback.

    That is a good idea. I was planning to create a better version of the patch before the UC anyway (this was my sort-of 5 minute fix mostly using 'sed').

    ReplyDelete
  3. I was wondering if you were far enough along to comment on how you'd assess the performance of WP on Drizzle vs. MySQL.

    Thank you!

    ReplyDelete
  4. Hi Seth,

    I try not to do such benchmarks since I can easily tune one to outperform the other, I leave that to more unbiased people than me. What I can say is this blog had 4500 visitors (multiple visits and multiple pages per-visit) on March 16th (most in the space of 6 hours) with no dent in performance.

    ReplyDelete
  5. Great! I guess that was more along the lines of my questions. I know that benchmark data can be "interpreted" but the real test is whether or not you (or your visitors) have experienced any sort of performance hits.

    Thanks for sharing your experience!

    ReplyDelete