Triggers in MySQL: a cautionary tale

I had the pleasure of handling a recent customer support request asking that they be able to install some triggers into their MySQL database one of our shared web hosting servers. This should be no trouble, you'd think. You just GRANT them the CREATE TRIGGER privilege and be done with it. Or so I thought. </didactic narration>

Triggers are a relatively recent addition to MySQL, having appeared in version 5.0. Postgres has had them since 1997 and they're consequently more mature. Yet another great reason to choose Postgres.

As a disclaimer, this applies to Redhat Enterprise Linux 5.2 with packaged MySQL 5.0.45, which is what we run on our newest shared web hosting server.

Unlike in Postgres, defining triggers in MySQL isn't handled sanely.

I'm not even sure that this is "fixed" in 5.1.6, as the documentation seems to indicate that even though you can now GRANT privileges to define triggers with the owner as the DEFINER (ie. non root), the DEFINER probably still needs to have SUPER.


Wiki: dedicated/MySQL_Triggers (last edited 2008-12-23 13:43:42 by AndrewRogers)