Friday, January 25, 2008

Multiple triggers for the same event - Bug or feature?


Greetings from the Southern Hemisphere.
The World tour proceeds as planned, although with some added tasks in the meantime. The solution to the quiz announced from North America is now given from Australia. (Boy! I love the global village!)

The task was to create a series of three triggers, all associated to the same table for the same event (BEFORE INSERT). If you had a look at the manual, you would know that you can't set multiple triggers for the same event. However, there is a workaround, using the Federated engine. The solution to the quiz was already available as a comment to a bug report. My intended solution, very similar to the one provided by the winner, is reported below. Notice that I use the CREATE SERVER statement instead of hardcoding username and password in the CREATE TABLE statement. The quiz required using MySQL 5.1.22 or later for two reasons:
  • the ability of using the CREATE SERVER statement, instead of hardcoding the connection credentials. This feature was introduced in MySQL 5.1
  • The ability of using a federated table pointing to a table in the same server. This behavior was introduced in MySQL 5.1.22.
(Hubert Roksor, send me your address if you want the T-shirt, or post the address as a comment. I won't publish it, but just get the address for the shipping).

DROP TABLE IF EXISTS t1, t1_f1a, t1_f1b, t1_f1c, t1_f2a, t1_f2b, t1_f2c;
DROP SERVER IF EXISts s1;

CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;

CREATE SERVER s1
FOREIGN DATA WRAPPER mysql
OPTIONS (
host '127.0.0.1',
port 5123,
database 'test',
user 'msandbox',
password 'msandbox'
);

CREATE TABLE t1_f1c (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f1b (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f1c';
CREATE TABLE t1_f1a (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f1b';

CREATE TABLE t1_f2c (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f2b (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f2c';
CREATE TABLE t1_f2a (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f2b';

CREATE TRIGGER bi_t1_fed_1 BEFORE INSERT ON t1_f1a
FOR EACH ROW
SET NEW.c = 'something';

CREATE TRIGGER bi_t1_fed_1b BEFORE INSERT ON t1_f1b
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' more');

CREATE TRIGGER bi_t1_fed_1c BEFORE INSERT ON t1_f1c
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' !!');

CREATE TRIGGER bi_t2_fed_1 BEFORE INSERT ON t1_f2a
FOR EACH ROW
SET NEW.id = NEW.id + 10;

CREATE TRIGGER bi_t2_fed_1b BEFORE INSERT ON t1_f2b
FOR EACH ROW
SET NEW.id = NEW.id + 100;

CREATE TRIGGER bi_t2_fed_1c BEFORE INSERT ON t1_f2c
FOR EACH ROW
SET NEW.id = NEW.id + 1000;

INSERT INTO t1_f1a VALUES (1, 'nothing');
INSERT INTO t1_f2a VALUES (1, 'nothing');

SELECT * FROM t1;
This works as expected. However, the above behavior is not in the manual. Is it a bug or a feature? Since it is not in the documentation, nor was in the original specifications, this behavior is a useful side effect. Strictly speaking, it's a bug. Pragmatically, it can be consider a feature, depending on your needs.

4 comments:

Hubert Roksor said...

I couldn't agree more about using CREATE SERVER. In fact, when I submitted my solution to that quiz the other day, the very instant I clicked on "Submit" I thought to myself that if I had used CREATE SERVER I wouldn't have had to edit all those statements to remove my actual password. CREATE SERVER is definitely one important feature.

As for whether that "trick" should be considered a bug or a feature, it never crossed my mind that it could be considered as a bug. Actually, I don't really consider a feature either, merely an aspect of MySQL that isn't subject to the current restrictions on stored routines and triggers, restrictions that I hope will be lifted in a future version, as soon as possible. Multiple triggers per table, recursive triggers and statement-level triggers are all features I can't wait to experiment on MySQL.

One last word about FEDERATED + TRIGGERs, I think the combination has a lot of potential and I'm glad it's slowly gaining more exposure. We often hear about how the big guys (be it Digg, Flicker, LiveJournal, etc...) partition their data on multiple servers (sharding). It's easy to imagine the extra complications involved when data has to move from one server to another, transiting by a third party (hence using twice the bandwith), wouldn't it be the perfect place to use FEDERATED? I can imagine myself inserting into a BLACKHOLE table, relying on triggers to insert the data into the right FEDERATED table/server :)

Anyway, sorry to rant on your blog. In short, multiple triggers for the same event: feature. Having to use FEDERATED for that: bug.

PS: of course I want that t-shirt! XD Address sent by email.

Bill Karwin said...

Pardon me for being blunt, but this solution is brittle and confusing. The bug, if one exists, is that MySQL does not allow multiple triggers per action per table.

Consider InterBase/FireBird. They do have syntax to specify multiple triggers for a single action/table (they have had this since circa 1994). You can specify the order of execution.

Imagine how much clearer it would be to do this:

CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;

CREATE TRIGGER t1_c_1 BEFORE INSERT POSITION 1 ON t1
FOR EACH ROW SET NEW.c = 'something';

CREATE TRIGGER t1_c_2 BEFORE INSERT POSITION 2 ON t1
FOR EACH ROW SET NEW.c = CONCAT(NEW.c, ' more');

CREATE TRIGGER t1_c_3 BEFORE INSERT POSITION 3 ON t1
FOR EACH ROW SET NEW.c = CONCAT(NEW.c, '!!');

CREATE TRIGGER t1_id_1 BEFORE INSERT POSITION 1 ON t1
FOR EACH ROW SET NEW.id = NEW.id + 10;

CREATE TRIGGER t1_id_2 BEFORE INSERT POSITION 2 ON t1
FOR EACH ROW SET NEW.id = NEW.id + 100;

CREATE TRIGGER t1_id_3 BEFORE INSERT POSITION 3 ON t1
FOR EACH ROW SET NEW.id = NEW.id + 1000;

I know MySQL has implemented triggers according to the ANSI/ISO SQL standard, which does not have the POSITION syntax. But this improvement in InterBase/FireBird (implemented before triggers were specified in the SQL-99 standard) is a very sensible extension.

Giuseppe Maxia said...

Bill,
Your reasoning makes sense, and many people have requested such feature.
From a QA point of view, this is not a bug, and it must be treated as a feature request. The company has to consider the available resources and the tasks at hand to set development priorities.
Perhaps if someone submits a patch ...

Bill Karwin said...

Hi Giuseppe,

Certainly, I understand and I sympathize about allocating limited resources to feature requests by priority. I've worked as a project manager and product manager in the past too.

I was labeling this as a "bug" in a rhetorical sense. :-)

I would love to work on this feature if I had time. I would also like triggers to have some mechanism to "abort" the operation that spawned the trigger. We'll get there someday...