Home>

Case number one:

Currently in the project,Error executing the function that creates mysql,

The mysql create function error message is as follows:

error code:1227. access denied;you need (at least one of) the super privilege (s) for this operation

First check if the function of creating a function is enabled.The SQL to check whether the creation function is enabled is as follows:

-Check whether the function of creating functions is enabled
show variables like "%func%";
-Enable function creation
set global log_bin_trust_function_creators=1;

After executing SQL, it was found to be turned on.Check if your own sql is wrong (because sql is given by others,No problem in others' environment,It is possible in your own environment).

Suddenly found that it is indeed a problem with sql,Since the sql he created has a specified user,So it causes problems,Here is his sql:

drop function if exists `nextval`;
delimiter ;;
create definer=`devop` @`%`function` nextval` (`seq_name` varchar (50)) returns varchar (20) charset utf8
begin
 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;
</pre>
</div>
<p>
Due to the create_function specification, it can be found that the definer parameter can specify the database user.
But his own library is not this user,So cause problems.
</p>
<p>
The problem has been resolved.
</p>
<p>
-eof-
</p>
<p>
Case two:
</p>
<p>
When creating a user-defined function in mysql,Reported the following error:
</p>
<div>
<pre>
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 there is a security parameter that is not turned on.log_bin_trust_function_creators is 0 by default, and synchronization of functions is not allowed.Turn on this parameter,You can create it 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 also enable this parameter on the slave side (salve needs to stop and then restart), otherwise creating a function on the master will cause the replaction to be interrupted.

Case three:

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:

When prompted,This error may be caused by a security configuration,Check the manual log_bin_trust_function_creators parameter default 0, function synchronization is not allowed,Generally, when we configure reaction, we forget to pay attention to this parameter.In this way, after the master updates the funtion, the slave will report an error.Then the slave stopped.

Process:

Login mysql database

>set global log_bin_trust_function_creators=1;
>start slave;

Track mysql startup log,The slave is running normally,problem solved.

  • Previous Aspnet collection of methods to generate verification code (1)
  • Next Js simple method for dynamically increasing the number of input boxes by clicking a button in a form