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

MySQL has a parameter log_bin_trust_function_creators. The official document describes and explains this parameter as follows:

This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 23.7, "Binary Logging of Stored Programs".

Briefly, when binary logging is enabled, this variable is enabled. It controls whether the creator of the stored function can be trusted and does not create a stored function that writes to the binary log that causes unsafe events. If set to 0 (the default), users must not create or modify stored functions unless they have SUPER privileges other than CREATE ROUTINE or ALTER ROUTINE privileges. A setting of 0 also forces the use of the DETERMINISTIC attribute or the READS SQL DATA or NO SQL attribute to declare the limits of the function. If the variable is set to 1m MySQL, these restrictions will not be imposed on the creation of stored functions. This variable also applies to the creation of triggers. See section 23.7, "Binary Logging of Stored Programs".

Let's test that when the binary log is enabled, if the variable log_bin_trust_function_creators is OFF, creating or modifying the storage function will report an error such as "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)", as shown below:

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

So why does MySQL have such restrictions? Because one of the important functions of binary logs is for master-slave replication, and the storage function may lead to inconsistent master-slave data. Therefore, when the binary log is enabled, the parameter log_bin_trust_function_creators will take effect, restricting the creation, modification and call of the storage function. So how to solve this problem at this time? The official documents are as follows. For more information, please refer to Binary Logging of Stored Programs

If you do not want to require function creators to have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system 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 is not enabled, log_bin_trust_function_creators does not apply. SUPER is not 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 is not repeatable. This can have two undesirable effects:

It will make a slave different from the master.

Restored data will be different from the original data.

To deal with these problems, MySQL enforces the following requirement: On a master server, creation and alteration of a function is refused unless you declare the function to be deterministic or to not modify data. Two sets of function characteristics apply here:

The DETERMINISTIC and NOT DETERMINISTIC characteristics indicate whether a function always produces the same result for given inputs. The default is NOT DETERMINISTIC if neither characteristic is given. To declare that 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 function reads or writes data. Either NO SQL or READS SQL DATA indicates that a function does not change data, but you must specify one of these explicitly because the default is CONTAINS SQL if no characteristic is given.

1: if the database does not use master-slave replication, 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 is restarted, so we must also set it in my.cnf, plus log_bin_trust_function_creators=1, so it will take effect permanently.

2: clearly specify the type of function, and if we open binary logging, then we must specify a parameter for our function. Of the following parameter types, only DETERMINISTIC, NO SQL and READS SQL DATA are supported. This is tantamount to explicitly telling the MySQL server that this function will not modify the data.

1 DETERMINISTIC uncertain

2 NO SQL does not have a SQl statement and certainly does not modify the data

3 READS SQL DATA only reads the data and certainly does not modify the data

4 MODIFIES SQL DATA wants to modify the data

5 CONTAINS SQL contains SQL statements

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.