EmergencyEMERGENCY? Get 24/7 Help Now!

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

 | February 2, 2012 |  Posted In: Insight for DBAs

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Jervin Real

As Senior Consultant, Jervin partners with Percona's customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.

One Comment

  • 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!

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.