Unsafe statement written to the binary log

  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    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
    Last edited by thecrazylegs; 02-27-2014, 11:32 AM.


    • #17
      Originally posted by przemek View Post
      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.
      Last edited by mwild8; 05-02-2014, 11:07 AM.


      • #18


        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..
        Last edited by TomD; 06-30-2014, 10:38 AM. Reason: Spam links removed from end of message


        • #19
          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?


          • #20
            @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.