22

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?

dreftymac
  • 105
  • 3
tesla747
  • 1,896
  • 10
  • 39
  • 74

1 Answers1

24

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

mysql_user
  • 1,942
  • 12
  • 9
  • 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
  • So if i change it when the db is running the change will be in effect immediately and besides that i will have to change it in config file for it to take effect if my mysql restarts. Right? – tesla747 Jul 28 '15 at 13:42
  • That's right @tesla747, Run on mysql prompt: set global log_bin_trust_function_creators=1;
    • This will set it globally in mysql.

    Add in my.cnf: log_bin_trust_function_creators=1

    • This will make the change permanent.
     – mysql_user Jul 28 '15 at 13:43
  • Any impacts on production db if I change set it globally when the prod is running? – tesla747 Jul 28 '15 at 13:59
  • No. (meeting min chars to post the comment) – mysql_user Jul 28 '15 at 14:56
  • Haha okay. Right now the parameter is showing the value as ON. Recently my server got restarted but the value is still ON. It should change right since I have not set the same in config file. – tesla747 Jul 28 '15 at 15:03
  • 1, TRUE, ON -- these are usually synonyms in setting variables. Ditto for 0, FALSE, OFF. – Rick James Jul 29 '15 at 02:32
  • @mysql_user should log_bin be enabled for log_bin_trust_function_creators to be in effect? – tesla747 Jul 29 '15 at 05:27
  • 1
    @tesla747 yep... it applies when binary logging is enabled. – mysql_user Jul 29 '15 at 05:32
  • @mysql_user mind helping me out in enabling binary logging? – tesla747 Jul 29 '15 at 05:41
  • @mysql_user http://chat.stackexchange.com/rooms/26025/discussion-between-mysql-user-and-kristofer-gisslen this would be a better place? – tesla747 Jul 29 '15 at 05:42
  • Do you know why CONTAINS SQL is not part of the allowed options? It seems more restrictive than READS SQL DATA so it should be "safer"?! – Xenos Dec 02 '19 at 21:36