MySQL教程 关于导入SQL出现1418错误

1、MySQL 创建函数出现1418错误
[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)
此错误也经常发生在使用SQL文件创建数据库的时候。
原来是因为在主从复制的两台 MySQL 服务器中开启了二进制日志选项 log-bin,slave 会从 master 复制数据,一些操作比如 function 所得的结果在 master 和 slave上可能不同,所以存 在潜在的安全隐患。因此,在默认情况下会阻止 function 的创建。也就是说,某些数据库开启 了安全选项,导致 function 不能任意创建。尤其是 mysql8 版本以上,默认都是不允许任意创 建 function 的。
有两种办法来解决这一问题:
1. 将 log_bin_trust_function_creators 参数设置为 ON,这样一来开启了 log-bin 的 MySQL Server 便可以随意创建 function。这里存在潜在的数据安全问题,除非明确知道创建的 function 在 master 和 slave 上的行为完全一致。设置该参数可以用动态的方式或者指定该参 数来启动数据库服务器或者修改配置文件后重启服务器。需要注意的是,动态设置的方式会在服 务器重启后失效。
使用 show variables like 'log_bin_trust_function_creators'; 可查看当前参数值,如果显示 值为 OFF 表示禁用。
使用 set global log_bin_trust_function_creators=1; 设置参数值为 ON 即可。
另外如果是在 master 上创建函数,想通过主从复制的方式将函数复制到 slave 上则也需在开启 了 log-bin 的 slave 中设置上述变量的值为 ON(变量的设置不会从master 复制到 slave上, 这点需要注意),否则主从复制会报错。
2. 明确指明函数的类型:
DETERMINISTIC 不确定的· NO SQL 没有SQl语句,当然也不会修改数据·
READS SQL DATA 只是读取数据,当然也不会修改数据·
比如: CREATE DEFINER='username'@'%' READS SQL DATA FUNCTION 函数名(参数) RETURNS 返回类 型… 这样一来相当于明确的告知 MySQL 服务器这个函数不会修改数据,因此可以在开启了 logbin 的服务器上安全的创建并被复制到开启了 log-bin 的 slave 上。
注意:如果是使用较低版本(小于 8.0)的 mysql 数据库备份成 sql 文件格式,然后通过运行 sql 的方式来还原到高版本(大于等于 8.0)数据库中,也会出现此问题。此时,使用第 1 种方 式解决即可。
本文作者:刘广法,转载注明出处。