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

Pascual

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

Selfdevotion

This did not work, I still have the same error after replacing /*!50017 DEFINER=root@%*/ by /*!50017 */ – Pascual

now works fine. I do have a "now()" in the trigger which will create a slightly different time on master and slave, but I don't think its a problem. I hope Cloud SQL will support multiple sql_mode flags soon, so one can write clean date code too. – Pascual

Even with this option set, I found I had to change /*!50017 DEFINER='root'@'localhost'*/ to /*!50017 DEFINER='root'@'%'*/ – Supremacist

Answer 2

I ran into the same issue and after reading all forums I could, the solution is to set the variable in the control panel from Google Cloud SQL instead of the console.

1. Enter in the Instances Page: Instances

2. Select your instance and go to edit. (Hit the three dots button).

3. Go to the last option, Flags of "Marcas de Cloud SQL".

4. Put your variable in ON State.

   log_bin_trust_function_creators -> ON

5. I also had to wait a couple of minutes and enter to the Cloud Shell. (I don't have enough reputation to enter the link but look in google for "Using the mysql client in the Cloud Shell"

2nd Generation Cloud SQL

Gainey

Answer 3

I had the same problem. How about the following operation ? 1. download the exported .sql file on the Cloud Strage 2.connect to Cloud SQL as root on CLI 3. import(e.g. source xxx.sql) It worked well.

Gegenschein