| 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: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.70, 5.1.30, 6.0.9 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[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. | +---------+------+---------------------------------------------------+
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