MySQL CREATE FUNCTION 错误“此函数没有 DETERMINISTIC、NO SQL 或 READS SQL 数据”

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

提问by richb

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:

启用二进制日志记录时，CREATE FUNCTION 会出现以下错误：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you mightwant to use the less safe log_bin_trust_function_creators variable)

此函数在其声明中没有 DETERMINISTIC、NO SQL 或 READS SQL DATA，并且启用了二进制日志记录（您可能希望使用不太安全的 log_bin_trust_function_creators 变量）

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

不管binlog_format设置了什么，上面的函数真的有问题吗？根据我对相关MySQL 页面的阅读，我看不出为什么这个函数与复制不兼容，无论是 ROW 还是 STATEMENT 级别的二进制日志记录。

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?

如果函数是安全的，设置全局 log_bin_trust_function_creators=1 会让我感到不安。我不想对所有功能禁用此检查，仅此一项。我可以将函数标记为 NO SQL 来抑制警告吗？我试过了，它奏效了。这会导致任何问题吗？

回答by SpiderWan

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

但要小心，数据恢复或复制可能不安全......

回答by Rikin Patel

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

参考：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).

MySQL 5.0.6：记录创建存储例程和 CALL 语句的语句。存储函数调用发生在更新数据的语句中时会被记录下来（因为这些语句会被记录）。

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.

但是，当函数调用发生在不更改数据的语句（例如 SELECT）中时，不会记录它们，即使数据更改发生在函数本身内；这可能会导致问题。

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

如果在不修改数据的SELECT等语句中调用存储的函数，则该函数的执行不会写入二进制日志，即使该函数本身修改了数据。这种记录行为有可能导致问题。假设一个函数myfunc()定义如上。

回答by Wale

There are two ways to fix this:

有两种方法可以解决这个问题：

Execute the following in the MySQL console:

在 MySQL 控制台中执行以下命令：

SET GLOBAL log_bin_trust_function_creators = 1;

Add the following to the mysql.ini configuration file:

将以下内容添加到 mysql.ini 配置文件中：

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.

请注意，如果未启用二进制日志记录，则此设置不适用。

回答by matof

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?

我可以将函数标记为 NO SQL 来抑制警告吗？我试过了，它奏效了。这会导致任何问题吗？

According to this Mysql doc:

根据这个Mysql 文档：

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.

对函数性质的评估基于创建者的“诚实”：MySQL 不会检查声明为 DETERMINISTIC 的函数是否没有产生不确定结果的语句。

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

所以这取决于你。如果您确定该方法不会引起任何问题...

回答by symcbean

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.

现在2个节点上的100号订单指的是不同的数据，没有101号订单了。

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

添加了许多功能来修改 auto_increment 变量的行为是有原因的。

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.

如果您将插入包装在一个过程中——它从序列生成器中检索一个值，然后将它嵌入到插入语句中，直接问题将得到解决，但是您需要考虑如何避免使用不同的数据库节点两次分配相同的数字。

回答by Jonathan Joestar

add READS SQL DATAwhich declare that is a read only function :

添加READS SQL DATA它声明它是一个只读函数：

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

回答by Federico Razzoli

Execute this just before creating the function:

在创建函数之前执行它：

SET @@global.log_bin_trust_function_creators = 1;

And add MODIFIES SQL DATAto the declaration.

并添加MODIFIES SQL DATA到声明中。

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

另外......好吧，你要求不要评论函数本身，但我建议你删除number变量并简单地做RETURN LAST_INSERT_ID().