All the Perl that's Practical to Extract and Report

Throwing Away All of My Trigger Work

Ovid on 2009-02-27T12:10:09

Unless someone on our team thinks of an incredibly creative solution, all of my work with triggers will need to be thrown away. I've used them previously, but only in our test database. Unfortunately, if you read the "create trigger" documentation carefully, it mentions a very interesting caveat:

In MySQL 5, prior to version 5.1.6, creating triggers required SUPER privileges (annoying, but I can live with that) and so does executing them.

We're on 5.0.45. Naturally, our production code is not going to run with SUPER privileges. Even if we were so foolish as to think this was a good idea (and yeah, go ahead and run Apache as root, will ya?), we share this database server with other teams who would strenuously object to our running as SUPER. Plus, upgrading to 5.1.6 means negotiating with all of those other teams. I don't think that's going to happen.

I have a lot of unpleasant work ahead of me ripping out triggers and reimplementing them in our DBIx::Class code :(

Aside from the fact that we're on an older version of MySQL, how on earth could the MySQL developers have thought that requiring SUPER privileges to run triggers was a good idea?

That's for CREATE, not running

autarch on 2009-02-27T14:07:07

Reading the docs, it seems like it's just for creating the triggers, not executing them.

Re:That's for CREATE, not running

Ovid on 2009-02-27T14:48:42

Sorry, but not quite correct. It's a pain to read through, but this is the killer:

At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:

  • The TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
  • The SELECT privilege for the subject table if references to table columns occur via OLD.col_name or NEW.col_name in the trigger definition.
  • The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger definition.
  • Whatever other privileges normally are required for the statements executed by the trigger.

That was not fun to track down, but there ya go.

Re:That's for CREATE, not running

autarch on 2009-02-27T17:46:46

Ugh, MySQL is _so_ lame.

Re:That's for CREATE, not running

TeeJay on 2009-02-27T19:33:48

Have you tested that? .. as it still seems to apply the privilege tests to the user that defined the trigger, rather than the user invoking it.

Re:That's for CREATE, not running

grink on 2009-02-28T01:07:44

Like Teejay said, DEFINER

Are you seeing something we're missing?

Bartek Jakubski

migo on 2009-02-27T23:49:51

We are using MySQL 5.0 heavily and I can assure you that running triggers does NOT require SUPER privileges.

There are plenty of reasons to hate MySQL, but this is not one of them :-)

use the sandbox

mapopa on 2009-02-28T04:13:56

you can use sandbox to create and 5.1 db and then access it with an port number something like localhost:10000 http://www.howtoforge.com/quick-db-setups-with-mysql-sandbox

also everything on your host remains unchanged the other mysql will run on 3306 as usual ps: i wish we had in firebird something similar to sandbox it's easy to create an script that starts multiple/versions/db servers

Finally!

manilodisan on 2009-08-21T20:20:58

The only host I found so far to support mysql triggers on shared packages is at [url=http://www.placehost.net]placehost.net[/url]. I hope more will come. This is probably because hosting providers wait for plesk or cpanel to support such versions of PHP/MySql and those two are moving slower than anything else. PHP 5.2.9 came out recently and a lot of bugs were fixed yet nor cpanel or plesk supports this version.