Mysql创建函数出错的解决

来源：互联网发布：linux can总线编辑：程序博客网时间：2024/04/29 10:46

在MySQL创建用户自定义函数时，报以下错误：

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)

这是因为有一个安全参数没有开启,log_bin_trust_function_creators 默认为0，是不允许function的同步的，开启这个参数，就可以创建成功了。

mysql> show variables like '%fun%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin_trust_function_creators | ON    |+---------------------------------+-------+1 row in set (0.00 sec)mysql> set global log_bin_trust_function_creators=1;                            Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%fun%';                                             +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin_trust_function_creators | ON    |+---------------------------------+-------+1 row in set (0.00 sec)

如果是在有master上开启了该参数，记得在slave端也要开启这个参数（salve需要stop后再重新start），否则在master上创建函数会导致replaction中断。

0 0