[MySQL Case] ERROR1418

Source: Internet
Author: User
1.1.1.ERROR1418 [environment description] mysql5.0.67 [Problem description] an error occurs when you create a stored procedure. # CREATEFUNCTION 'xxx' (num01int) RETURNSint (11) begindeclaremmintdefault0; if (xx) thensetmmnum01 * num01 * 10; elsei

1.1.1.ERROR 1418 [environment description] mysql5.0.67 [Problem description] ERROR 1418 is reported when a stored procedure is created. # Create function 'xxx' (num01 int) RETURNSint (11) begin declare mm int default 0; if (xx) then set mm = num01 * num01 * 10; elsei

1.1.1. ERROR 1418

[Environment description]

Mysql5.0.67

[Problem description]

When creating a stored procedure, an ERROR 1418 is reported.

# Create an SQL statement for 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 30 then set mm = 111;

When 31 then set mm = 222;

End case;

End if;

Return mm;

End

# Error message

# 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)

[Solution]

Disable binary log or modify parameters.

Here, the parameter modification method is used:

Set globallog_bin_trust_function_creators = 1;

[Error cause]

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.

[References]

Command-LineFormat-- Log-bin-trust-function-creators

Option-FileFormatLog-bin-trust-function-creators

SystemVariable NameLog_bin_trust_function_creators

VariableScopeGlobal

DynamicVariableYes

PermittedValues

TypeBoolean

DefaultFALSE

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. seep19.7, "Binary Logging of Stored Programs ".

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.