[MySQL Case] ERROR 1418, mysqlerror1418

[MySQL Case] ERROR 1418, mysqlerror1418

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

SQL server 2005 database image connection problems, when the image database is set to the start image, and then the network connection error 1418 occurs, what is the problem?

The database image transmission function is disabled in the endpoint configuration. Master server version information: Microsoft SQL requires sp2 2005 without the SP2 patch, and Sp1 can be used only for your version

Life-saving MySQL Error: 2002

When mysql is started, ERROR 2002: appears. solution:
1. cd/usr/bin/
2. sudo./mysql_install_db
Start mysql.
# Mysql-u root-pERROR 2002 (HY000)
Solution 1.
1. # ps-A | grep mysql displays the following information: 1829? 00:00:00 mysqld_safe
1876? 00:00:31 mysqld
2. # kill-9 1829
3. # kill-9 1876
4. #/etc/init. d/mysql restart
5. # mysql-u root-p. His troubles have been solved. I have not solved it yet!
Continue to solution 2
Check/etc/rc. d/init. d/mysqld status to see if m y s q l has been started.
Check whether the permission is correct.
Bytes ------------------------------------------------------------------------------------
[Root @ localhost beinan] # chown-R mysql: mysql/var/lib/mysql
[Root @ localhost beinan] #/etc/init. d/mysqld start
Start MySQL: [OK]
[Root @ localhost lib] # mysqladmin-uroot password '123'
[Root @ localhost lib] # mysql-uroot-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 3 to server version: 4.1.11

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffe, and the problem persists.
Solution 3
After the problem is solved, it turns out that max_connections = 1000 has said too much, and then he changed it to 500 and said too much, but he had no choice but to delete the problem. Still not good
Solution 4: change all file permissions of/var/lib/mysql to mysql. mysql is not feasible solution 5
Abstract: mysql cannot be used. the main cause of sock connection to MySQL is that it cannot pass '/tmp/mysql. the sock is connected to the server, and the php standard configuration has been used '/tmp/mysql. sock, but some mysql installation methods will be mysql. put sock in/var/lib/mysql. sock or somewhere else, you can modify/etc/my. cnf file to modify it, open the file, you can see the following stuff:
[Mysqld]
Socket =/var/lib/mysql. sock
Just change it, but it will also... the remaining full text>