Bug #32468 delete rows event on a table with foreign key constraint fails
Submitted: 17 Nov 2007 14:39 Modified: 6 Feb 2008 10:01
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version: OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[17 Nov 2007 14:39] Andrei Elkin
Description:
While the master manages to delete a record, see how to repeat, the slave
fails.

How to repeat:
./mysql-test-run.pl --mysql=--binlog-format=row --start-and-exit rpl_row_basic_11bugs

connection master;

create table ti1 (b int primary key) engine = innodb; create table ti2 (a int primary key, b int, foreign key (b) references ti1(b))      engine = innodb; set @@session.binlog_format=row; insert into ti1 set b=1;insert into ti2 set a=1, b=1;

set foreign_key_checks=0;
delete from ti1 /* must be ok */;

connection slave;
show slave status\G =>

                   Last_Errno: 0
                   Last_Error: Error in Delete_rows event: error during transaction execution on table test.ti1. 

Suggested fix:
Analysis of the slave sql thread state shows that the thread sets
OPTION_NO_FOREIGN_KEY_CHECKS bit too late and the option has not become effective
for innodb engine.

The patch is prepared and is being committed.
[20 Nov 2007 16:03] Andrei Elkin
The patch is coming with fixes for Bug #31552.
[5 Feb 2008 13:03] Bugs System
Pushed into 5.1.24-rc
[5 Feb 2008 13:08] Bugs System
Pushed into 6.0.5-alpha
[6 Feb 2008 10:01] Jon Stephens
Documented in the 5.1.24 and 6.0.5 changelogs as follows:

        When using row-based replication, deletes from a table with a
        foreign key constraint failed on the slave.
[6 Mar 2008 13:13] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[31 Mar 2008 16:29] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.