When creating a stored procedure
Error message:
Copy codeThe Code is as follows:
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)
Cause:
Copy codeThe Code is as follows:
If bin-log is enabled, you must specify whether our function is
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
In function, only DETERMINISTIC, no SQL, and reads SQL DATA are supported. If bin-log is enabled, we must specify a parameter for our function.
Solution:
Copy codeThe Code is as follows:
SQL code
Mysql> show variables like 'Log _ bin_trust_function_creators ';
+ --------------------------------- + ------- +
| Variable_name | Value |
+ --------------------------------- + ------- +
| Log_bin_trust_function_creators | OFF |
+ --------------------------------- + ------- +
Mysql> set global log_bin_trust_function_creators = 1;
Mysql> show variables like 'Log _ bin_trust_function_creators ';
+ --------------------------------- + ------- +
| Variable_name | Value |
+ --------------------------------- + ------- +
| Log_bin_trust_function_creators | ON |
After the parameter is added, if mysqld is restarted, the parameter will disappear again, so remember to add it in the my. cnf configuration file:
Log_bin_trust_function_creators = 1