Bug #34729 CREATE TRIGGER does not look at log_bin_trust_function_creators
Submitted: 21 Feb 2008 14:55 Modified: 22 Feb 2008 8:51
Reporter: Gertjan Oude Lohuis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.45 OS:Linux
Assigned to: Paul DuBois CPU Architecture:Any

[21 Feb 2008 14:55] Gertjan Oude Lohuis
Description:
The documentation of MySQL states that the log_bin_trust_function_creators also applies to triggers (http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html). I quote: "
* To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:

      mysql> SET GLOBAL log_bin_trust_function_creators = 1;

You can also set this variable by using the --log-bin-trust-function-creators=1 option when starting the server.
If binary logging is not enabled, log_bin_trust_function_creators does not apply and SUPER is not required for routine creation.

Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: CREATE TRIGGER does not have an optional DETERMINISTIC characteristic, so triggers are assumed to be always deterministic. However, this assumption might in some cases be invalid. For example, the UUID() function is non-deterministic (and does not replicate). You should be careful about using such functions in triggers.

Triggers can update tables (as of MySQL 5.0.10), so error messages similar to those for stored functions occur with CREATE TRIGGER if you do not have the SUPER privilege and log_bin_trust_function_creators is 0.
"

Binary logging is enabled at my servers, and my users do not have the SUPER privilege.

However, enabling log_bin_trust_function_creators does not seem to work here.

How to repeat:
As superuser:

> create database test;
> grant all on test.* to gertjan identified by 'banaan';
> set global log_bin_trust_function_creators = 1;

As 'gertjan':
> use test;
> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

Results in:
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

(The example query has been copied from http://dev.mysql.com/doc/refman/5.0/en/triggers.html.

Suggested fix:
1. Point out my mistake :-), or
2. Fix documentation.
[21 Feb 2008 21:25] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

log_bin_trust_function_creators does not apply to triggers.  Revising the relevant passage to say this:

Triggers are similar to stored functions, so the preceding remarks
regarding functions also apply to triggers with the following
exceptions: log_bin_trust_function_creators does not apply to
triggers and does not affect the privileges required for CREATE
TRIGGER. Also, CREATE TRIGGER does not have an optional DETERMINISTIC
characteristic, so triggers are assumed to be always deterministic.
However, this assumption might in some cases be invalid. For example,
the UUID() function is non-deterministic (and does not replicate).
You should be careful about using such functions in triggers.
[22 Feb 2008 8:51] Gertjan Oude Lohuis
Thanks, although I'd rather seen you pointing out my mistake, because I'd like to be able to offer my users triggers :-).