CODEWITHSUNDEEP
mysqlazure
Daniel Guldberg Aaes
Daniel Guldberg Aaes

Reputation: 292

Unable to create Triggers in Azure Mysql DB

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?

Upvotes: 3

Views: 2098

Answers (3)

H Cahit
H Cahit

Reputation: 41

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.

Upvotes: 4

Bashar
Bashar

Reputation: 21

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://learn.microsoft.com/en-us/azure/mysql/concepts-server-parameters#log_bin_trust_function_creators

Upvotes: 2

Barmar
Barmar

Reputation: 782785

From Online migration issues & limitations to Azure DB for MySQL with Azure Database Migration Service

Schema in target Azure Database for MySQL must not have any triggers.

So I don't think the Azure service allows triggers.

Upvotes: 0

Related Questions