MySQL Bugs: #57255: Cascade Delete results in "Got error -1 from storage engine"

Bug #57255	Cascade Delete results in "Got error -1 from storage engine"
Submitted:	5 Oct 2010 16:27	Modified:	30 Nov 2010 23:34
Reporter:	Walt Stoneburner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.51-community	OS:	Any (MS Windows 7, Linux)
Assigned to:	Jimmy Yang	CPU Architecture:	Any
Tags:	-1, 1030, cascading, constraints, depth, engine, error, foreign, hy000, key, MAX, regression, storage

Description:
MySQL is reporting:
ERROR 1030 (HY000): Got error -1 from storage engine

The error log is reporting:
InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 250

The problem is that the schema doesn't allow for recursive or circular references, and the data is trivial. Plus this problem did not happen in 5.1.42-community with the identical database, literally.

How to repeat:
Have three tables, A, B, and C. Each has a integer primary key, A_id, B_id, C_id.

B has a foreign key, fk_A_id, to A. C has a foreign key, fk_B_id, to B. Both keys are set to CASCADE DELETE.

I've been able to find a trivial case that works repeatedly for me.
Table A has one row.
Table B has two rows, fk_A_id references Table A's row.
Table C has 257 rows that have a fk_B_id referencing Table B's first row.
Table C has 486 rows that have a fk_B_id referencing Table B's second row.

At that point one just does this:
mysql> DELETE FROM A where A_id = 1;
ERROR 1030 (HY000): Got error -1 from storage engine
mysql> DELETE FROM C where fk_B_id = 2;
Query OK, 486 rows affected (0.01 sec)
mysql> DELETE FROM A where A_id = 1;
Query OK, 1 row affected (0.01 sec)
...to which at this point tables A, B, and C are all empty due to the cascade.

Looking at the mysql error log, this message appears at the time we get the ERROR 1030:
InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 250

What's interesting is that the max depth ought to be just two, from A to B to C.
No tables loop back to themselves nor create a circular referential path.

Experiments with other sets of data show that:
mysql> DELETE FROM A;
...works for small sets of data (hundreds of rows if there's little data in table C for each of them).

The problem seems to be quantity based.

Suggested fix:
The speculation I'm coming to is that a count isn't being reset as it visits each cascading child. Thus, it's counting rows rather than cascade hops between tables. But that's just a guess.

Thank you for the report.

Verified as described. Versions 5.0 and 5.6.99 are not affected. Plugin is affected.

Test case for MTR:

--source include/have_innodb.inc

create table A(id int not null primary key) engine=innodb;
create table B(id int not null auto_increment primary key, f1 int not null, foreign key(f1) references A(id) on delete cascade) engine=innodb;
create table C(id int not null auto_increment primary key, f1 int not null, foreign key(f1) references B(id) on delete cascade) engine=innodb;

insert into A values(1), (2);

--disable_query_log
let $i=257;
while ($i)
{
insert into B(f1) values(1);
dec $i;
}
let $i=486;
while ($i)
{
insert into C(f1) values(2);
dec $i;
}
--enable_query_log
--disable_abort_on_error
DELETE FROM A where id = 1;
DELETE FROM C where f1 = 2;
DELETE FROM A where id = 1;

Probably related to the fix of bug#54582.

Yes, it is related to #54582. We introduced fk_cascade_depth counter to prevent a running out recursive call to row_update_cascade_for_mysql(). This counter is initialized and reset in row_update_for_mysql(). Apparently, we should reset this counter when it comes out of recursive call immediately, so it does not accumulated with checks on different rows (as in this case) under a single  row_update_for_mysql().

Added to change log:

InnoDB incorrectly reported an error when a cascading foreign key
constraint deleted more than 250 rows.

Confirmed as introduced while upgrading from 5.1.50 to 5.1.51
In our setup this hits the daily dbmail-cleanup

Repairing DBMAIL physmessage integrity...
Ok. Found [2] unconnected physmessagesWarning: could not delete orphaned physmessages. Check log.

InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 250
Please drop excessive foreign constraints and try again

I just came across this too and want to be clear - is it a 'pretend' error, or are the cascades ignored/rolled back?

Fortunately it was on a disposable slave which I downgraded to 5.1.50 and reloaded.

I don't think this bug is "pretend" at all.  It would not let me do important DELETE operations, aborting them and rolling back the transaction.

It's my personal opinion that this is actually quite serious to have in production, and I'm eagerly awaiting the fix to be rolled out to the public.  In fact, its impact is so significant that deploying from http://labs.mysql.com/ has become an option.

Thanks for the clarification Walt, I agree that this can be a show-stopper for serious applications and hope it can be patched soon.

Can anybody put the fucking 5.1.52 on the servers?

> D.1.1. Changes in MySQL 5.1.52 (11 October 2010)
> Bugs fixed:
> InnoDB Storage Engine: InnoDB incorrectly reported an error when 
> a cascading foreign key constraint deleted more than 250 rows. 
> (Bug#57255)

It does not help anybody that the release is ready since more than two weeks when there is no download

Agree with previous speaker! This bug is a show stopper and makes 5.1.51 useless for many applications, including the one I'm working with. If there's a fix ready, why don't you get it out?

Is this the new way oracle will handle the mysql-community like opensolaris, openoffice not so long ago?

This damn bug is in a category where normally a urgent update should be pushed and not waiting weeks to place the download on the server even after the next version is finished

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)

Harald, Mattias, the date isn't the date that 5.1.52 was released. It's the date that a branch to prepare for testing and release was created, indicating that bug fixes produced after that won't normally be included in that version. Those tests, and adding fixes for any problems they find, take a while and we aren't going to ship an untested build.

5.1.52 was released on 1 November to all parts of the community, both those with support contracts and those without, though it can take a day or so for some mirrors to have a build available.

Prior to that there was the option to use a daily development build, to use an older release or to incorporate the patch and build yourself, as usual for open source projects. Or, for those with appropriate support contract, we'd do the build for someone ourselves if it was a critical production problem.

Sorry about the aggravation.

First THANK YOU
I deployed 5.1.52 last night and 9.000 old mails could be deleted

If this is not the release date this should not named "released"

This was a bug in a category where a mysql-5.1.51.1 would make sense because it was a real show stopper, the deletion of old mails is not the only problem, you could not even delete a user / domain at dbmail

Harald, thanks for confirming that it's worked for you. I've passed on your suggestion to our documentation team lead. I agree that it would be good to be more explicit about what the dates mean and perhaps to give both.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)