Create a function in the master database of the MySQL master-slave replication machine, reporting the following error:
Error code:1418. This function has none of the deterministic, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled c1> (you *might* want for use of the less safe log_bin_trust_function_creators variable)
It turns out that the binary log option is turned on for two MySQL servers in the master-slave replication Log-bin,slave will replicate data from master, and some operations, such as function results, may be different on master and slave, so there is a potential security risk. Therefore, the creation of the function is blocked by default.
There are two ways to solve this problem
1. Set the log_bin_trust_function_creators parameter to on so that Log-bin MySQL server can create the function as you wish. There is a potential data security problem, unless you explicitly know that the created function is exactly the same as the behavior on master and slave.
Set this parameter to start the database server either dynamically or by specifying the parameter, or to restart the server after modifying the configuration file. It is important to note that the dynamic setting will expire after the server restarts.
Mysql> Show variables like ' log_bin_trust_function_creators ';
mysql> set global Log_bin_trust_function_creators=1;
In addition, if you create a function on master, and you want to copy the function to slave by the master-slave copy, you also need to set the value of the above variable in the Log-bin slave to ON (the variable's settings will not be copied from master to slave, which requires attention), Otherwise, the master-slave copy will error.
2. Clearly indicate the type of function
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
Example: CREATE definer= ' username ' @ '% ' READS SQL DATA FUNCTION ' Fn_getitemclock ' (i_itemid bigint,i_clock int,i_pos int) RETURNS Int (11) ...
This is equivalent to explicitly informing the MySQL server that the function does not modify the data, so it can be securely created on the Log-bin server and copied to the Log-bin-enabled slave.
How to create a function in MySQL server with log-bin enabled
