没有超级权限就无法创建简单的MySQL函数 [英] Can't create simple MySQL function without super privilege

问题描述

我一直在创建存储过程，没有问题，但是当尝试创建一个简单的函数来返回表上COUNT()函数的结果时，我得到了:

I've been creating stored procedures without problems but when trying to create a simple function which returns the result of a COUNT() function on a table, I get:

错误1418:此函数没有DETERMINISTIC，NO SQL或READS 在其声明和二进制日志记录中启用了SQL DATA(您可能 想要使用不太安全的log_bin_trust_function_creators变量)

ERROR 1418: 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)

因为它使用的是COUNT()，所以我将其更改为READS SQL DATA，但出现错误:

Since it's using COUNT() I change it to be READS SQL DATA but then get the error:

错误1419:您没有SUPER特权，并且二进制日志记录是 已启用(您可能希望使用安全性较低的 log_bin_trust_function_creators变量)

ERROR 1419: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

我不明白为什么我可以创建过程但不能创建函数.日志记录并不是我真正可以访问的东西，即使我了解了该区域的问题所在.有一点谷歌搜索表明这与数据库复制有关，但这只是一个简单的数据库.

I don't understand why I can create procedures but not functions. The logging is not something I really have access to, even if I understood what the problem was in that area... a little Googling suggests it's to do with DB replication or something but this is a simple DB only.

推荐答案

前滚从备份还原的数据库时，也会使用binlog.它是标准内置的.

The binlog is also used when rolling forward a database restored from backup. It's built in as standard.

如果在不同的时间执行函数，则它将根据计数的行数返回不同的值.二进制记录的目的是使您的系统从一种已知状态转变为另一种已知状态.重播您的陈述会影响结果.解决方案是在不依赖Binlog的情况下将其关闭，或者允许DBMS将数据更改写入Binlog，而不是用于更改数据的语句.

If you execute your function at different times it will return different values depending on how many rows it has counted. And the point of binlogging is to get your system from one known state to another known state. When your statement is replayed will affect the outcome. The solution is to switch off binlogging if your not relying on it, or to allow the DBMS to write the data changes to the binlog instead of the statements used to change the data.

所有内容都在手册中.

这篇关于没有超级权限就无法创建简单的MySQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！