Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Friday February 27, 2009
07:10 AM

Throwing Away All of My Trigger Work

[ #38563 ]

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?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Reading the docs, it seems like it's just for creating the triggers, not executing them.

    • 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.

      • Ugh, MySQL is _so_ lame.

      • 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.

        --

        @JAPH = qw(Hacker Perl Another Just);
        print reverse @JAPH;
      • Like Teejay said, DEFINER

        Are you seeing something we're missing?

  • 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 :-)
  • 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 [howtoforge.com] 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
    --
    flamerobin.org
  • 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.