This function has none of DETERMINISTIC, NO SQL, or READS SQL

MySQL存储过程与复制警告

最新推荐文章于 2024-04-09 09:41:46 发布

原创最新推荐文章于 2024-04-09 09:41:46 发布 · 178 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #SQL Server #MySQL

mysql 专栏收录该内容

19 篇文章

订阅专栏

本文探讨了MySQL中关于存储过程（SP）和用户定义函数（UDF）声明中的警告，特别是当这些对象未明确指定DETERMINISTIC、NOSQL或READSSQLDATA属性时。文中解释了为什么MySQL服务器会发出此类警告，并提供了几种解决方法，包括更改服务器配置或关闭二进制日志记录。

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary

This warning is rising because as the default MySQL server supports replication. This means that BINARY LOGGING is turned ON.

So, there is a replication log that contains all the changes that is made to the database.

Unfortunately, MySQL is not checking the statements that are stored in the SPs or UDFs. The server can't say, whether the procedure changes the data in the database or not.

That is why MySQL server asks for the explicit specification of the operations that are performed inside the SP or UDF, if the binary logging is on.

This doesn't mean that the procedure will be prohibited to execute the update statements if it is defined as DETERMINISTIC or READS SQL DATA or NO SQL. The characteristics are advisory only.

So, If you are not replicating your server and the binary logging is on as the default, just turn it off to not receive the warning.

Or, if you are replicating the server then you can:

1. Set the SUPER privileges for your user;

2. Change the system variable for the server like:

mysql> SET GLOBAL log_bin_trust_function_creators = 1 ;

3. Set this variable by using the --log-bin-trust-function-creators=1 option when starting the server.