July 28, 2014

STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

DELETE IGNORE suppresses errors and downgrades them as warnings, if you are not aware how IGNORE behaves on tables with FOREIGN KEYs, you could be in for a surprise.

Let’s take a table with data as example, column c1 on table t2 references column c1 on table t1 – both columns have identical set of rows for simplicity.

An expected behavior for DELETE IGNORE is that if the statement fails to delete all rows, none should be deleted at all, after all this is InnoDB right? Wrong, take a look at bug 44987. As stated on the bug, only 5.0 exhibits the above mentioned behavior on 5.1 and 5.5, MySQL will stop deleting rows if it encounters constraint errors.

To demonstrate on 5.5.17:

I delete some rows from t2 so some rows on t1 does not have a constraint.

Now I try to DELETE IGNORE rows 301 to 500 on t1, note rows 301 to 400 does not have any existing constraints from t2 as we deleted them from above.

As expected a warning is generated because rows 201 to 300 on t1 still has referencing foreign keys from t2. However, 100 has been deleted! Let’s see.

Now let’s check the slave.

Uh oh, now the slave is out of sync, because the statement failed to delete all intended rows it was not written to the binary log and consequently not reaching the slave.

So how can you workaround this? Simple, 1) do not use IGNORE, be critical about your data 2) use ROW* based replication. When using the latter, MySQL will log separate statements for each row that is deleted – so if the first 100 rows was successfully deleted then those 100 events are logged and eventually replicated.

* MIXED mode will not work since the query in this example will be considered STATEMENT (http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html) thus failure to execute the query successfully means it will not get logged.

About Jervin Real

Jervin is a member of Percona's Rapid Response Consulting team. When you come to Percona for consulting, chances are he'll be greeting you first. His primary role is to make sure customer issues are handled efficiently and professionally. Jervin joined Percona in April 2010.

Comments

  1. Jonathan Valerio says:

    Most of us wasted our time looking for the root cause of slaves being out-sync from master and this can be one of the possible cause we always miss. Thanks for sharing!

Speak Your Mind

*