Bug #59531 error in trigger privilege
Submitted: 16 Jan 2011 8:42 Modified: 21 Jan 2011 6:50
Reporter: Greg Hazel Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Security: Privileges Severity:S1 (Critical)
Version:5.5.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: trigger

[16 Jan 2011 8:42] Greg Hazel
I am unable to create TRIGGERs as my db user due to privileges.

As I understand from http://bugs.mysql.com/bug.php?id=9412 this should not require SUPER privileges anymore.

I have granted the TRIGGER privilege as you can see here:

mysql> show grants;
| Grants for bob@%                                  |
| GRANT TRIGGER ON `my_db`.`addresses` TO 'bob'@'%' |

but I still get the same error.

How to repeat:
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)
[16 Jan 2011 10:00] Peter Laursen

"CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege ..."

Rom the error message I think that you havee binary logging enabled.

(not a MySQL person)
[16 Jan 2011 12:17] Valeriy Kravchuk
So, do you have binary logging enabled?
[16 Jan 2011 15:56] Greg Hazel
Yes, I have the binary log enabled. I'm not aware of any other way to have a reliable backup. (I use nightly snapshots with binlog to cover the time between the snapshot and the present).
[17 Jan 2011 9:40] Valeriy Kravchuk
For me it looks like this manual page, http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html, explains both the problem you have and possible workaround. Please, check.
[17 Jan 2011 19:48] Greg Hazel
So TRIGGERs are automatically marked as DETERMINISTIC. This function clearly modifies no data. If I switched to row based logging would I be able to use TRIGGERs?
[20 Jan 2011 12:29] Valeriy Kravchuk
I think you should just try to use workaround described in the manual. And yes, RBR may help as it is explained in http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html
[20 Jan 2011 12:30] Greg Hazel
The workaround says "you *might* want to use the less safe ...". Not very inspiring..
[21 Jan 2011 6:50] Valeriy Kravchuk
Actually all I described above are still just workarounds (even if they are documented). This is a duplicate of bug #39489, still not fixed.