触发Cloud SQL第二代 [英] Triggers cloud sql 2nd generation

问题描述

我正在尝试使用当前在第一代云SQL上运行的数据库转储填充第二代云sql实例(v5.7).它具有一些触发条件:

I am trying to populate a 2nd gen cloud sql instance (v5.7) with a database dump currently running on 1st gen cloud SQL. It has some triggers:

    /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `resourcetypetransaction_AFTER_INSERT` AFTER INSERT ON `resourcetypetransaction` FOR EACH ROW
  BEGIN
    INSERT INTO `resourcetypetransactionlog` SELECT *
                                             FROM `resourcetypetransaction`
                                             WHERE id = NEW.id;
  END */;;

尝试将数据库插入第二代时的结果是:

The result when trying to insert the database to 2nd gen is:

shinkansen:sql ameyer$ cat gae_2016-08-30T08\:21\:33.sql | mysql -u root -pxxxx -h xxxx napoleon;
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1419 (HY000) at line 1067: 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)

有没有办法在第二代上启用这样的触发器?

Is there a way to enable triggers like this on 2nd gen?

干杯， 安德烈斯

推荐答案

由于某些触发器在启用复制后会产生不一致的结果，因此默认配置中不允许使用它们.

Because some triggers can produce inconsistent results when replication is enabled, they are not allowed in the default configuration.

您有两个选择:

• 如果您不关心复制或能够进行时间点恢复，则可以禁用二进制日志.

• If you do not care about replication or being able to do point in time recovery, you can disable Binary Logs.

否则，如果您确定触发器是安全的，则可以编辑实例并在advanced options下将log_bin_trust_function_creators标志设置为true.为了安全起见，触发器必须保证它将对主副本和副本副本产生相同的效果.您可以在以下文章中找到不安全触发器的说明和示例:

Otherwise, if you are sure that your trigger is safe you can edit your instance and set the log_bin_trust_function_creators flag to true under advanced options. To be safe, the trigger must guarantee that it would generate the same effect on both the master and the replica(s). You can find an explanation and an example of an unsafe trigger in the following post: https://dba.stackexchange.com/questions/73447/why-when-does-creating-a-mysql-trigger-require-super-privileges

这篇关于触发Cloud SQL第二代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！