无法在 5.1.32 上创建具有 TRIGGER 权限的 MySQL 触发器 [英] Can't create MySQL trigger with TRIGGER privilege on 5.1.32

问题描述

我和我的开发人员在共享 MySQL 开发数据库上拥有自己的开发模式.我的任务要求我在我的架构中创建触发器，但到目前为止我没有成功.

My fellow developers and I have our own development schemas on a shared MySQL development database. My assignment requires me to create triggers in my schema, yet I've been unsuccessful so far.

CREATE TRIGGER myTrigger AFTER DELETE on myTable
FOR EACH ROW BEGIN
    -- DO STUFF
END;

MySQL 说:错误 1419 (HY000):您没有 SUPER 权限并且启用了二进制日志记录(您可能想要使用不太安全的 log_bin_trust_function_creators 变量)

MySQL says: ERROR 1419 (HY000): 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)

我查看了 MySQL 手册(http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html):

I checked the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html):

TRIGGER 权限使您能够创建和删除触发器.您必须拥有该表的此权限才能为该表创建或删除触发器.这个特权是在 MySQL 5.1.6 中添加的.(在 MySQL 5.1.6 之前，触发器操作需要 SUPER 权限.)

The TRIGGER privilege enables you to create and drop triggers. You must have this privilege for a table to create or drop triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER privilege.)

我们运行的是5.1.32-enterprise-gpl-advanced-log"，所以TRIGGER权限应该足够了；但是，DBA 授予我 mySchema.* 的 TRIGGER 权限，当我执行 SHOW GRANTS; 时我可以看到它；但我仍然收到关于需要SUPER"权限的错误.我们不想给所有的开发者超级.

We are running "5.1.32-enterprise-gpl-advanced-log", so the TRIGGER privilege should be sufficient; however, the DBA granted me the TRIGGER privilege on mySchema.* and I can see it when I do SHOW GRANTS;, yet I still get this error about needing the "SUPER" privilege. We don't want to give all of the developers SUPER.

有什么建议吗?

推荐答案

这里是 bug为此报告.一种选择是运行--log-bin-trust-function-creators 选项已打开，这将允许您在没有 SUPER 权限的情况下创建触发器.本页解释了开启该选项意味着什么.基本上它与 MySQL 是否认为您的触发器是确定性的(即复制安全)有关.您的 DBA 可能适合也可能不适合在该模式下运行.这并不理想，但总比给 SUPER 好...

Here is the bug report for this. One option is to run with the --log-bin-trust-function-creators option turned on, which will allow you to create triggers without the SUPER privilege. This page explains what turning that option on means. Basically it has to do with whether or not MySQL thinks your triggers are deterministic (i.e. safe for replication). Your DBA may or may not be comfortable running in that mode. It's not ideal, but better than giving out SUPER...

更新:第二个链接中的文档实际上听起来您可以通过使用基于行的复制，甚至混合模式复制来解决这个问题.至少这将使复制安全.我不知道您是否仍然需要拥有 SUPER，但它可能值得一试.

UPDATE: The docs at the second link actually make it sound like you may be able to get around this by using row-based replication, or even mixed-mode replication. At least that would make it safe for replication. Whether or not you would still be required to have SUPER, I don't know, but it may be worth a try.

这篇关于无法在 5.1.32 上创建具有 TRIGGER 权限的 MySQL 触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！