MySQL log_bin_trust_function_creators error

Recently I got these 2 MySQL errors.

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

And

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)

For the first one, as it’s mentioned in MySQL documentation.

  • When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
  • By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise the first error occurs.

Now For the second one, as it’s mentioned in MySQL documentation.

The CREATE FUNCTION and INSERT statements are written to the binary log, so the replica executes them. Because the replica's applier thread has full privileges, it executes the dangerous statement. Thus, the function invocation has different effects on the source and replica and is not replication-safe.

To guard against this danger for servers that have binary logging enabled, stored function creators must have the SUPER privilege, in addition to the usual CREATE ROUTINE privilege that is required. Similarly, to use ALTER FUNCTION, you must have the SUPER privilege in addition to the ALTER ROUTINE privilege. Without the SUPER privilege, the second error occurs:

For fixing these two errors, I run below command in MySQL console.

SET GLOBAL log_bin_trust_function_creators = 1;

But there is a problem. If I restart the MySQL server, this settings will be gone.
To fix this, I have added “log_bin_trust_function_creators = 1” in MySQL’s my.cnf file.

Reference: MySQL Documentation


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.