1.1.1. ERROR 1418
【环境描述】
mysql5.0.67
【问题描述】
创建存储过程的时候遇到ERROR 1418报错。
# 创建函数的SQL语句
CREATE FUNCTION `xxx`( num01 int ) RETURNSint(11)
begin
declare mm int default 0;
if (xx) then
set mm = num01*num01*10;
elseif xxx then
set mm = 10;
else
case num01
when 30 then set mm = 111;
when 31 then set mm = 222;
end case;
end if;
return mm;
end
# 报错信息
# ERROR 1418 (HY000) at line xxxxx: Thisfunction has none of DETERMINISTIC, NO SQL, or READS SQL DATA in itsdeclaration and binary logging is enabled (you *might* want to use the lesssafe log_bin_trust_function_creators variable)
【解决方法】
关闭关闭binary log或者修改参数。
此处采用修改参数的方法：
set globallog_bin_trust_function_creators=1;
【报错原因】
By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NOSQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
ERROR1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READSSQL DATA in its declaration and binary logging is enabled
(you*might* want to use the less safe log_bin_trust_function_creators
variable)
If you set log_bin_trust_function_creators to 1, the requirement that functions be
deterministic or not modify data is dropped.
【参考资料】
Command-LineFormat --log-bin-trust-function-creators
Option-FileFormat log-bin-trust-function-creators
SystemVariable Name log_bin_trust_function_creators
VariableScope Global
DynamicVariable Yes
PermittedValues
Type boolean
Default FALSE
This variable applieswhen binary logging is enabled. It controls whether stored function creators canbe trusted not to create stored functions that will cause unsafe events to bewritten to the binary log. If set to 0 (the default), users are not permittedto create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQLdoes not enforce these restrictions on stored function creation. This variablealso applies to trigger creation. See Section19.7, “Binary Logging of Stored Programs”.