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