Mysql privileges broken by log_bin_trust_function_creators=1 flag

jp flag

My privileges seem to be broken all round since enabling log_bin_trust_function_creators on GCP Cloud SQL

I needed a trigger, which they don't allow at all with Binary Logging (which is enabled). So I enabled that flag, because the insert trigger is deterministic + very simple, therefore safe for binary logging

As soon as the flag was set I was thrown a error, not anymore that "triggers were blocked full stop/try the flag etc", but now a pure user privileges error.

Then I realised that I couldn't view tables anymore. Not even the user tables! Even when logged in as root!

And all I did was enable that flag...

I disabled the flag and removed. I even rebooted.

Still locked out.

I am not replicating, but added that tag because of the link with binary logging


I have a backup, will have lost a day's work :(

RR44 avatar
jp flag
I now see that then root % user has had its permissions cut by the managed service to just USAGE, meaning no privileges... The root localhost user has all privs... But I can't run from that on this managed service and there. Thankfully I have another user with admin rights. But what a mess from just changing that flag!

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.