MYSQL log_bin_trust_function_creators

MySQL

I am trying to create triggers on a mysql database. In doing this I encountered the following error:

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)

After a bit of research on the MySQL doc I pretty much understood that when creating triggers or store procedures you run into the risk of them being non deterministic and mess up the binary log. This is because the binary log won't be able to be used correctly for replication or data recovery if any of these functions might produce a different result given the same inputs.

The quick fix for this is to tell mysql not to worry by giving the user SUPER privileges or by setting log_bin_trust_function_creators = 1 (as reported by different articles and stackoverflow answers)

I can do that, but I use triggers as a logging mechanism and I call NOW() inside it. This is clearly non deterministic.

Reading a bit further though they seem to say that these issues are only present if binlog_format is set to STATEMENT or in other words if the db logs mysql statements that have been run, and not the resulting rows.

So recapping, if my binlog_format is set to MIXED or ROW i should not be worring about any of this and just set log_bin_trust_function_creators to be able to create these triggers, otherwise since my triggers are non deterministic I could run into possible recovery and replication issues.

Is my understanding of this correct? Sorry for the lengthy explanation but I want to make sure my interpretation of the docs is accurate

Best Answer

Non-deterministic functions like NOW() are handled in statement replication by having a fake time record and other workarounds so that a replication stream will end up with a consistent slave as expected.

log_bin_trust_function_creators is about the procedures/function really being deterministic when they are marked as such. It can be set globally rather than giving SUPER permissions.

Replication format has tradeoffs however if you aren't doing bulk deletes probably a MIXED/ROW format will enable you do avoid a lot of the complications around triggers/procedures.

Related Topic