创建函数错误："此函数缺少DETERMINISTIC、NO SQL或READS SQL DATA中的任何一个属性"

Question

我们的数据库有一个生成订单号的功能。它会从一个设置表中读取一个值，将其递增，然后返回新的值。例如：

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

注意：不要批评此函数，我知道它有缺陷，仅供说明。

我们使用此函数的方式如下：

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

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

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

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

如果该函数是安全的，则将全局 log_bin_trust_function_creators=1 会让我感到不安。我不想为所有函数禁用此检查，只想禁用此函数的检查。我能否将该函数标记为 NO SQL 以取消警告？我尝试过并且有效。这会引起任何问题吗？

- richb

Answer 1

我通过谷歌搜索找到了这里。 我找到了一种方法：

SET GLOBAL log_bin_trust_function_creators = 1;

但是要小心，这可能不安全，可能会影响数据恢复或者复制...

- SpiderWan

---

这并没有以任何方式回答这个问题。 - richb

Answer 2

根据我的理解，当进行数据恢复或复制时，可能会出现问题。

参考：http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html MySQL 5.0.6：创建存储例程的语句和CALL语句会被记录在日志中。当函数调用出现在更新数据的语句中（因为这些语句被记录），函数调用也会被记录在日志中。

然而，在不改变数据的语句（比如SELECT）中出现的函数调用不会被记录，即使函数本身导致了数据更改；这可能会引起问题。

在某些情况下，函数和过程在不同时间或不同（主机和从机）的计算机上执行可能会有不同的效果，因此可能对数据恢复或复制不安全。

例如：

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

SELECT myfunc();

如果在类似于SELECT的语句中调用存储函数，而该语句不修改数据，则即使函数本身修改了数据，该函数的执行也不会被写入二进制日志。这种日志记录行为可能会引起问题。假设一个名为myfunc()的函数如上所述被定义。

- Rikin Patel

---

我认为这不正确。查看链接，您将看到_斜体字_...对于存储函数，记录的是函数内部所做的行更改，而不是函数调用。对于触发器，记录的是触发器所做的行更改。..._斜体字_。因此，对我来说，logbin_format=ROW仍然似乎不允许未标记为DETERMINISTIC的函数，这是无法解释的。 - Alan Berezin

Answer 3

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

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

SET GLOBAL log_bin_trust_function_creators = 1;

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

log_bin_trust_function_creators = 1

该设置放宽了对非确定性函数的检查。非确定性函数是指修改数据的函数（即具有更新、插入或删除语句的函数）。更多信息请参见这里。

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

- Wale

---

当我从视图中选择表时，遇到了这个错误。有什么解决办法吗？ - sat

Answer 4

思考一下写入二进制日志的内容。

无法确保在主服务器上创建的订单在事务在从服务器上播放时生成相同的序列，或者更有可能的是在集群中的另一个主服务器上生成。例如：

 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

现在，节点2上的100个订单涉及不同的数据，并且不存在101个订单。

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

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

- symcbean

---

symcbean - 我理解这种方式在集群中不起作用。我不是询问那种情况。我是在询问简单的主从设置，其中插入/更新只发生在一个地方。 - richb

Answer 5

编写属性对我很有帮助。在这个函数中，你需要写 - MODIFIES SQL DATA - 因为该函数使用了 UPDATE。如果函数中只使用 SELECT，则我们会写 READS SQL DATA。如果函数体中同时使用了数据读取和写入操作符，那么你也可以写这两个属性。

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

- FranSC

Answer 6

我能否只将函数标记为NO SQL以抑制警告？我尝试了一下，它起作用了。这会引起任何问题吗？

根据这个Mysql文档：

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

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

- matof

Answer 7

添加 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

- Jonathan Joestar

Answer 8

在创建函数之前执行以下操作：

SET @@global.log_bin_trust_function_creators = 1;

在声明中添加MODIFIES SQL DATA。

还有...好吧，你要求不评论函数本身，但我建议你放弃number变量，直接执行RETURN LAST_INSERT_ID()。

- Federico Razzoli

---

这个不起作用。log_bin_trust_function_creators是全局变量，而不是会话变量。 - richb

什么？全局行为没有受到影响吗？是的，它受到了影响。您正在设置一个全局变量。 - richb