MySQL Bugs: #34729: CREATE TRIGGER does not look at log_bin_trust_function

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
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

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.

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.

Thanks, although I'd rather seen you pointing out my mistake, because I'd like to be able
to offer my users triggers :-).