Not the answer you need?
Register and ask your own question!

Unsafe statement written to the binary log

systemalisystemali AdvisorCurrent User Role Beginner
Hello Everyone,

I am not sure when and how did this happen, But i just happen do view my log file files and i was surprised to see it throwing up this errors continuously like this :-
tail -f /var/lib/mysql/Newdbsrv.err

130708 16:54:21 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... IGNORE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. Statement: create temporary table IF NOT EXISTS vt_tmp_u1098(id int(11) primary key) ignore (SELECT 1098 as id) UNION (SELECT vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN vtiger_role ON vtiger_role.roleid=vtiger_user2role.roleid WHERE vtiger_role.parentrole like 'H1::H2::H31::H32::%') UNION (SELECT groupid FROM vtiger_groups where groupid in (777,778))

As i am not sure what this is and what it means, I am seeking your kind and humble support.

I would also like to point out that my server was rebooted 48 hrs ago, could this be the reason for the same ?

I also have a replication server setup and it seems to be functioning normally.

would appreciate your kind revert into this.

Thank you every one !!

Comments

  • systemalisystemali Advisor Current User Role Beginner
    Hi Guys,

    I am just bumping this thread, just to gain some attention.

    I would appreciate some suggestions !!

    any one ?

    Thank you
  • systemalisystemali Advisor Current User Role Beginner
    Hello niljoshi and przemek,

    Since there was a glitch on the forums which had temporarily removed several posts over the last few days .

    Would you be kind enough to please repeat your response that you had provide me earlier as it is no more online :(

    Thank you so very much for your time once again.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    The error message it gives you is pretty good actually. Basically the statement that it is warning you about could potentially end up with different results when it runs on the slave versus when it ran on the master. This is something that a tool like Percona's pt-table-checksum (see below link) can help with to detect what is called slave drift or data drift, which is when the slave has different data than the master.

    What to actually do about this (other than check your tables with the tool I mentioned) depends on the situation. The query it is warning you about may end up working perfectly fine, but you would not know for sure without checking, and even then it may work sometimes and not others.

    The best option in my opinion is to switch to row based replication to avoid this, as then MySQL is replicating the data itself and not the statement. Row based replication has some downsides, mainly that more data is transferred, so slave lag may be more of an issue in extreme cases, so you'll want to read up on that first before making any changes.

    The other option would be to re-write the query to avoid any functions / formatting that results in unsafe queries for statement best replication (see below link).

    http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
    http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html
    http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html
  • systemalisystemali Advisor Current User Role Beginner
    Hello Scott,

    Firstly accept my appreciation for looking into my thread and paying heed to it.

    Secondly to just switch to "ROW" based replication, I can see that all i need to do is specify the command globally for all.
    like this :- mysql> SET GLOBAL binlog_format = 'ROW';

    ** NOTE :- i guess this can be done on the fly without any restart, am i correct ?

    Further, I was also going further through the doc link that you have provided with regards to ROW based replication and i could also see it ALSO mentions about "Mixed replication (statement and row based replication)". Could you suggest if this is also a viable option ? :-

    http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html

    would you clear the sky on this too ?

    Once again, Thank you for every thing :)
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Mixed format does pretty much what it sounds like, but I've never used it myself and have not seen many other people using it. So that choice is up to you, but I would make sure to research / test it first.

    The binlog_format variable is dynamic, so you may change it at any point. The cleanest way would be to set it in your my.cnf on the master and restart the server, otherwise you can stop replication on your slave, change the setting dynamically on the master, and then start replication on your slave so that it picks up the change since a change in global variables only applies to new connections.
  • systemalisystemali Advisor Current User Role Beginner
    Fantastic,

    I'll have this done in the coming week the very first thing and shall provide an update on it :)

    Thank you so very much,

    Seriously, you have taught me many a things i would have not known :) !!!!
  • przemekprzemek Percona Support Engineer Percona Staff Role
    Hi,

    I'm sorry about those replies being lost.
    I can now only add that the MIXED mode is a good compromise between statement and ROW, and will work for the statement you had in warning message. Basically, for any data update that is "unsafe" from replication point of view, MySQL will log all changed rows values instead of the original statement. But it will keep logging other updates the same way as in statement mode.
  • systemalisystemali Advisor Current User Role Beginner
    Thank you for your revert once again przemek,

    Sincerely appreciate it,

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

    Thank you,
  • systemalisystemali Advisor Current User Role Beginner
    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 :)
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.

    http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
  • systemalisystemali Advisor Current User Role Beginner
    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!!
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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:
    http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html

    The fact is, that starting from MySQL 5.6, ROW based replication works much better then in previous versions.
  • MarkRMarkR Entrant Inactive User Role Beginner
    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...
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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.
  • thecrazylegsthecrazylegs Entrant Current User Role Beginner
    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: 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
  • mwild8mwild8 Entrant Inactive User Role Beginner
    przemek wrote: »
    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.

    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.
  • kumraldemircankumraldemircan Entrant Inactive User Role Beginner
    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..
  • kubilaykubilay Entrant Inactive User Role Beginner
    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?
  • yogesh777yogesh777 Contributor Current User Role Beginner
    @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.
  • RanjanRanjan Entrant Current User Role Beginner
    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.
  • RanjanRanjan Entrant Current User Role Beginner
    Please help me on this, its urgent.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.

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

    -Scott
  • RanjanRanjan Entrant Current User Role Beginner
    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.
  • RanjanRanjan Entrant Current User Role Beginner
    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
  • RanjanRanjan Entrant Current User Role Beginner
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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:
    https://dev.mysql.com/doc/refman/5.7/en/upgrading.html

    -Scott
  • mirtillionmirtillion Entrant Inactive User Role Beginner
    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?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.