MYSQL creates a solution for the function error _mysql

When using the MySQL database, you sometimes encounter situations where MySQL functions cannot be created. Here is a solution to the MySQL function can not create problems, for your reference.

Case one:

Currently, there was an error executing the function to create MySQL in the project,

MySQL Create function error message is as follows:

Error code:1227. Access denied; Your need (at least one of) the SUPER privilege (s) for this operation

First check to see if the function creation function is turned on, and check that the SQL for the CREATE function is turned on as follows:

--See if you want to open the function creation show
variables like '%func% ';
--Open the function creation function
set global log_bin_trust_function_creators = 1;

After the execution of SQL, the discovery has been turned on, with the check that their SQL is wrong (because SQL is given to others, in other people's environment is not a problem, in their own environment is possible).

Suddenly found that the problem is really SQL, because he created the SQL has a specified user, so caused the problem, the following is his sql:

DROP FUNCTION IF EXISTS ' nextval ';
DELIMITER;;
CREATE definer= ' devop ' @ '% ' FUNCTION ' nextval ' (' Seq_name ' VARCHAR ()) RETURNS VARCHAR (m) CHARSET UTF8
BEGIN 
 DECLARE Seq_max BIGINT ();
 UPDATE sequenceconftable SET ' max ' = ' max ' + NEXT WHERE NAME = seq_name; 
 SELECT ' Max ' into Seq_max from sequenceconftable WHERE NAME = seq_name;
 return Seq_max; 
End
;;
DELIMITER;

Because of the create_function specification, it can be found that definer this parameter can specify the database user, but its own library is not this user, so cause the problem.

The problem has now been resolved.

-eof-

Case TWO:

When MySQL creates a user-defined function, the following error is reported:

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL, or reads SQL DATA in its declaration and binary Loggi NG is enabled (for your *might* want to use the less safe log_bin_trust_function_creators variable)

This is because a security parameter is not turned on, log_bin_trust_function_creators defaults to 0, is not allowed function synchronization, open this parameter, you can create a successful.

Mysql> Show variables like '%fun% '; 
+---------------------------------+-------+ 
| Variable_name     | Value | 
+---------------------------------+-------+ 
| log_bin_trust_function_creators | On | 
+---------------------------------+-------+ 
1 row in Set (0.00 sec) 
 
mysql> set global log_bin_trust_ Function_creators=1;        
Query OK, 0 rows Affected (0.00 sec) 
 
mysql> show variables like '%fun% ';            
+---------------------------------+-------+ 
| Variable_name     | Value | 
+---------------------------------+-------+ 
| log_bin_trust_function_creators | On | 
+---------------------------------+-------+ 

If you open this parameter on master, remember to open the parameter on the slave side (salve need to stop and start again), otherwise creating a function on Master will cause the replaction to break.

Case THREE:

Error code:1418

This function has none of the deterministic, NO SQL, or reads SQL DATA in the its declaration and binary logging are enabled (you * Might* want to use the less safe log_bin_trust_function_creators variable)  

Analysis:

According to the system prompts, the cause of this error may be a security settings configuration, the search manual log_bin_trust_function_creators parameter default 0, is not allowed function synchronization, generally we are in the configuration of Repliaction, Forget to focus on this parameter, so after master updates funtion, slave will report the error and then slave stoped.

Processing process:

Login to MySQL Database

> Set global log_bin_trust_function_creators = 1;
> Start slave;

Track MySQL boot log, slave normal operation, problem solving.