CREATE FUNCTION error "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA"

Question

Our database has a function to generate an order number. It reads a value from a Settings table, increments it, then returns the new value. For example:

CREATE FUNCTION NextOrderNumber() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC
BEGIN
  DECLARE number INTEGER UNSIGNED;
  UPDATE Settings SET IntegerValue=LAST_INSERT_ID(IntegerValue+1) WHERE KeyName='NextOrderNumber';
  SET number=LAST_INSERT_ID();
  return number;
END

Note: Don't critique this function I know it has flaws it's just for illustration.

We use this function as follows:

INSERT INTO Orders(OrderNumber, ...)
SELECT NextOrderNumber(), ...

When binary logging is enabled, CREATE FUNCTION gives this error:

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)

Regardless of what binlog_format is set, is there really a problem with the above function? According to my reading of the relevant MySQL page I can't see any reason why this function would be incompatible with replication, with either ROW or STATEMENT level binary logging.

If the function is safe, setting the global log_bin_trust_function_creators=1 makes me uneasy. I don't want to disable this check for all functions, just this one. Could I instead just flag the function as NO SQL to suppress the warning? I tried it and it worked. Will this cause any problem?

Answer 1

I've googled and here I am. I've found a way :

SET GLOBAL log_bin_trust_function_creators = 1;

But be careful, it may be unsafe for data recovery or replication...

Answer 2

As per my understating it cause problem when data recovery or replication

Ref: http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

MySQL 5.0.6: Statements that create stored routines and CALL statements are logged. Stored function invocations are logged when they occur in statements that update data (because those statements are logged).

However, function invocations are not logged when they occur in statements such as SELECT that do not change data, even if a data change occurs within a function itself; this can cause problems.

Under some circumstances, functions and procedures can have different effects if executed at different times or on different (master and slave) machines, and thus can be unsafe for data recovery or replication.

E.g.

CREATE FUNCTION myfunc () RETURNS INT DETERMINISTIC
BEGIN
  INSERT INTO t (i) VALUES(1);
  RETURN 0;
END;

SELECT myfunc();

If a stored function is invoked within a statement such as SELECT that does not modify data, execution of the function is not written to the binary log, even if the function itself modifies data. This logging behavior has the potential to cause problems. Suppose that a function myfunc() is defined as above.