Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When a CREATE FUNCTION or ALTER FUNCTION statement fails with 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), the server is writing a binary log and the account running the statement does not hold the privilege the manual requires for stored functions in that case. Confirm that binary logging is on and read the account's grants. Then create the function as an administrator account, grant the privilege on purpose, or relax the check for a short, controlled time.

What the error means

The binary log records statements that change data. It serves two purposes: replication, where replicas run the logged events, and data recovery, where events recorded after a backup are run again. On a replica the applier thread has full privileges unless replication privilege checks are set up. The manual explains the danger: a function can take a different path on the source and on the replica, so although a user must have the CREATE ROUTINE privilege to create a function, the user can write a function containing a dangerous statement that executes only on the replica.

To guard against that, the manual says that on servers with binary logging enabled, stored function creators "must have the SUPER privilege, in addition to the usual CREATE ROUTINE privilege", and that ALTER FUNCTION needs SUPER in addition to ALTER ROUTINE. The 8.4 page also says SET_ANY_DEFINER is required to create or alter a stored function, and that depending on the DEFINER value it might be required whether or not binary logging is enabled. The page states both requirements and does not reconcile them, so read the grants for both names.

Three scope points from the same page:

  • The conditions apply to stored functions. They "do not apply to stored procedures or Event Scheduler events".
  • Triggers are similar. CREATE TRIGGER gives similar errors when you lack the required privileges.
  • If binary logging is not enabled, log_bin_trust_function_creators does not apply.

Binary logging is on by default. The log_bin entry says it is "enabled by default, with the log_bin system variable set to ON", except when the data directory is initialised manually in the way that entry describes. Many servers therefore meet this error without anyone having chosen to enable the log.

A related message, ERROR 1418, says the function has none of DETERMINISTIC, NO SQL or READS SQL DATA in its declaration. Both errors name the same variable, but 1418 is about the function's declaration, not the account.

Likely causes

Cause What you see How to confirm
Account has CREATE ROUTINE but not SUPER, and binary logging is on ERROR 1419 on CREATE FUNCTION or ALTER FUNCTION SHOW VARIABLES LIKE 'log_bin'; returns ON, and SHOW GRANTS; for the account has no SUPER
A SQL file or application run by a limited account contains CREATE FUNCTION The load stops at the first function The failing statement and the account that ran it, from the client output or the application log
Trigger creation by a limited account A similar error on CREATE TRIGGER The statement that failed is CREATE TRIGGER
DEFINER names another account The page says a further privilege is needed whether or not binary logging is on; it gives no error text for that case The DEFINER clause in the statement, and SHOW GRANTS; for SET_ANY_DEFINER
Function not declared as the server requires ERROR 1418, not 1419 The text names DETERMINISTIC, NO SQL and READS SQL DATA

Prove the cause before changing anything

All of these are read-only. SHOW VARIABLES needs no privilege beyond connecting.

1. Is binary logging on?

SHOW VARIABLES LIKE 'log_bin';

ON means the binary log is available. OFF means it is not in use, and this error should not come from that server. Check that you are connected to the server you think you are.

2. Is the trust variable at its default?

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

The manual gives the default as OFF (0). A good result for this diagnosis is OFF: the check is enforced as designed.

3. What can the account do?

SHOW GRANTS;

With no FOR clause the statement lists the current account's grants, and the manual says it needs no special privilege for the current user. Look for CREATE ROUTINE or ALTER ROUTINE on the schema, and for SUPER on *.*. If SUPER is missing, that is the cause. Keep this output; the fix below compares against it.

4. What exactly failed?

Read the statement. Note whether it is a function, a trigger or a procedure, and whether it has a DEFINER clause. A failing CREATE PROCEDURE is not this error.

Fix

Choose in this order. The first option changes nothing on the server.

Option 1: create the function with an administrator account

Run the same CREATE FUNCTION statement with an account that already has the needed privileges, such as your administrative account. Use the same schema and the same DEFINER the application expects. No setting, grant or restart is involved. This is the least risky route and is the right one when only one or two functions are being installed.

Option 2: grant SUPER to the account

The manual says of SUPER: it "is a powerful and far-reaching privilege and should not be granted lightly", and that it is deprecated. It also lists dynamic privileges as a narrower alternative for some jobs, but its stored function text names SUPER for this error.

First save the account's current grants, using the account name from your own server:

SHOW GRANTS FOR 'appdev'@'192.0.2.10';

Risk: the next statement gives the account SUPER on the whole server. That account can then change global settings with SET GLOBAL and do other server-wide tasks the manual lists under SUPER. Grant it only to an account you trust, and only for as long as the work needs.

Grant the privilege:

GRANT SUPER ON *.* TO 'appdev'@'192.0.2.10';

The privileges page lists SUPER among the static privileges, and the Privilege Changes page says static global privileges take effect "only in sessions for subsequent connections", so the account must disconnect and reconnect before the function statement is retried. The Privilege Changes page says that for account-management statements such as GRANT and REVOKE, "the server notices these changes and loads the grant tables into memory again immediately".

To undo it: if the saved output did not already list SUPER, run the revoke below, then run SHOW GRANTS FOR 'appdev'@'192.0.2.10'; and compare with the saved output.

REVOKE SUPER ON *.* FROM 'appdev'@'192.0.2.10';

If the saved output already listed SUPER, do not run the revoke; nothing was changed for that privilege.

Option 3: set log_bin_trust_function_creators

The variable is described as relaxing both conditions: that you must have SUPER, and that a function must be declared deterministic or to not modify data. The entry marks it as deprecated, global and dynamic. Changing it needs SYSTEM_VARIABLES_ADMIN (or the deprecated SUPER).

First print and keep the current value:

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

Risk: the next statement removes the protection for every account on the server, not only the one in front of you. Until it is set back, anyone with CREATE ROUTINE can create functions that may behave differently on the source and on a replica, and that may be unsafe for recovery from the binary log. The error message itself calls the variable "less safe".

Set it for the session of work:

SET GLOBAL log_bin_trust_function_creators = 1;

Run the CREATE FUNCTION statement, then undo the setting straight away. SET GLOBAL affects only the running server and does not carry over to the next start. Setting the variable in an option file or with SET PERSIST would make it permanent, which this procedure does not do.

To back out: if the saved value was OFF, the variable's default, run:

SET GLOBAL log_bin_trust_function_creators = 0;
SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

The second line should print OFF, the value you saved. If the saved value was ON, the setting was already relaxed before you started and there is nothing to undo.

Example (illustrative): On db01, a developer account appdev@192.0.2.10 loads a file that creates the function order_total in the shop schema. The load stops with ERROR 1419. SHOW VARIABLES LIKE 'log_bin'; returns ON. SHOW GRANTS; lists CREATE ROUTINE on shop.* and no SUPER. The database administrator connects with the administrative account and runs the same file, and the function is created. No grant or variable was changed. Had the function been created by a limited account again later, the same error would have returned.

Confirm the fix

  1. The CREATE FUNCTION statement returns without an error.
  2. SHOW FUNCTION STATUS WHERE Db = 'shop'; lists the function. The statement takes an optional LIKE or WHERE clause.
  3. If you used Option 3, SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators'; shows the saved value again.
  4. If you used Option 2 and revoked afterwards, SHOW GRANTS FOR the account matches what you saved.

What to watch afterwards

  • Check replicas if the server has them. The reason for the rule is that statements replay on the replica, so confirm the function exists and behaves the same there.
  • After a restore from a backup that includes functions, repeat the check on the restored server. The error is about the account running the load.
  • If the variable was set to 1 for a task, look at it again the next day. A global setting left on is easy to forget.
  • Keep the account used for application deployment separate from the application's runtime account.

Risks and limits

  • These commands have not been run or lab-tested.
  • The 8.4 page names SUPER in the error text and SET_ANY_DEFINER in its conditions summary. It does not say how the two combine for every case, so test on a copy of the schema.
  • log_bin_trust_function_creators is deprecated in 8.4, so plan for its removal and do not build a deployment process that depends on it.
  • The manual does not say how other MySQL versions or MySQL-compatible forks word these checks. A hosted database service may restrict SUPER and SET GLOBAL; read that service's own page.
  • The manual says replication privilege checks are recommended regardless of the choice you make about function creators.

Sources