MySql 创建函数 Error Code : 1418
查看日志信息:show variables like 'log_%';显示'log_bin'、'log_bin_trust_function_creators'等状态
解决方法:
- 关闭binary logging
- 在创建函数 begin 之前加上 DETERMINISTIC READS SQL DATA
SET GLOBAL log_bin_trust_function_creators = 1;
参考 http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html
- To create or alter a stored function, you must have the
SUPER
privilege, in addition to theCREATE ROUTINE
orALTER ROUTINE
privilege that is normally required. (Depending on theDEFINER
value in the function definition,SUPER
might be required regardless of whether binary logging is enabled. See Section 13.1.9, “CREATE PROCEDURE
andCREATE FUNCTION
Syntax”.)
-
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a
CREATE FUNCTION
statement to be accepted, at least one ofDETERMINISTIC
,NO SQL
, orREADS SQL DATA
must be specified explicitly. Otherwise an error occurs: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)
This function is deterministic (and does not modify data), so it is safe:
CREATE FUNCTION f1(i INT) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN RETURN i; END;
This function uses
UUID()
, which is not deterministic, so the function also is not deterministic and is not safe:CREATE FUNCTION f2() RETURNS CHAR(36) CHARACTER SET utf8 BEGIN RETURN UUID(); END;
This function modifies data, so it may not be safe:
CREATE FUNCTION f3(p_id INT) RETURNS INT BEGIN UPDATE t SET modtime = NOW() WHERE id = p_id; RETURN ROW_COUNT(); END;
Assessment of the nature of a function is based on the “honesty” of the creator: MySQL does not check that a function declared
DETERMINISTIC
is free of statements that produce nondeterministic results. -
To relax the preceding conditions on function creation (that you must have the
SUPER
privilege and that a function must be declared deterministic or to not modify data), set the globallog_bin_trust_function_creators
system variable to 1. By default, this variable has a value of 0, but you can change it like this:mysql>
SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1
option when starting the server.If binary logging is not enabled,
log_bin_trust_function_creators
does not apply.SUPER
is not required for function creation unless, as described previously, theDEFINER
value in the function definition requires it. -
For information about built-in functions that may be unsafe for replication (and thus cause stored functions that use them to be unsafe as well), see Section 16.4.1, “Replication Features and Issues”.