Unable to create Triggers in Azure Mysql DB

user_4132097 0
Viewed 1273

I have a MySQL Database running in azure. I have been trying to create some very basic trigger which I have tested on a local instance of MySQL which worked like they where supposed to do.

However, It doesn't seem like an option to create triggers in Azure's MySQL database.

> CREATE TRIGGER update_last_modified_xxx
-> BEFORE UPDATE ON xxx
->     FOR EACH ROW
-> BEGIN
->     SET new.last_modified = NOW();
-> END;
->


(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)') (edited)

And changing log_bin_trust_function_creators is not possible due to the limitations set by MS. Am I missing something here or should it be possible to create triggers in a MySQL DB running in azure?

azure mysql
3 Answers

I've simply set the log_bin_trust_function_creators parameter 'ON' by hand from 'Server Parameters' tab in the Azure Portal and solved the issue.

user_122542140
user_14918950

In Azure Database for MySQL, binary logs are always enabled (i.e. log_bin is set to ON). In case you want to use triggers you will get error similar to 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).

The binary logging format is always ROW and all connections to the server ALWAYS use row-based binary logging. With row-based binary logging, security issues do not exist and binary logging cannot break, so you can safely set log_bin_trust_function_creators to TRUE.

Ref: https://docs.microsoft.com/en-us/azure/mysql/concepts-server-parameters#log_bin_trust_function_creators

user_151084520
Related