MySQL vs PostgreSQL

At work, we’ve just started converting one of our projects to use MySQL instead of the present PostgreSQL. Due to client orders, you understand. Unfortunately, after using PostgreSQL, coming up against MySQL reveals how bizarre, baroque and arbitrary it is.

  • CREATE TABLE automatically creates a transaction. So much for that schema loading script undoing its changes if it fails.
  • TEXT columns aren’t like PostgreSQL’s infinite length VARCHAR. They’re more like a 64k long blob. Heaven help us if we go over that by accident (which is not improbable).
  • Oh, and TEXT columns also can’t be primary keys. You have to switch to varchar for that. This is a minor (but seemingly gratuitous) incompatibility.
  • I defy anybody to make sense of MySQLs handling of date & time data types.
    • Firstly, you have to choose between datetime or timestamp, which appear to be different variations of the same thing. Except that there’s no choice because you can’t appear to set a default value on a datetime column. So you have to pick timestamp.
    • And then you have to understand TIMESTAMP Properties as of MySQL 4.1. This is incredibly obtuse, but manages to just about make clear the utterly arbitrary limitation that only one column can have a default value. That’s just wrong. No, really.
  • DROP USER doesn’t drop a user unless they have no privileges. What’s the point in that? Thankfully, this seems to have been fixed in later versions.
  • One minor, but exceedingly annoying faux pas: ^W in the mysql command line doesn’t delete the previous word. It deletes the entire line. Strictly contrary to every other user of readline.

I’m using MySQL 5.0.16, which I thought had grown up beyond all this sort of thing. Oh well. None of this stuff is an impenetrable problem, but it’s also certainly not endearing me to us MySQL for future projects.

Update: Just for amusement, witness TeeJay having even more pain going in the opposite direction.



I’ve just switched the theme on this blog to Origami, winner of the typo theme contest. Whilst I think it looks good, it feels a bit slow. And I really dislike the Flash technique for replacing the texts of the headlines. It means you can’t select the text properly any more. So the theme may change again shortly. Given my level of artistic inability, it’s won’t be an original creation though.

Meanwhile, Jeremy Keith has given his blog engine a makeover—Everything old is new again. It looks exactly the same as before, but now comes with added searchy goodness. Of course, he used MySQL instead of a real database, but we can forgive small foibles. ;-)

My only minor critique is that a permanent redirect should be set up on the RSS feed to direct clients towards the new URL. Something like this in .htaccess should do the trick.

  Redirect permanent /journal/journal.rss

I never understood why Apache required you to specify an absolute URL for the destination there. It should be able to work it out if you’ve left the hostname and scheme off…


Blogging Software

It started innocently enough, but as usual turned into yak shaving rather quickly. I wanted to try out some graphical editors for posting to my blog. Sounds simple enough. Except that a while back, I chose wordpress-pg. Its a version of wordpress, but it talks to PostgreSQL instead. Except that for some reason, my copy doesn’t have the xmlrpc.php file needed. So heading over to the wordpress-pg site, I am greeted by this lovely message.

WARNING! This version is probably subject to flaws in xmlrpc handling. Delete xmlrpc.php if present in your install. This flaw can allows outside attackers to execute arbitrary commands as the user your web server runs as.

Oh dear. So I won’t be doing that then. But part of the problem is that it’s already fixed in wordpress 1.5 (MySQL edition). Sadly, wordpress-pg is a very inactive project. I know, it’s open source, fix it yourself. But right now, I more or less just want something to work. And I don’t want to delve into scary php if I can help it (I know, that’s rich coming from a Perl programmer). So what are the choices?

I could look at getting MySQL installed and moving over to wordpress proper. Well, I’ve finally gotten around to doing some MySQL at work and I now understand it a little bit, even if I don’t like it that much. But I still have to get the actual database ported from PostgreSQL to MySQL somehow, which seems jolly arduous.

The alternative is to take a look at Typo, a Rails powered blogging engine. The selling points are that it appears to be actively maintained, it’s written in Rails (which I wish to play with) and also that it has a wordpress import script. So I’ve just spent an hour attempting to convert my wordpress-pg install over to typo. And I have to say that it works pretty well. Here’s what I found.

Firstly, I followed the basic install to get typo up and running. I saw Piers complaining about it, but it’s reasonable for me. But I’ve used rails before, so I’m probably not the target market. Anyway, I got to the admin screens and set up a test account pretty quickly. I then ran the import script giving it my old wordpress database name. Needless to say, it didn’t work first time. I had to make these changes to the script:

  1. Get rid of all backquotes, that’s some weird MySQLism.
  2. I had to change all entry['ID'] to entry['id']. PostgreSQL downcases things.
  3. I had to rework the trackback support slightly. Because wordpress-pg is based on wordpress 1.2, trackbacks are stored as a comment that starts with <trackback /> at the beginning. So I had to change comment_type = 'trackback' into comment_content LIKE '<trackback />%'
  4. The import script runs in the dev environment by default, but I had been testing in the production environment. This meant I had no users set up in my dev environment. Consequently, I had to patch up the relationships between the users and the articles afterwards (update articles set user_id=1;). Actually, looking at the script, it doesn’t seem to make those links anyway…

The biggest problem was actually getting the script to access the original data. The script assumes that you can do the MySQLish thing of accessing another database by specifying dbname.table. This doesn’t work in PostgreSQL. But, you can take advantage of the fact that PostgreSQL does support schemas, so instead, you can create a schema called wordpress and load a dump of the main database into there. I had to make a few edits to the dumpfile before loading it in though.

  1. Get rid of the stuff about plpgsql at the top. I don’t use it.
  2. Add CREATE SCHEMA wordpress and SET search_path = wordpress, public, pg_catalog.
  3. Comment out all the ALTER foo SET OWNER TO wordpress lines. You have to import it as the typo user and don’t want that changing behind your back.

After the import you can just run DROP SCHEMA wordpress CASCADE to clean up.

After that, I now have a working blog with all my old content. Hurrah! Not simple, but not too difficult either.

Now before I get this main blog converted over, I just need to work out how to run FastCGI scripts from Apache, something I haven’t done before. And then I’ll be away, and I can uninstall wordpress-pg.



SiteBar looks useful. And it has a firefox extension…

But their server software needs MySQL. I like PostgreSQL. Bah. That sounds like work. Thankfully, their database abstraction looks reasonably clean. Oh well, here we go.

Update: no, the mysqlisms are too deeply embedded. Or at least, I don’t know how to sensibly deal with assumptions about mysql_insert_id that aren’t true in a PostgreSQL world.


wordpress & backslashes

One of my pet peeves about wordpress is that because it’s based on PHP and MySQL it doesn’t like backslash characters at all. It just eats them up and screws up your post. But I’ve found a simple answer. Use numeric entities instead. Just type in \ or \ to get a nice \backslash.