MySQL Bugs: #41754: 'Cannot add or update a child row: a foreign key constraint fails' on SBR+Innodb

Bug #41754	'Cannot add or update a child row: a foreign key constraint fails' on SBR+Innodb
Submitted:	26 Dec 2008 13:03	Modified:	24 Mar 10:44
Reporter:	Philip Stoev
Status:	Duplicate
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.0.70, 5.1.30, 6.0.9	OS:	Any
Assigned to:	Andrei Elkin	Target Version:	5.1+
Triage:	Triaged: D2 (Serious) / R5 (Severe) / E5 (Major)

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

Thank you for the report.

But where is test case for bug #39440 is located?

The actual test case is under bug 41395.  I am sorry for the confusion, they are related
bugs with very similar stack traces.

Thank you for the feedback.

Verified as described.

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?

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.

Inserting into more than one autoincrement tables is unsafe for SBR as bug#19630 stated.

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.

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. | 
+---------+------+---------------------------------------------------+