无法在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.

有什么建议吗?

推荐答案

这是错误报告.一种选择是使用 --log-bin-trust-function-creators选项已打开，这将使您可以创建没有SUPER特权的触发器. 此页面解释了启用该选项的含义.基本上，这与MySQL是否认为您的触发器具有确定性(即复制安全)有关.您的DBA在该模式下运行时可能会适应，也可能不适应.这不是理想的方法，但是比给出超级方法要好.

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屋！