MySQL DETERMINISTIC、NO SQL 或 READS SQL DATA 在其声明和二进制日志记录已启用

DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

提问by ASR

While importing the database in mysql, I have got following error:

在mysql中导入数据库时​​，出现以下错误：

1418 (HY000) at line 10185: 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)

1418 (HY000) at line 10185: 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)

I don't know which things i need to change. Can any one help me how to resolve this?

我不知道我需要改变哪些事情。任何人都可以帮我解决这个问题吗？

回答by Donal

There are two ways to fix this:

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

1. Execute the following in the MySQL console:

   SET GLOBAL log_bin_trust_function_creators = 1;

2. Add the following to the mysql.ini configuration file:

   log_bin_trust_function_creators = 1;

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

   SET GLOBAL log_bin_trust_function_creators = 1;

2. 将以下内容添加到 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.

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

Binary Logging of Stored Programs

存储程序的二进制日志

If binary logging is not enabled, log_bin_trust_function_creators does not apply.

如果未启用二进制日志记录，则 log_bin_trust_function_creators 不适用。

log_bin_trust_function_creators

log_bin_trust_function_creators

This variable applies when binary logging is enabled.

当启用二进制日志记录时，此变量适用。

The best approach is a better understanding and use of deterministic declarations for stored functions. These declarations are used by MySQL to optimize the replication and it is a good thing to choose them carefully to have a healthy replication.

最好的方法是更好地理解和使用存储函数的确定性声明。MySQL使用这些声明来优化复制，仔细选择它们以获得健康的复制是一件好事。

DETERMINISTICA routine is considered “deterministic” if it always produces the same result for the same input parameters and NOT DETERMINISTIC otherwise. This is mostly used with string or math processing, but not limited to that.

DETERMINISTIC如果例程总是为相同的输入参数产生相同的结果，则该例程被认为是“确定性的”，否则为 NOT DETERMINISTIC。这主要用于字符串或数学处理，但不限于此。

NOT DETERMINISTICOpposite of "DETERMINISTIC". "If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.". So it seems that if no statement is made, MySQl will treat the function as "NOT DETERMINISTIC". This statement from manual is in contradiction with other statement from another area of manual which tells that: " When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication. By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs"

NOT DETERMINISTIC与“DETERMINISTIC”相反。"如果例程定义中既未给出 DETERMINISTIC 也未给出 NOT DETERMINISTIC，则默认值为 NOT DETERMINISTIC。要声明函数是确定性的，您必须明确指定 DETERMINISTIC。"。所以看起来如果没有声明，MySQl 会将该函数视为“NOT DETERMINISTIC”。手册中的这一声明与手册另一领域的其他声明相矛盾，该声明指出：“创建存储函数时，必须声明它是确定性的或不修改数据。否则，数据恢复或复制可能不安全。默认情况下，要接受 CREATE FUNCTION 语句，必须明确指定 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的至少一个。否则会出现错误"

I personally got error in MySQL 5.5 if there is no declaration, so i always put at least one declaration of "DETERMINISTIC", "NOT DETERMINISTIC", "NO SQL" or "READS SQL DATA" regardless other declarations i may have.

如果没有声明，我个人在 MySQL 5.5 中遇到错误，所以我总是至少放置一个“DETERMINISTIC”、“NOT DETERMINISTIC”、“NO SQL”或“READS SQL DATA”的声明，而不管我可能有其他声明。

READS SQL DATAThis explicitly tells to MySQL that the function will ONLY read data from databases, thus, it does not contain instructions that modify data, but it contains SQL instructions that read data (e.q. SELECT).

READS SQL DATA这明确告诉 MySQL 该函数只会从数据库读取数据，因此，它不包含修改数据的指令，但它包含读取数据的 SQL 指令（eq SELECT）。

MODIFIES SQL DATAThis indicates that the routine contains statements that may write data (for example, it contain UPDATE, INSERT, DELETE or ALTER instructions).

MODIFIES SQL DATA这表明例程包含可能写入数据的语句（例如，它包含 UPDATE、INSERT、DELETE 或 ALTER 指令）。

NO SQLThis indicates that the routine contains no SQL statements.

NO SQL这表示例程不包含 SQL 语句。

CONTAINS SQLThis indicates that the routine contains SQL instructions, but does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SELECT NOW(), SELECT 10+@b, SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

CONTAINS SQL这表示例程包含 SQL 指令，但不包含读取或写入数据的语句。如果没有明确给出这些特征，则这是默认设置。此类语句的示例是 SELECT NOW()、SELECT 10+@b、SET @x = 1 或 DO RELEASE_LOCK('abc')，它们执行但既不读取也不写入数据。

Note that there are MySQL functions that are not deterministic safe, such as: NOW(), UUID(), etc, which are likely to produce different results on different machines, so a user function that contains such instructions must be declared as NOT DETERMINISTIC. Also, a function that reads data from an unreplicated schema is clearly NONDETERMINISTIC. *

注意有MySQL函数不是确定性安全的，比如：NOW()、UUID()等，在不同机器上很可能会产生不同的结果，所以包含此类指令的用户函数必须声明为NOT DETERMINISTIC . 此外，从非复制模式读取数据的函数显然是非确定性的。*

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

对例程性质的评估基于创建者的“诚实”：MySQL 不会检查声明为 DETERMINISTIC 的例程是否没有产生不确定结果的语句。但是，错误声明例程可能会影响结果或影响性能。将非确定性例程声明为 DETERMINISTIC 可能会导致优化器做出不正确的执行计划选择，从而导致意外结果。将确定性例程声明为 NONDETERMINISTIC 可能会导致不使用可用优化，从而降低性能。

回答by Sunny S.M

• When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

• By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

• 创建存储函数时，必须声明它是确定性的或不修改数据。否则，数据恢复或复制可能不安全。

• 默认情况下，要接受 CREATE FUNCTION 语句，必须明确指定 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的至少一个。否则会出现错误：

To fix this issue add following lines After Return and Before Begin statement:

要解决此问题，请在 Return 和 Before Begin 语句之后添加以下行：

READS SQL DATA
DETERMINISTIC

For Example :

例如 ：

CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
/*ADD HERE */
READS SQL DATA
DETERMINISTIC
BEGIN

For more detail about this issue please read Here

有关此问题的更多详细信息，请阅读此处

回答by Mayra Navarro

following Donald's comment:

遵循唐纳德的评论：

This variable applies when binary logging is enabled.

当启用二进制日志记录时，此变量适用。

All I had to do was:

我所要做的就是：

1. disabled log_bin in my.cnf (#log_bin)
2. restart mysql
3. import DB
4. enable log_bin
5. restart mysql

1. 在 my.cnf (#log_bin) 中禁用 log_bin
2. 重启mysql
3. 导入数据库
4. 启用 log_bin
5. 重启mysql

That step out that import problem.

那一步解决了导入问题。

(Then I'll review the programmer's code to suggest an improvement)

（然后我会查看程序员的代码以提出改进建议）

回答by Park JongBum

When your function is deterministic, you are safe to declare it to be deterministic. The location of "DETERMINISTIC" keyword is as follows.

当您的函数是确定性的时，您可以安全地将其声明为确定性的。“DETERMINISTIC”关键字的位置如下。

回答by Preetham

On Windows 10,

在 Windows 10 上，

I just solved this issue by doing the following.

我刚刚通过执行以下操作解决了这个问题。

1. Goto my.ini and add these 2 lines under [mysqld]

   skip-log-bin
   log_bin_trust_function_creators = 1
   

2. restart MySQL service

1. 转到 my.ini 并在[mysqld]下添加这两行

   skip-log-bin
   log_bin_trust_function_creators = 1
   

2. 重启 MySQL 服务

回答by DevKingKev

Try setting the definer for the function!

尝试为函数设置定义器！

So instead of

所以代替

CREATE FUNCTION get_pet_owner

CREATE FUNCTION get_pet_owner

you will write something akin to

你会写一些类似于

CREATE DEFINER=procadmin@%FUNCTION get_pet_owner

CREATE DEFINER=procadmin@%FUNCTION get_pet_owner

which ought to work if the user prodacminhas rights to create functions/procedures.

如果用户prodacmin有权创建函数/过程，这应该可以工作。

In my case the function worked when generated through MySQL Workbench but did not work when run directly as an SQL script. Making the changes above fixed the problem.

在我的情况下，该函数在通过 MySQL Workbench 生成时起作用，但在直接作为 SQL 脚本运行时不起作用。进行上述更改解决了问题。