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 TRIGGERgives similar errors when you lack the required privileges. - If binary logging is not enabled,
log_bin_trust_function_creatorsdoes 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
- The
CREATE FUNCTIONstatement returns without an error. SHOW FUNCTION STATUS WHERE Db = 'shop';lists the function. The statement takes an optionalLIKEorWHEREclause.- If you used Option 3,
SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';shows the saved value again. - If you used Option 2 and revoked afterwards,
SHOW GRANTS FORthe 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.
Read next
Related articles you may want next.
- Backup & RecoveryMySQL Point-in-Time Recovery: Match the Backup to Binary Log CoordinatesPlan a consistent MySQL recovery set using recorded coordinates or GTID context and a deliberate transaction stop point.Read article
- TroubleshootingMySQL ERROR 1045 (28000) "Access denied for user": which account the server matchedMySQL ERROR 1045 (28000) Access denied for user? Read the user, host and 'using password' text, find the account the server matched, then fix it.Read article
- PerformanceMySQL Replica Lag: Separate Receiver Delay from Apply DelayLocate the receiver or applier bottleneck and state what replica freshness is actually evidenced.Read article
Risks and limits
- These commands have not been run or lab-tested.
- The 8.4 page names
SUPERin the error text andSET_ANY_DEFINERin 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_creatorsis 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
SUPERandSET 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
- MySQL 8.4 Reference Manual: Stored Program Binary Logging — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: Replication and Binary Logging Options and Variables (log_bin, log_bin_trust_function_creators, binlog_format) — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: System Variable Privileges — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: Privileges Provided by MySQL (SUPER) — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: Privilege Changes — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: SET Syntax for Variable Assignment — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: Persisted System Variables — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: SHOW GRANTS Statement — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: SHOW VARIABLES Statement — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: SHOW FUNCTION STATUS Statement — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: REVOKE Statement — Oracle, no date shown, accessed 22 November 2026
This article was originally published on .