perldiver: A false-color multi-spectrum image of Sol. (Default)
posted by [personal profile] perldiver at 06:26pm on 29/07/2010
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.

Reply

From:
Anonymous( )Anonymous This account has disabled anonymous posting.
OpenID( )OpenID You can comment on this post while signed in with an account from many other sites, once you have confirmed your email address. Sign in using OpenID.
User
Account name:
Password:
If you don't have an account you can create one now.
Subject:
HTML doesn't work in the subject.

Message:

 
Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

December

SunMonTueWedThuFriSat
      1
 
2
 
3
 
4
 
5 6
 
7
 
8
 
9
 
10
 
11
 
12
 
13
 
14
 
15
 
16
 
17
 
18
 
19
 
20
 
21
 
22 23
 
24
 
25
 
26
 
27
 
28
 
29
 
30
 
31