Can't create MySQL trigger with TRIGGER privilege on 5.1.32

Question

My fellow developers and I have our own development schemas on a shared MySQL development database. My assignment requires me to create triggers in my schema, yet I've been unsuccessful so far.

CREATE TRIGGER myTrigger AFTER DELETE on myTable
FOR EACH ROW BEGIN
    -- DO STUFF
END;

MySQL says: ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

I checked the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html):

The TRIGGER privilege enables you to create and drop triggers. You must have this privilege for a table to create or drop triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER privilege.)

We are running "5.1.32-enterprise-gpl-advanced-log", so the TRIGGER privilege should be sufficient; however, the DBA granted me the TRIGGER privilege on mySchema.* and I can see it when I do SHOW GRANTS;, yet I still get this error about needing the "SUPER" privilege. We don't want to give all of the developers SUPER.

Any suggestions?

Answer 1

Here is the bug report for this. One option is to run with the --log-bin-trust-function-creators option turned on, which will allow you to create triggers without the SUPER privilege. This page explains what turning that option on means. Basically it has to do with whether or not MySQL thinks your triggers are deterministic (i.e. safe for replication). Your DBA may or may not be comfortable running in that mode. It's not ideal, but better than giving out SUPER...

UPDATE: The docs at the second link actually make it sound like you may be able to get around this by using row-based replication, or even mixed-mode replication. At least that would make it safe for replication. Whether or not you would still be required to have SUPER, I don't know, but it may be worth a try.