Unsafe statement written to the binary log

Thank you for your revert once again przemek,

Sincerely appreciate it,

I shall post back once i have done the changes tomorrow !!

Thank you,

Hello Scott,

I have gone ahead and used the mixed mode replication and it has been more than 6 hours and both the servers seem to be doing fine with NO errors being reported et all on the Master Server.

Thanks to you and przemek, i find this issue to be resolved.

Thank you so so very much :slight_smile:

Nice work! Just make sure to check for slave drift occasionally using a tool like pt-table-checksum since you are still using statement based replication with the mixed format.

[URL]http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html[/URL]

Scott,

My apologies for the delayed turnaround,

You mean to say “statement based replication” should NOT be used with mixed mode replication or NOT be used et all, Instead always use “row based replication”, is this more safer and should this be preferred over “statement” ?

Can you please clarify this,

I think i am doing a merry-go-round here :D, apologies for that!!

In “MIXED” mode, replicated transactions are written in either of two formats: row based logging, or statement based login. MySQL decides to use statement or row based log entry if it considers a given statement is “safe” or not. So “safe” updates will be logged just like in the STATEMENT binlog format, and “not-safe” will be logged as row-based events. This way the MIXED mode is more safe then STATEMENT from data integrity point of view. It’s just a compromise between pure STATEMENT or pure ROW modes.
Now, whether to use pure ROW format, we can argue, as there are many pros and cons of this solution, take a look here:
[url]http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html[/url]

The fact is, that starting from MySQL 5.6, ROW based replication works much better then in previous versions.

A bit off topic I guess, but I wonder, why does pt-table-checksum requires STATEMENT based binlog? It fills up my MySQL error log with warnings about unsafe statements…

It’s quite simple, in order to calculate checksum numbers for data in order to later compare them between master and slave, those calculations have to be done separately on master and slave. With ROW binary format, the slave is only applying row changes from master, so the checksum table would be the same. Yet we need statements replicated to slaves, not data. But that’s just per pt-table-checksum session setting, all the rest traffic goes via globally set binlog format.

Hello
Sorry for my english (Google Translate)

I have a big problem, I migrate to 5.6 and since impossible to DELETE an entire table.

I get an error: [COLOR=#FF0000]ER_BINLOG_ROW_LOGGING_FAILED :: Writing one row to the row-based binary log failed

binlog_format ROW
max_binlog_cache_size 18446744073709547520
max_binlog_size 104857600
max_binlog_stmt_cache_size 18446744073709547520

If I change the binlog_format in MIXED I can do, my host said that there will be problem with Multi Master replication

HELP !!! please

Just to be clear, pt-table-checksum is setting its SESSION level binlog_format to STATEMENT and not GLOBAL? I noticed that the global setting is still MIXED when running “SHOW GLOBAL VARIABLES LIKE ‘binlog_format’;” while pt-table-checksum is running “nohup pt-table-checksum --no-check-binlog-format --empty-replicate-table > /dev/null 2>&1 &”

Why is this? Is this explained in more detail somewhere? What is the issue exactly? Because filling up the log with these warnings doesn’t seem like correct behaviour.

hi

my err : ER_BINLOG_ROW_LOGGING_FAILED

i try to do one row to the row-based binary log failed

my hosting company said that its software problem

my problem same with thecrazylegs user post

thanks your suggestions…

Well I understand what you are saying Przemek. I understand that it has to replicate the statements to the slaves, in order to re-run them there, and not the data. That is fine. But why it doesn’t run when you have a MIXED binlog_format on the master and the slaves? It is asking me to put the –no-check-binlog-format option on pt-table-checksum script. When I put the options it does run and does checksum both server and slaves which are in MIXED mode. But unfortunately it requires that the user has the SUPER privilege. Because it needs that to set the binlog_format to STATEMENT at run time. Is this really necessary?

@przemek, how mysql 5.6 handles or decide whether the statement is safe or unsafe for replication when GTID is enabled and of course GTID requires ROW bin log format enabled.

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.

Please help me on this, its urgent.

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

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.

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

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.

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

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?