在其声明中使用DETERMINISTIC，NO SQL或READS SQL DATA并启用二进制日志记录 [英] DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

问题描述

在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)

我不知道我需要更改哪些内容.谁能帮我解决这个问题?

解决方案

有两种方法可以解决此问题:

1. 在MySQL控制台中执行以下操作:

   SET GLOBAL log_bin_trust_function_creators = 1;

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

   log_bin_trust_function_creators = 1;

该设置放宽了对不确定功能的检查.非确定性函数是修改数据的函数(即具有更新，插入或删除语句的函数).有关更多信息，请参见此处.

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

存储程序的二进制记录

如果未启用二进制日志记录，则log_bin_trust_function_creators会执行 不适用.

log_bin_trust_function_creators

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

最好的方法是更好地理解和使用存储函数的确定性声明. MySQL使用这些声明来优化复制，因此，仔细选择它们以确保复制正常是一件好事.

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

不确定性 与"DETERMINISTIC"相反. "如果例程定义中未给出DETERMINISTIC或NOT DETERMINISTIC，则默认值为NOT DETERMINISTIC.要声明函数是确定性的，必须明确指定DETERMINISTIC.". 因此，似乎没有作出任何声明，MySQl会将函数视为"NOT DETERMINISTIC". 手册中的这一陈述与手册另一领域中的其他陈述相矛盾，该陈述表明: "创建存储函数时，必须声明它是确定性的或不修改数据.否则，对于数据恢复或复制来说可能是不安全的. 默认情况下，要接受CREATE FUNCTION语句，必须显式指定DETERMINISTIC，NO SQL或READS SQL DATA中的至少一个.否则会发生错误"

如果没有声明，我个人会在MySQL 5.5中出错，因此无论我可能有其他声明如何，我总是至少放置一个声明"DETERMINISTIC"，"NOT DETERMINISTIC"，"NO SQL"或"READS SQL DATA".

读取SQL数据 这明确地告诉MySQL该函数将仅从数据库读取数据，因此，它不包含修改数据的指令，但是包含读取数据的SQL指令(例如SELECT).

修改SQL数据 这表明该例程包含可能写入数据的语句(例如，它包含UPDATE，INSERT，DELETE或ALTER指令).

没有SQL 这表明该例程不包含任何SQL语句.

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

请注意，有些MySQL函数不是确定性安全的，例如:NOW()，UUID()等，它们可能在不同的机器上产生不同的结果，因此必须声明包含此类指令的用户函数不具有决定性. 同样，从未复制模式中读取数据的函数显然是NONDETERMINISTIC. *

对例行程序性质的评估是基于对常规"的诚实"进行的. 创建者:MySQL不检查声明为DETERMINISTIC的例程是否为 没有产生不确定结果的陈述.然而， 错误声明例程可能会影响结果或影响性能. 将不确定的例程声明为DETERMINISTIC可能会导致 通过使优化器产生不正确的结果而产生意外结果 执行计划的选择.将确定性例程声明为 不确定性可能会导致可用性能降低性能 不使用优化.

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

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?

解决方案

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;

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

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.

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

NOT DETERMINISTIC Opposite 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"

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.

READS SQL DATA This 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).

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

NO SQL This indicates that the routine contains no SQL statements.

CONTAINS SQL This 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.

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

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.

这篇关于在其声明中使用DETERMINISTIC，NO SQL或READS SQL DATA并启用二进制日志记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！