Description:
A system I use runs the following statement in SBR mode which generates the following warning on a slave using SBR:
2016-04-30T18:00:16.209112Z 1 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM `db`.`some_table` WHERE timestamp_column < NOW() - INTERVAL 2 HOUR ORDER BY id LIMIT 5000
The warning is of course correct but I do not care. I understand the risks of a statement such as this generating a change in the slave if master and slave are out of sync. Given the id column is a primary key then if the master and slave have the same content this is completely safe. (really the warning here is not 100% correct.)
I'd like therefore to be able to provide a query hint to turn off this warning as I know I do not mind. I have several other "unsafe warning" messages on different systems which for similar reasons are not a concern and having a way of avoiding these warnings which populate the logs with "noise" would be most welcome.
Sometimes this happens for thousands or tens of thousands of statements and I've seen log files grow to several GB because of this sort of warning.
How to repeat:
Run a similar query and see that it generates "unsafe warning messages" in SBR mode.
[ Switching to RBR might help but that assumes that I want to do that and want to do it now. Switching to RBR requires other checks to be made etc and may not be appropriate for a number of reasons. ]
# from today's log file
20:44:10 [myuser@host ~]$ sudo grep -c Unsafe /var/log/mysqld.log
768
# version of MySQL being used.
20:44:23 [myuser@host ~]$ rpm -q mysql-community-server
mysql-community-server-5.7.11-1.el6.x86_64
Suggested fix:
I have spoken to several people asking for a better way to filter this type of statement using configuration settings (global variables) but nothing has happened. Given the number of possible ways of doing this and being flexible makes this quite hard perhaps it's better to do this with the query hinting infrastructure which would potentially allow me to change the behaviour for specific queries which I generate. That would give me absolute control, allowing the current behaviour of warning about "possible problems", while I can turn this off if I deem that is appropriate.
So perhaps something like:
DELETE FROM `db`.`some_table` /*+ no_unsafe_warnings */ WHERE timestamp_column < NOW() - INTERVAL 2 HOUR ORDER BY id LIMIT 5000
might be appropriate. In another bug report I asked for the grammar used for the query hinting to be defined more formally and if you add new hinting options like this then that would make more sense to ensure that any new options are added in a 100% consistent manner.
© 2025, Oracle Corporation and/or its affiliates