MySQL Bugs: #59531: error in trigger privilege

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:	None
Category:	MySQL Server: Security: Privileges	Severity:	S1 (Critical)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	trigger

Description:
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:
mysql> CREATE TRIGGER `something` BEFORE DELETE ON `addresses` FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Example';
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)

http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

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

Peter
(not a MySQL person)

So, do you have binary logging enabled?

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

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.

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?

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

The workaround says "you *might* want to use the less safe ...". Not very inspiring..

Actually all I described above are still just workarounds (even if they are documented). This is a duplicate of bug #39489, still not fixed.