Tag Archives: NO SQL

[Solved] 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)

Prompt when running MySQL: [Err] 1418-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)
reason:
This is when we turn on bin-log, we must specify whether our function is
1 DETERMINISTIC uncertain
2 NO SQL has no SQl statement, and of course it will not modify the data
3 READS SQL DATA only reads the data, of course it will not modify the data
4 MODIFIES SQL DATA To modify the data
5 CONTAINS SQL contains SQL statements
Among them, in function, only DETERMINISTIC, NO SQL and READS SQL DATA are supported. If we enable bin-log, we must specify a parameter for our function.

Solution:
SQL code
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF  |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON  |

After adding the parameter in this way, if mysqld restarts, that parameter will disappear again, so remember to add in the my.cnf configuration file:
log_bin_trust_function_creators=1