有两种方法可以解决这个问题:
在MySQL控制台中执行以下操作:
SET GLOBAL log_bin_trust_function_creators = 1;
将以下内容添加到mysql.ini配置文件:
log_bin_trust_function_creators = 1;
该设置放松了对非确定性函数的判断.非确定性函数是修改数据的函数(即具有update、insert或delete语句).有关更多信息,请参阅here.
请注意,如果未启用二进制日志(log)记录,则此设置不适用.
Binary Logging of Stored Programs
如果未启用二进制日志(log)记录,则log_bin_trust_function_creators会启用
log_bin_trust_function_creators
启用二进制日志(log)记录时,此变量适用.
最好的方法是更好地理解和使用存储函数的确定性声明.MySQL使用这些声明来优化复制,仔细 Select 它们以实现健康的复制是一件好事.
DETERMINISTIC
NOT DETERMINISTIC
我个人在MySQL 5.5中遇到了一个错误,如果没有声明,那么我总是至少声明一个"确定性"、"非确定性"、"无SQL"或"读取SQL数据",而不管我可能有其他声明.
READS SQL DATA
MODIFIES SQL DATA
NO SQL
CONTAINS SQL
请注意,有些MySQL函数不是确定性安全的,例如:NOW()、UUID()等,它们可能会在不同的机器上产生不同的结果,因此包含此类指令的用户函数必须声明为非确定性.
Assessment of the nature of a routine is based on the “honesty” of the
creator: MySQL does not check that a routine declared DETERMINISTIC is
free of statements that produce nondeterministic results. However,
misdeclaring a routine might affect results or affect performance.
Declaring a nondeterministic routine as DETERMINISTIC might lead to
unexpected results by causing the optimizer to make incorrect
execution plan choices. Declaring a deterministic routine as
NONDETERMINISTIC might diminish performance by causing available
optimizations not to be used.