Reason analysis and solution of MySQL ERR 1418

MySQL has a parameter log_bin_trust_function_creators, the official document of this parameter is introduced, the explanation is as follows:

This variable applies if binary logging is enabled. It controls whether stored function creators can be trusted don't to create stored functions that would cause unsafe events t O is written to the binary log. If set to 0 (the default), users is not permitted to create or alter stored functions unless they has the SUPER Privileg E In addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must is declared with the deterministic characteristic, or WI th the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does isn't enforce these restrictions on stored function creation. This variable also applies to trigger creation. See section 23.7, "Binary Logging of Stored Programs".

Briefly, this variable is enabled when the binary log is enabled. It controls whether the store function creator can be trusted, and does not create a storage function that writes to the binary log causing unsafe events. If set to 0 (the default), users must not create or modify storage functions unless they have super privileges other than create routine or alter routine privileges. Setting to 0 also enforces restrictions on the use of the deterministic attribute or the reads SQL data or no SQL attribute to declare functions. If the variable is set to 1,mysql, these restrictions are not enforced on the Create storage function. This variable also applies to the creation of triggers. See section 23.7, "Binary Logging of Stored Programs".

Below we test, when the binary log is turned on, if the variable log_bin_trust_function_creators is off, then the creation or modification of the storage function will be reported "ERROR 1418 (HY000): This function has none of deterministic, NO SQL, or READS SQL DATA in its declaration and binary logging are enabled (you might want to u Se the less safe log_bin_trust_function_creators variable) "Such an error as follows:

You will also encounter this error when calling a stored function, as shown in the following test:

So why does MySQL have such a limitation? Because one of the important functions of the binary log is for master-slave replication, storage functions can cause master-slave data inconsistencies. So when the binary log is turned on, the parameter log_bin_trust_function_creators will take effect, restricting the creation, modification, and invocation of the stored function. So how do we solve this problem at this point? The official documentation is described below, for specific reference 23.7 Binary Logging of Stored Programs

If you don't want to require function creators to has the SUPER privilege (for example, if all users with the CREATE ROU TINE privilege on your system is experienced application developers), set the global log_bin_trust_function_creators Syst EM variable to 1. You can also set this variable by using the--log-bin-trust-function-creators=1 option when starting the server. If binary logging isn't enabled, Log_bin_trust_function_creators does not apply. SUPER isn't required for function creation unless, as described previously, the Definer value in the function definition Requires it.
If a function that performs updates is nondeterministic, it's not repeatable. This can has both undesirable effects:

· It'll make a slave different from the master.

· Restored data is different from the original data.

To deal with these problems, MySQL enforces the following requirement:on a master server, creation and alteration of a fu Nction is refused unless you declare the function to being deterministic or to not modify data. Sets of function characteristics apply here:

· The deterministic and not deterministic characteristics indicate whether a function all produces the same result for GI VEN inputs. The default is isn't deterministic if neither characteristic is given. To declare this a function is deterministic, you must specify deterministic explicitly.

· The CONTAINS sql, NO sql, READS SQL data, and modifies SQL data characteristics provide information about whether the Func tion reads or writes data. Either NO SQL or READS SQL DATA indicates that a function does isn't change DATA, but you must specify one of these explicit ly because the default is CONTAINS SQL if no characteristic is given.

1: If the database is not using master-slave replication, then you can set the parameter log_bin_trust_function_creators to 1.

mysql> set global Log_bin_trust_function_creators=1;

This dynamic setting will expire after the service restarts, so we must also set it in My.cnf, plus log_bin_trust_function_creators=1, so it will be in effect forever.

2: Explicitly indicate the type of function, and if we open the binary log, then we must specify a parameter for our function. Among the following types of parameters, only deterministic, NO SQL and READS SQL DATA are supported. This is equivalent to explicitly informing the MySQL server that this function does not modify the data.

1 Deterministic indeterminate

2 No SQL does not have SQL statements and of course does not modify the data

3 READS SQL Data just reads and of course does not modify the data

4 modifies SQL data to modify

5 CONTAINS SQL contains SQL statements

Reason analysis and solution of MySQL ERR 1418