log_bin_trust_function_creators
在从其它环境导入函数的时候发现报错,报错信息如下:
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)
本章将向大家介绍改问题的原因以及解决方法:
当我们开启binlog后,log_bin_trust_function_creators默认值为OFF:
1 mysql> show variables like 'log_bin'; 2 +---------------+-------+ 3 | Variable_name | Value | 4 +---------------+-------+ 5 | log_bin | ON | 6 +---------------+-------+ 7 1 row in set (0.01 sec) 8 9 mysql> show variables like 'log_bin_t%'; 10 +---------------------------------+-------+ 11 | Variable_name | Value | 12 +---------------------------------+-------+ 13 | log_bin_trust_function_creators | OFF | 14 +---------------------------------+-------+ 15 1 row in set (0.00 sec)
从官方文档上看当开启binlog并且log_bin_trust_function_creators参数为OFF时,如果创建函数,则必须要要SUPER权限,否则创建的函数必须指定DETERMINISTIC、
NO SQL、
READS SQL DATA其中一个属性,否则将返回我们看到的这个错误,因此当我们需要用普通用户创建需要影响binlog的函数时,必须将log_bin_trust_function_creators参数修改为ON。