13

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?

10

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

  • does it take a boolean value? Right it is is set to TRUE for me.. does that mean it is '1'? – tesla747 Jul 28 '15 at 12:03
  • mysql> select if(1 is true,'TRUE','FALSE')\G *************************** 1. row *************************** if(1 is true,'TRUE','FALSE'): TRUE – mysql_user Jul 28 '15 at 12:08
  • Ok I understand that. But is it necessary to change it to '1' particularly for that parameter? Now since I set it as TRUE, when I restart the mysql service will it change again – tesla747 Jul 28 '15 at 12:11
  • 1
    That's right! So to make it persistent you should put the same configuration value in your my.cnf file as well. – mysql_user Jul 28 '15 at 12:13
  • 1
    @tesla747 yep... it applies when binary logging is enabled. – mysql_user Jul 29 '15 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.