[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

一、创建函数的时候报错信息

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

二、原因分析

原因是开启了log-bin日志，创建函数时，函数中没有包含DETERMINISTIC, NOSQL和 READS SQL DATA声明，即没有涉及修改数据

三、问题解决

查看参数

show variables like 'log_bin_trust_function_creators'

修改参数

(root@localhost) [db1]> set global log_bin_trust_function_creators=1 ；
Query OK, 0 rows affected (0.00 sec)

查看参数

(root@localhost) [db1]> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)

重新创建函数

(root@localhost) [db1]> create function func_test1(total int)
    -> returns int
    -> begin
    ->   declare i int;
    ->   declare res int;
    ->   set i=1;
    ->   set res=1;
    ->   if total <= 0 then
    ->      set total=1;
    ->   end if;
    ->   while i<=total DO
    ->       set res=res*i;
    ->       set i=i+1;
    ->   end while;
    ->   return res;
    -> end; //
Query OK, 0 rows affected (0.01 sec)

报错解决，这是临时设置参数，数据库重启失效。