Use of log_bin_trust_function_creators in MySQL

Question

What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if I restart my MySQL service?

Answer 1

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions.

This has been explained well in 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 an error occurs:

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)

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.

Reference: mysql-documentation (read this as it explains with example.)

About your second question, if it's dynamic and will it change if you restart server:

• Yes it is a dynamic variable.

• The variable will change upon restart, if you do not update the config to reflect the change.

Reference: mysql-documentation-again