This function has none of deterministic, NO SQL, or READS SQL DATA

This function has none of deterministic, NO SQL solution 2011-12-01 14:07:01

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, NOSQL, or READS SQL DATA in its declaration and binary loggin G IsEnabled (*might* want to use the less safelog_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 Code

MySQL > Showvariables like 'log_bin_trust_function_creators';

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

|                  variable_name | Value |

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

| log_bin_trust_function_creators |  OFF |

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

MySQL > Set globallog_bin_trust_function_creators = 1 ;

MySQL > Showvariables 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 none of deterministic, NO SQL, or READS SQL DATA