perldiver

Hey everyone out there with MySQL chops, can you help me out?


I have a table that looks like this:

CREATE TABLE videos
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
url TEXT NOT NULL,
url_md5 VARCHAR(255) NOT NULL UNIQUE,
[...various irrelevant columns snipped...]
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created TIMESTAMP NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET=utf8;

Here's what I'm trying to accomplish:

1) The URL must always be unique, but is likely to be longer than 255 characters (meaning it needs some variation on a TEXT datatype to hold it).
2) The 'created' field automatically shows the date/time that the row was created, and is automatically set by the DB.
3) The 'last_updated' field shows when the row was last, and is automatically set by the DB.

Doing *either* #2 or #3 is easy -- the default value takes care of that.  But MySQL can only have one TIMESTAMP DEFAULT CURRENT_TIMESTAMP field per table, and it must be the first timestamp in the table.  Ok, make it the last_updated.  That means we need a trigger to update the 'created' column at insert time.

Doing #1 is harder -- TEXT fields cannot be given a unique constraint.  To solve this, I added the 'url_md5' column; this column will be automatically updated at insert and update time so that it always contains the md5 hash of whatever is in the 'url' field.  We set a unique constraint on THAT and call it good.


To implement all this, I wrote the following trigger:

CREATE TRIGGER videos_newrow BEFORE INSERT ON videos FOR EACH ROW SET NEW.created = NOW(), NEW.url_md5 = md5(NEW.url);

This works fine on my local machine, but fails when I attempt to run it on DreamHost.  The error I get is:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'


Here's the thing that confuses me:  the DreamHost version of MySQL is a *more recent* version than mine, yet this works on my box and not theirs.  Here are the versions:

dreamhost>  mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2


localhost> mysql --version
mysql  Ver 14.12 Distrib 5.0.41, for apple-darwin8.5.1 (i686) using readline 5.0

 
Does anyone have suggestions for a workaround?  I'm almost ready to do all of this in code and call it a day, but would really prefer not to -- that way lies madness and corrupted datastores.