2

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So I've written a script that does that. — Is it safe to run this script on both masters? It deletes data only. What I'm wondering is what will the MySQL replication module do, if the row it's about to delete has already been deleted? Will replication break, or is it safe to delete rows on both masters? Does it matter if we use row based or statement based replication?

Example: The log data deletion script deletes row 123 on Master A. At the same time, the script deletes that same row on Master B. Then master B reads its relay log, and sees: "I should delete row 123, because it was deleted on Master A and I'm its slave. Let's delete it... ... ...but it's already gone!" — now what will Master B do? Will it halt replication, as if there was a duplicate key error? Or will it think "Fine, I need do nothing", and everything is okay.

Improve this question
2
  • is there a reason not to run the script on one master and let replication handle the rest?
    – Derek Downey
    Apr 10, 2014 at 17:16
  • @DerekDowney It would be a bit simpler for us to have the same configuration on both masters. Also, the script would be a little bit less complex if it didn't need to think about if it runs on the active master or the passive master. — We do have one active master however, when it comes to other kinds of write requests, and one passive master (that writes nothing), as standby.
    – KajMagnus
    Apr 11, 2014 at 0:43

1 Answer 1

Reset to default
1

Your question already has the key to your answer. It does depend on row based or statement based replication.

STATEMENT-BASED REPLICATION

If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, no big deal. Replication will just carry on. It will just take you a lot longer to realize your data draft on the Slave (or in your case, either Master) if there were slight differences at all.

ROW-BASED REPLICATION

If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, that can break replication because the exact row info is embedded in the relay logs on slave. That row is expected to exist for the delete to happen. There is an error code for it:

sh-4.1# perror 1032
MySQL error code 1032 (ER_KEY_NOT_FOUND): Can't find record in '%-.192s'

You will likely see error log messages like these:

2014-02-27 22:03:00 4070 [ERROR] Slave SQL: Could not execute Update_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000166, end_log_pos 93114177, Error_code: 1032

2014-03-17 10:50:15 11596 [ERROR] Slave SQL: Could not execute Delete_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000337, end_log_pos 427881, Error_code: 1032

Here are other links that discuss this

For more insights, please read Advantages and Disadvantages of Statement-Based and Row-Based Replication from the MySQL Documentation.

Improve this answer
1
  • We use mixed binary log format, which seems to mean that MySQL defaults to statement based, but switches to row based according to the rather many rules outlined here: dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html — it seems too complicated to determine what MySQL will actually do, so I suppose we'll simply allow only one single master to delete old log data.
    – KajMagnus
    Apr 11, 2014 at 7:26

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.