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.
Best Answer
The Error Message you received
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:
I have addressed something similar (
error 1032
in my answer to MySQL master master replication, safe to delete rows on both masters?) where doing aDELETE
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,
MIXED
and SlaveROW
. At the very least, avoidSTATEMENT
on the Slave if the Master is notSTATEMENT
.