This function has a none of deterministic, no sql,or reads SQL data in it declaration and binary logging is enabled

Original: http://blog.chinaunix.net/uid-20639775-id-3031821.html This function has none of deterministic, NO SQL solution

When you create a stored procedure

Error message:

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL, or READS SQL DATA in its declaration and binary Loggi NG is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Reason:

This is when we open the bin-log and we have to specify whether our function is
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

Within the function, only deterministic, NO SQL, and READS SQL DATA are supported. If we open the Bin-log, we must specify a parameter for our function.

Workaround:

SQL codemysql> 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 |+---------------------------------+-------+
With this added parameter, if MYSQLD restarts, that parameter disappears, so remember to add it in the MY.CNF configuration file:
Log_bin_trust_function_creators=1

This function has a none of deterministic, no sql,or reads SQL data in it declaration and binary logging is enabled