Triggers cloud sql 2nd generation

Question

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?

Cheers, Andres

Answer 1

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

You have two options:

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

• 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