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...