5

We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between master and slave.

On master:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW

On slave:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: STATEMENT

Once in a while, the replication breaks with an error similar to the following:

Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448

We don't know for sure whether this was caused due to the different binlog_format setting between the master & slave.

Could the above error potentially be caused by this inconsistent setting? Should I change the slave to "row" based format? It is hard to reproduce the error so I am looking for the best practice we should be following and we are very new to MySQL.

2
  • Binlog format is user for binary log between master and slave. you can turn it off on slave while the error you shared is because of inconsistency of data for the table. Try to match count or sync the table and then start slave, then there should be no errors. Let know in case of more errors after syncing tables. Jun 22, 2015 at 16:18
  • What is the topology? If the Slave is not "Master" to some third machine, you won't have the binlog turned on, hence it's binlog_format won't matter.
    – Rick James
    Jun 22, 2015 at 23:09

1 Answer 1

2

The Error Message you received

Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448

This message can come up when doing a DELETE query with row-based replication. Unfortunately, it is one of the disadvantages to row-based replication:

  • Disadvantages of row-based replication

RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

I have addressed something similar (error 1032 in my answer to MySQL master master replication, safe to delete rows on both masters?) where doing a DELETE query was the issue.

In your case, since the Slave is using STATEMENT, the row change is expected to be a precise match of PRIMARY KEYs between Master and Slave. On many occassions, it may not be the case. That is why error 1032 exists.

To play it safe,

  • use binlog_format=ROW on both Master and Slave
  • use binlog_format=MIXED on both Master and Slave
  • use binlog_format=STATEMENT on both Master and Slave
  • You could experiment with Master MIXED and Slave ROW. At the very least, avoid STATEMENT on the Slave if the Master is not STATEMENT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.