"MySQL case" ERROR 1418

1.1.1. ERROR 1418

"Environment description"

mysql5.0.67

"Problem description"

Error 1418 error encountered while creating stored procedure.

# SQL statement to create a function

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 the then set mm = 111;

When the set mm = 222;

End case;

End If;

return mm;

End

# error message

# ERROR 1418 (HY000) at line Xxxxx:thisfunction have none of deterministic, NO SQL, or READS SQL DATA in Itsdeclaration an D binary logging is enabled (you *might* want to use the Lesssafe log_bin_trust_function_creators variable)

"Workaround"

Turn off binary log or modify parameters.

Here's how to modify the parameters:

Set Globallog_bin_trust_function_creators=1;

"Cause of error"

by default, for a CREATE FUNCTION statement to is accepted, at least one of Deterministic , NOSQL , or READS SQL DATA must be specified explicitly. Otherwise An error occurs:

ERROR1418 (HY000): This function has a none of deterministic, NO SQL,

or Readssql DATA in it 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 this functions be

Deterministic or not modify data is dropped.

Resources

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 would cause unsafe events to Bewritten to the binary log. If set to 0 (the default), users is not permittedto create or alter stored functions unless they has theSUPERPrivilege In addition to theCREATE ROUTINEorALTER ROUTINEprivilege. A setting of0 also enforces the restriction that a function must is declared with theDeterministiccharacteristic, or with theREADS SQL DATAorNO SQLcharacteristic. If the variable is set to 1, Mysqldoes does enforce these restrictions on stored function creation. This variablealso applies to trigger creation. SeeSection19.7, "Binary Logging of Stored Programs".