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 the CREATE ROUTINE orALTER ROUTINE privilege that is normally required. (Depending on the DEFINER value in the function definition,SUPER might be required regardless of whether binary logging is enabled. See Section 13.1.9, “CREATE PROCEDURE and CREATE 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 of DETERMINISTIC, NO SQL, or READS 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 global log_bin_trust_function_creatorssystem 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, the DEFINER 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”.

巴特西

MySql 创建函数 Error Code : 1418

最新文章

热门文章