Can't create Trigger on MySql table within Google Cloud [duplicate]

Question

CREATE TRIGGER trigger_LocationType_Insert
BEFORE UPDATE ON LocationType
FOR EACH ROW
SET NEW.NAME = ''

Returns this error:

[HY000][1419] 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)

I know this is because even the root user "has all privileges except SUPER and FILE." https://cloud.google.com/sql/docs/mysql/users

And I think that if I could "Set log_bin_trust_function_creators to 1" then it would work (this user does have CREATE TRIGGER permission). However, I don't see how to do that in the Google Cloud SQL console or from a sql statement.

How do you create a TRIGGER then within this environment?

Bellicose

Try to create with DEFINER = user or DEFINER = CURRENT_USER read ( dev.mysql.com/doc/refman/5.7/en/create-trigger.html ) – Storfer

I tried CREATE DEFINER = CURRENT_USER TRIGGER... without any change. – Bellicose

Answer 1

This page described how to change MySQL configuration options for a Google Cloud instance: https://cloud.google.com/sql/docs/mysql/flags

Select the instance to open its Instance Overview page.

The database flags that have been set are listed under the Database flags section.

You have to use their UI to change global variables, because you can't use SET GLOBAL without SUPER privilege.

The log_bin_trust_function_creators option is listed among the supported configuration options you can change in the UI on the page I linked to above.

To read more about this error, see https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

Organist