MySQL 5.0 in production. And so is my blog. With TRIGGERS to generate feed IDs.

With the release of MySQL 5.0.15, the first 5.0 build to be marked as a production release, I also release my blog to the public.

You'll read about my experience handling the transition from 4.1 to 5.0 and using all it's new features, on my personal web page as well as in the company work I do for MeteoNews GmbH and RADIO TOP.

It took me a little longer to release my blog, as I wanted to write a little blog/feed/cms on the LAMP stack from scratch. And yes, it's actually LAM5P!

So let's see one first example of how I used the new features of MySQL 5.0.

Use of a TRIGGER to generate feed IDs

My blog uses an Atom feed and therefore every entry needs a globally unique and persistent ID.

Globally unique means that you can't just use your own primary key here and persistent means that you'll probably have to store your Atom ID along with your entry, so it will still be the same when you transfer your data to another system. Read How to make a good ID in Atom on this topic.

Now, wouldn't this be a perfect use for TRIGGERs? I wanted my feed IDs to conform to the Tag URI format and still wanted them to be based on my primary keys for the category and the story (called item in my database, as there will probably be items other than only stories on my web page in the future).

That's my items table with the constraints and some of the fields omitted for brevity:

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL default '0',
  `id_tag` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`item_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The primary key item_id was of type auto_increment at first and I wanted to write a trigger to automatically create the id_tag from it on every insert. But I already hit a limitation of MySQL 5.0 writing my first trigger ever! The manual states, that an auto_increment column will always be zero when accessed from within a BEFORE trigger. It seems that the auto_increment value only gets computed after the trigger has fired.

Now, what's next? We actually don't even need MySQL's auto_increment feature anymore. We can easily build our own replacement.

We just need an additional table and a stored function:

CREATE TABLE `sequences` (
  `sequence` char(32) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY  (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE FUNCTION `sequence`(name CHAR(32))
    RETURNS int(11)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    INSERT INTO sequences (sequence, value)
    VALUES (name, LAST_INSERT_ID(1))
    ON DUPLICATE KEY UPDATE value = LAST_INSERT_ID(value + 1);
    RETURN LAST_INSERT_ID();
END

Now using this sequence function instead of the built-in auto_increment feature we're finally ready to write our trigger:

CREATE TRIGGER ins_items
BEFORE INSERT ON `items` FOR EACH ROW
SET NEW.`item_id` = sequence('items'),
    NEW.`id_tag` =
        CONCAT(
            ( SELECT `value`
              FROM `configuration`
              WHERE `key` = 'canonical_host' ), ',',
            DATE(NOW()), ':/',
            NEW.`category_id`, '/',
            NEW.`item_id`
        );

As you can see, I take the hostname from an additional table where I keep all the global configuration settings for my web page.

And that's what the id_tag for this article hopefully will look like after I insert it to the database using the new trigger:

www.futhark.ch,2005-10-25:/4/104

We've already seen a minor limitation in my first article on MySQL 5.0. But most of all we've seen the real power it gives us: Missing a feature? Just build it yourself!

If you still think it would be useful to be able to access auto_increment values from BEFORE triggers, just write a comment to my Bug #14053 and suggest to open it again as a feature request.