Shulou(Shulou.com)06/01 Report--

Editor to share with you to solve the problem of MYSQL creation function errors, I hope you will learn a lot after reading this article, let's discuss it together!

Example 1:

Currently, there is an error executing the function that creates mysql in the project.

The error message of the mysql creation function is as follows:

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

First, check whether the creation function is enabled, and the SQL to check whether the creation function is enabled is as follows:

-- check whether the function to create a function is enabled show variables like'% func%';-- enable the function to create set global log_bin_trust_function_creators = 1

After executing the SQL, I found that it has been turned on, so please check if your SQL is miswritten (because the SQL is given by others, it is not a problem in other people's environment, it is possible in your own environment).

Suddenly found that there is indeed a problem with SQL, because the SQL he created has a specified user, so there is a problem. Here is his SQL:

DROP FUNCTION IF EXISTS `nextval`; DELIMITER; CREATE DEFINER= `devop` @ `% `FUNCTION `nextval` (`name` VARCHAR (50)) RETURNS varchar (20) CHARSET utf8BEGIN DECLARE seq_max BIGINT (20); 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

Due to the CREATE_FUNCTION specification, you can find that the parameter DEFINER can specify a database user, but your own library is not that user, which leads to a problem.

At present, the problem has been solved.

-EOF-

Case 2:

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

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you * might* want to use the less safe log_bin_trust_function_creators variable)

This is because a security parameter is not enabled. Log_bin_trust_function_creators defaults to 0, which disallows synchronization of function. If this parameter is enabled, it can be created successfully.

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 | +- -+-+ 1 row in set (0.00 sec)

If this parameter is enabled on a master, remember to enable this parameter on the server (salve needs stop before re-start), otherwise creating a function on the master will cause replaction interruption.

Case 3:

Error Code: 1418

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you * might* want to use the less safe log_bin_trust_function_creators variable) (0 ms taken)

Analysis:

According to the system prompt, the cause of the error may be a security configuration, look up the manual log_bin_trust_function_creators parameter default 0, function synchronization is not allowed, generally we configure repliaction, we forget to pay attention to this parameter, so in master update funtion, slave will report an error, and then slave stoped.

Process:

Log in to mysql database

> set global log_bin_trust_function_creators = 1; > start slave

Track the startup log of mysql, slave is running normally, and the problem is solved.

After reading this article, I believe you have a certain understanding of solving the problem of errors in the creation function of MYSQL. Want to know more about it. Welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.