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

Have a think about what's getting written to the binary log.

You can't ensure that an order created on a master would have the same sequence generated for it when the transaction is played on a slave - or, what would much more likely, by another master in the cluster. e.g.

 0) Node 1 and Node 2 are in sync, NextOrderNumber=100
 1) Node 1 receives insert statement wrt order from customer A and assigns 
    order number 100, changes its NextOrderNumber to 101
 2) Node 1 writes the settings update to the log
 3) Node 1 writes the insert statement to the log
 4) Node 2 processes for customer B, asigns order number 100 and increments
 5) Node 2 writes the settings update from to the log
 6) Node 2 writes the insert statement to the log
 7) Nodes 2 reads settings update from the log @2 
         - Its NextOrderNumber is now 102
 8) Node 2 reads insert from log @3, tries to apply it but it fails 
         due to duplicate key
 9) Node 1 reads the update @5 - Its nextOrderNumber is also now 102
 10) Node1 reads insert from log @6 - 
         but this fails due to duplicate key

Now orders 100 on the 2 nodes refer to different data, and there is no order 101.

There is a reason that there has been a lot of functionality added to modify the behaviour of auto_increment variables.

If you wrap the insert in a procedure - which retrieves a value from the sequence generator then embeds it in the insert statement the immediate problem will be resolved, however you need to think about how you avoid assigning the same number twice using different database nodes.

Answer 2

add READS SQL DATA which declare that is a read only function :

CREATE FUNCTION NextOrderNumber() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC
READS SQL DATA
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

Answer 3

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 4

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.

Answer 5

Execute this just before creating the function:

SET @@global.log_bin_trust_function_creators = 1;

And add MODIFIES SQL DATA to the declaration.

Also... well, you asked not to comment the function itself, but I suggest that you drop the number variable and simply do RETURN LAST_INSERT_ID().

Answer 6

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?

According to this Mysql doc:

Assessment of the nature of a function is based on the “honesty” of the creator: MySQL does not check that a function declared DETERMINISTIC is free of statements that produce nondeterministic results.

So it's up to you. If you are sure the method won't cause any problem...

Answer 7

Writing the attribute helped me. In this function, you need to write - MODIFIES SQL DATA - because the function uses UPDATE. If only SELECT is used in the function, then we would write READS SQL DATA. You can also write these two attributes if both data read and write operators are used in the function body.

CREATE FUNCTION NextOrderNumber() 
RETURNS INTEGER 
UNSIGNED 
NOT DETERMINISTIC
MODIFIES SQL DATA
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

Answer 8

There are two ways to fix this:

Execute the following in the MySQL console:

SET GLOBAL log_bin_trust_function_creators = 1;

Add the following to the mysql.ini configuration file:

log_bin_trust_function_creators = 1

The setting relaxes the checking for non-deterministic functions. Non-deterministic functions are functions that modify data (i.e. have update, insert or delete statement(s)). For more info, see here.

Please note, if binary logging is NOT enabled, this setting does not apply.