MySQL parameter log_bin_trust_function_creators introduction,

MySQL parameter log_bin_trust_function_creators introduction,

 

MySQL has a log_bin_trust_function_creators parameter. The official documentation introduces and explains this parameter as follows:

 

 

Log_bin_trust_function_creators

Command-Line Format	-- Log-bin-trust-function-creators
System Variable	Name	Log_bin_trust_function_creators
Variable Scope	Global
Dynamic Variable	Yes
Permitted Values	Type	Boolean
Default	FALSE

 

 

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 haveSUPERPrivilege in addition toCREATE ROUTINEOrALTER ROUTINEPrivilege. 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. seeSection 23.7, "Binary Logging of Stored Programs".

 

 

This variable is enabled when binary logs are enabled. It controls whether the creator of the storage function can be trusted, and does not create a storage function that writes binary logs to cause unsafe events. If it is set to 0 (default value), you cannot CREATE or modify stored functions unless they have SUPER permissions except the create routine or alter routine privileges. If it is set to 0, the DETERMINISTIC feature or the reads SQL data or no SQL feature is also required to declare the function. If the variable is set to 1, MySQL does not impose these restrictions on the creation of storage functions. This variable also applies to trigger creation. See section 23.7"Binary Logging of Stored Programs".

 

Next, let's test. If the variable log_bin_trust_function_creators is OFF after binary logs are enabled, the ERROR 1418 (HY000): This function has none of DETERMINISTIC will be reported when the storage function is created or modified, 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:

 

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

mysql>  show variables like '%log_bin_trust_function_creators%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF   |

+---------------------------------+-------+

1 row in set (0.00 sec)

mysql> 

mysql> DELIMITER //

mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)

    -> RETURNS VARCHAR(12)

    -> BEGIN

    ->   RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);

    -> END

    -> //

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)

mysql> 

 

This error is also encountered when calling the storage function, as shown in the following test:

 

 

mysql> DELIMITER ;

mysql> set global log_bin_trust_function_creators=1;

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)

    -> RETURNS VARCHAR(12)

    -> BEGIN

    ->   RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ID,

    ->        GET_UPPER_NAME(ID)

    -> FROM TEST;

    -> //

+------+--------------------+

| ID   | GET_UPPER_NAME(ID) |

+------+--------------------+

|  100 | KERRY              |

|  101 | JIMMY              |

+------+--------------------+

2 rows in set (0.00 sec)

mysql> DELIMITER ;

mysql> set global log_bin_trust_function_creators=0;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ID,

    ->        GET_UPPER_NAME(ID)

    -> FROM TEST;

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)

mysql> 

 

 

 

So why does MySQL have such restrictions? Binary logs are used for master-slave replication, and storage functions may cause data inconsistency between the master and slave nodes. Therefore, when binary logs are enabled, the log_bin_trust_function_creators parameter takes effect, limiting the creation, modification, and calling of storage functions. How can this problem be solved? The official documentation is as follows. For details, refer to 23.7 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 previusly, 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 log_bin_trust_function_creators parameter to 1.

 

Mysql> set global log_bin_trust_function_creators = 1;

 

This dynamic setting method will expire after the service is restarted, so we must also set it in my. cnf and add log_bin_trust_function_creators = 1, which will take effect permanently.

 

 

 

2: Specify the function type. If binary logs are enabled, we must specify a parameter for our function. Among the following parameter types, only DETERMINISTIC, no SQL, and reads SQL DATA are supported. This is equivalent to explicitly notifying the MySQL server that the function will not modify data.

 

1 DETERMINISTIC

2 no SQL does not have SQL statements, and of course data will not be modified

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

4 modifies SQL DATA to modify DATA

5 contains SQL statements

 

mysql> show variables like 'log_bin_trust_function_creators';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF   |

+---------------------------------+-------+

1 row in set (0.00 sec)

mysql> DROP FUNCTION GET_UPPER_NAME;

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)

    -> RETURNS VARCHAR(12)

    -> READS SQL DATA

    -> BEGIN

    ->   RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);

    -> END

    -> //

Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> SELECT ID,

    ->        GET_UPPER_NAME(ID)

    -> FROM TEST;

+------+--------------------+

| ID   | GET_UPPER_NAME(ID) |

+------+--------------------+

|  100 | KERRY              |

|  101 | JIMMY              |

+------+--------------------+

2 rows in set (0.00 sec)