Bug #41754 'Cannot add or update a child row: a foreign key constraint fails' on SBR+Innodb
Submitted: 26 Dec 2008 12:03 Modified: 24 Mar 2009 9:44
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.70, 5.1.30, 6.0.9 OS:Any
Assigned to: Assigned Account
Triage: Triaged: D2 (Serious) / R5 (Severe) / E5 (Major)

[26 Dec 2008 12:03] Philip Stoev
Description:
When executing the rpl_sys test with Innodb and statement-based replication, the slave thread terminates with:

Error 'Cannot add or update a child row: a foreign key constraint fails (`test`.`insdel2_sub`, CONSTRAINT `insdel2_sub_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `insdel2_tbl` (`id`) ON DELETE CASCADE)' on query. Default database: 'test'. Query: 'INSERT INTO test.insdel2_sub VALUES (NULL, NAME_CONST('ins_count',100), 'More text to test with')'

This is not observed with other replication types. Concurrency appears to be required as well.

error log:

081226 14:58:14 [ERROR] Slave SQL: Error 'Cannot add or update a child row: a foreign key constraint fails (`test`.`insdel2_sub`, CONSTRAINT `insdel2_sub_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `insdel2_tbl` (`id`) ON DELETE CASCADE)' on query. Default database: 'test'. Query: 'INSERT INTO test.insdel2_sub VALUES (NULL, NAME_CONST('ins_count',100), 'More text to test with')', Error_code: 1452
081226 14:58:14 [Warning] Slave: Cannot add or update a child row: a foreign key constraint fails (`test`.`insdel2_sub`, CONSTRAINT `insdel2_sub_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `insdel2_tbl` (`id`) ON DELETE CASCADE) Error_code: 1452
081226 14:58:14 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 19764105

How to repeat:
To reproduce, use the test case from  bug 39440 however run it with Innodb and SBR:

1. Unpack the ZIP from bug39440 so that a directory mysql-test/suite/bug39440 is created.

2. Run:

 perl mysql-test-run.pl \
  --start-and-exit \
  --mysqld=--innodb rpl_alter

 mysql -uroot \
  --socket=var/tmp/master.sock \
  -e 'set global binlog_format = "statement"'

 mysql -uroot \
  --socket=var/tmp/slave.sock \
  -e 'change master to master_port=9306 , master_host="127.0.0.1" , master_user="root"'

 mysql -uroot --socket=var/tmp/slave.sock -e 'start slave'

 engine_type=Innodb MTR_VERSION=1 perl mysql-test-run.pl \
--stress \
--extern \
--user=root \
--socket=var/tmp/master.sock \
--stress-init-file=bug39440_init.txt \
--stress-test-file=bug39440_run.txt \
--stress-suite=bug39440 \
--stress-test-duration=60000 \
--stress-threads=100
[29 Dec 2008 10:19] Sveta Smirnova
Thank you for the report.

But where is test case for bug #39440 is located?
[29 Dec 2008 11:07] Philip Stoev
The actual test case is under bug 41395.  I am sorry for the confusion, they are related bugs with very similar stack traces.
[31 Dec 2008 8:18] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[13 Jan 2009 16:11] David Dewar
Sorry to be a nuisance, but does anyone have a solutions to this yet? Replication works fine for me until someone performs an insert into a table with a foreign key constraint - then replication fails. Is there a way to work around this?
[16 Jan 2009 20:14] Andrei Elkin
Actually, the test indicates the problem in 5.1.29 as well, but does not in aelkin@mysql.com-20081002084223-6evhpt47cko8he59 and (most probably) earlier.
[20 Jan 2009 21:11] Andrei Elkin
Inserting into more than one autoincrement tables is unsafe for SBR as bug#19630 stated.
[16 Mar 2009 19:39] Andrei Elkin
Wrt a possible solution I am sorry to be able only to quote:

Bug #19630 comments [4 May 2007 7:03]
"This bug has been discussed numerous time. Lars and Jeffrey made the decision that we
will not fix this problem in 5.0 now because (a) it's too complicated a fix and (b) the
problem has been fixed in 5.1. Limitation has been documented. Bug cannot be escalated."

Upgrading to 5.1 and using the mixed replication format would be a
good enough solution. The mixed format forces the actual binlogging of the
two-or-more auto-inc inserts in the row-based format.
[24 Mar 2009 9:44] Andrei Elkin
Setting it as a duplicate of bug#19630.

The regression test uses stored functions that insert into an auto-inc
column so that in effect there can be inserts into at least 2
auto-inc columns per query. That is unsafe for binlogging. And the server warns
that.

E.g a query from the ddl part of the regression test

mysql> INSERT INTO test.insdel2_sub VALUES (null, NAME_CONST('ins_count',100), 'More text to test with');
Query OK, 1 row affected, 2 warnings (0.00 sec)

generates:

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. | 
| Warning | 1592 | Statement is not safe to log in statement format. | 
+---------+------+---------------------------------------------------+