Unsafe statement written to the binary log

MySQL & MariaDB Other MySQL® Questions
21

Hi All,

I’m getting this alert in my mysql-error.log file that, can you please suggest what action i have to take.
2015-11-03 00:00:02 4378 [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: UPDATE city SET city.active_day = ‘2015-11-03 00:00:00’ WHERE city.code = ‘DELHI’ AND city.active_day IS NULL LIMIT 150.

22

Please help me on this, its urgent.

23

Hi Ranjan;

You could either modify the query to be deterministic (add an ORDER BY), or change the binlog_format to either MIXED or ROW.

[url]http://dev.mysql.com/doc/refman/5.5/en/replication-features-limit.html[/url]
[url]http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html[/url]
[url]http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html[/url]

-Scott

24

Thanks Scott for your advise, am checking if possible i will add order by on update statement, if not i will change the binlog format=MIXED or ROW.

25

Hi Scott, after addition of order by also i’m getting the error. Please advise, it is necessary to change the binary format to MIXED.
2015-11-06 00:00:24

26

Hi Team, Also i have requirement to upgrade mysql 5.6.23 to 5.7 version, can you please provide me some documents to process this it would be better.

27

Hi Ranjan;

I would ORDER BY the PRIMARY KEY.

And the MySQL manual has a guide for upgrading. Make sure to read all the details and test thoroughly in development environment:
[url]https://dev.mysql.com/doc/refman/5.7/en/upgrading.html[/url]

-Scott

28

Hi, I changed my my.cnf today on the base of Percona recommendation. Everything is working fine, however I get warnings in my error log as follows:

25630 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT… ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO wp_options (option_name, option_value, autoload) VALUES (‘_transient_wc_products_will_display_321_1524575950’, ‘1’, ‘no’) ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

There are a lot of them basically for every databases. What could I do to handle this?