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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | CREATE TABLE `t1` ( `t1_c1` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE ) ENGINE=InnoDB; [revin@forge rsandbox_5_5_17]$ for int in {1..2000}; do ./master/use test -e "insert into t1 values($int)"; done [revin@forge rsandbox_5_5_17]$ ./master/use test -e "insert into t2 select * from t1" master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 2000 | +----------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 2000 | +----------+ 1 row in set (0.00 sec) |
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.
1 2 | master [localhost] {msandbox} (test) > DELETE FROM t2 WHERE t2_c1 BETWEEN 201 AND 400; Query OK, 200 rows affected (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 | master [localhost] {msandbox} (test) > DELETE IGNORE FROM t1 WHERE t1_c1 BETWEEN 301 AND 500; Query OK, 100 rows affected, 1 warning (0.00 sec) master [localhost] {msandbox} (test) > SHOW WARNINGS \G *************************** 1. row *************************** Level: Error Code: 1451 Message: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE) 1 row in set (0.00 sec) |
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.
1 2 3 4 5 6 7 | master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 1900 | +----------+ 1 row in set (0.00 sec) |
Now let’s check the slave.
1 2 3 4 5 6 7 8 9 | [revin@forge rsandbox_5_5_17]$ ./node1/use test ... slave1 [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 2000 | +----------+ 1 row in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | [revin@forge rsandbox_5_5_170]$ ./master/use test ... master [localhost] {msandbox} (test) > SELECT @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > DELETE FROM t2 WHERE t2_c1 BETWEEN 201 AND 400; Query OK, 200 rows affected (0.00 sec) master [localhost] {msandbox} (test) > DELETE IGNORE FROM t1 WHERE t1_c1 BETWEEN 301 AND 500; Query OK, 100 rows affected, 1 warning (0.01 sec) master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 1900 | +----------+ 1 row in set (0.00 sec) [revin@forge rsandbox_5_5_170]$ ./node1/use test ... slave1 [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 1900 | +----------+ 1 row in set (0.00 sec) |
* 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.
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!