MySQL – Master/Slave Desynchronization Issues

Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G

For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'. This indicates that you should look up the value 174465 in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

• If Seconds_Behind_Master is NULL

  • Replication is Broken : Look for Tell-Tale Signs
  • If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error

• If Seconds_Behind_Master is a Number

  • Replication is running
  • When Seconds_Behind_Master > 0, Replication is Catching Up.
  • When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

• If Seconds_Behind_Master is NULL

  • Replication is Broken : Look for Tell-Tale Signs
  • If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error

• If Seconds_Behind_Master is a Number

  • Replication is running
  • When Seconds_Behind_Master > 0, Replication is Catching Up.
  • When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

• In a MySQL Master/Slave replication, what would happen if I write to the Slave?
• Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working
• MySQL replication problem: slave has become out of sync

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.