【MySQL案例】ERROR 1418

標籤：mysql   faq   

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 FUNCTIONstatement to be accepted, at least one of DETERMINISTIC, NOSQL, or READS SQL DATAmust 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_creatorsto 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 theSUPER privilege in addition to theCREATE ROUTINE orALTER ROUTINE privilege. A setting of0 also enforces the restriction that a function must be declared with theDETERMINISTIC characteristic, or with theREADS SQL DATA orNO SQL characteristic. If the variable is set to 1, MySQLdoes not enforce these restrictions on stored function creation. This variablealso applies to trigger creation. SeeSection19.7, “Binary Logging of Stored Programs”.