1

As far as I understand based on my research, a very simple update query like:

UPDATE someTable SET status = 10 WHERE id=123;

will fill the error log with bogus warnings if the query invokes triggers, and statement based binary logging is turned on.

At the end of this bug report https://bugs.mysql.com/bug.php?id=45677 it says:

For 5.1, We mark all the statements that invoke a trigger or call a function that updated autoinc fields as unsafe and will switch to row-format in mixed mode. But in statement mode, we didn't emitted the warning. (sic)

For 5.5, we emitted the warning in statement mode, because a suppress warning feature is introduced.

In my understanding this means that whenever I update a row with an auto-increment column, and triggers are invoked, the queries the trigger runs will be marked as unsafe for replication in statement mode without further consideration. Is this correct? Why is it like that?

If the above is correct, how can I turn the warnings off for these specific queries only?

Please note, that switching to row based replication is not an option for me, because in my three tier replication model I have a blackhole engine in the middle. This kind of setup, I believe, cannot function with row based replication, and is a matter of another question of mine here: Row-row based logging in a blackhole replication filter setup?

I'm using Mysql 5.7

1

Just switch binary logging from statement based to mixed.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

Source: What is the safest way to switch the binlog format at runtime?

  • I believe you missed the last part of my question which explains that I cannot use row based replication with my setup. Your recommended mixed format would create statement and row based replication, too. – Istvan Dupai Jul 2 '18 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.