MySQL: Foreign key constraints that exceed max depth

Question

I have MySQL Server 5.1.62 installed on production server. I am monitoring mysql server's error log file every day and suddenly I found below error in my error log file.

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 have a database structure with primary key - foreign key relationships with proper update/delete actions and I need to delete data of child tables if the data in parent table deleted by application or manually (backend).

I had googled this issue but I can't find proper solution. How can I resolve this issue?

Can you post your schema? I suspect you have a loop in your foreign key constraints. You don't have to post the entire tables, just the columns that are mentioned in foreign key settings. — Barmar, Dec 24 '12 at 21:36
I agree with @Barmar here. Your db schema might have a circular foreign key constraint causing the error on delete. We need to see the table+column schema to be more helpful. — Tanzeel Kazi, Dec 26 '12 at 04:57
What changed right before you started seeing this error message? — Mike Sherrill 'Cat Recall', Dec 26 '12 at 12:59
Deleting multiple rows from tbl_indentmaster and because of cascade delete data from all child tables were deleted so I found this error — Saharsh Shah, Dec 26 '12 at 13:01
I don't mean that kind of change. What changed in the db schema or in the server configuration just before you started seeing this error message? — Mike Sherrill 'Cat Recall', Dec 26 '12 at 13:09
No, haven't done any changes. Deleteing some old data which were not in use — Saharsh Shah, Dec 26 '12 at 13:14
It's really hard to figure it out from the diagram, text would be better. All we need are the table names and the `references` clauses. — Barmar, Dec 26 '12 at 13:36
In my schema design all the relationship created with cascade delete. — Saharsh Shah, Dec 26 '12 at 14:55
There doesn't seem to be a recursive constraint related to `tbl_indentmaster` in your diagram. What's the query you are trying to run? — Tanzeel Kazi, Dec 27 '12 at 08:54
I am using simple delete query. **DELETE FROM tbl_indentmaster WHERE indentId IN (1,2,3,4,5,6,...,150,151,155,156,....)**. — Saharsh Shah, Dec 27 '12 at 09:14
@SaharshShah Do you get the same error if you delete just _one_ `indentId`? — Tanzeel Kazi, Dec 27 '12 at 09:19
No, It will run for small amount of data. But it gives error if I want to delete more data — Saharsh Shah, Dec 27 '12 at 09:33

Devart · Answer 1 · 2012-12-14T07:58:09.937

3

Have a look at this link - Cascade Delete results in "Got error -1 from storage engine". There is a suggestion.

Also, as a solution you may try to do it without ON DELETE CASCADE option, just use DELETE statement that removes records from some tables (multiple-table syntax).

edited Dec 14 '12 at 07:58

answered Dec 14 '12 at 07:22

Devart

119,203
23
166
186

I had already used ON DELETE CASCADE and I am getting the error when I execute DELETE Query. This is not the solution of my question – Saharsh Shah Dec 14 '12 at 07:41
exceeding is when you have too much ... this can't be solved by adding more – Sir Rufo Dec 14 '12 at 07:41
I had checked that link but not find exact solution for this situation – Saharsh Shah Dec 14 '12 at 09:23

Mike Sherrill 'Cat Recall' · Answer 2 · 2012-12-26T13:46:54.557

The picture of a schema isn't very useful, because it doesn't show any cascading declarations. For example, if deletes are supposed to cascade from tbl_indentmaster to tbl_tepdetails, but deletes are not supposed to cascade from tbl_tepdetails to tbl_tepnoting, then then I'd expect some deletes to fail. (But with a different error message.)

If there is a circular referential constraint that's causing this, I'd expect it to be caused in part by a cascading reference from tbl_indentmaster to tbl_tepdetails. You might want to try dropping that foreign key constraint for testing. Do that on a tset sserver, not on the production server.

If this started suddenly, and your database worked correctly before, I'd first think about

restoring the database from backup, or
restoring the schema from backup, and reloading the current data, or
checking out the current version and rebuilding the database. (You do have the database schema under version control, don't you?)

I'll assume you don't have a good backup, and that you don't have your schema under version control.

Are you starting with a good database? Run mysqlcheck. Read that documentation carefully. Don't --repair before you have a tested, good backup.

Assuming that your database is good, that cascading deletes ought to work correctly in your database, and that your Google skills are good, I think your best start is to

install MySQL 5.5 or 5.6 on a test server,
load your database onto that test server, and
see whether you can reproduce that specific error.

To load your database onto the test server, dump the contents using mysqldump. Don't copy files at the filesystem level--one or more of them might be corrupt.

Although this might not resolve your issue, it might tell you exactly where the issue is. If it works correctly, you know the problem is probably related to the server version, and that it might be resolved with a version upgrade.

I have updated mysql version to 5.5 but still I am facing the same issue — Saharsh Shah, Dec 26 '12 at 11:15
In my schema design all the relationship created with cascade delete. And till mow i am not deleting any rows from db but now i want to delete past data so i got this error — Saharsh Shah, Dec 26 '12 at 14:54
Did you drop the foreign key constraint between tbl_indentmaster and tbl_tepdetails on a test server, then try to delete rows from tbl_indentmaster? — Mike Sherrill 'Cat Recall', Dec 26 '12 at 16:08
It will work if i remove constraint from db. But i can't do that. As per my understanding the error occurs because of exceed depth of constraint as if i delete 50 rows from indentmaster table it automatically deletes more than 1000 rows from different tables so it crosses the depth of cascade delete — Saharsh Shah, Dec 26 '12 at 17:40
Your understanding is wrong; the number of rows is irrelevant. The problem is *probably* related to the number of tables you're cascading the delete to. And that's *probably* because you have a circular reference. (The delete cascades back to the table it started in.) If it works when you drop the FK constraint between tbl_indentmaster and tbl_tepdetails, put *that* constraint back, and drop the FK constraint between tbl_tepdetails and tbl_tepmapbidder. Repeat until you isolate the problem, which I expect will be a circular FK reference. — Mike Sherrill 'Cat Recall', Dec 26 '12 at 17:48

score 1 · Accepted Answer · edited Jun 19 '13 at 10:05

I agree with the original answers by @Devart and @Catcall here but I'd like to add a few things after exchanging a few comments with the OP.

First, I have reduced the schema image representation to only the tables that are affected by a DELETE query on tbl_indentmaster.

From what I could see there are no circular FK references in this schema diagram.

Also, the OP ran the following query:

DELETE FROM tbl_indentmaster WHERE indentId IN (1,2,3,4,5,6,...,150,151,155,156,....)

That's an aweful lot of rows to delete. On enquiring further the OP claims that the query works for smaller subsets of indentId's.

From this I think we can take two possibilities:

There's a bug in MySQL (highly unlikely but possible) which causes large queries with CASCADE DELETE like yours to fail. Note I am suggesting the possibility of a new bug not the one [posted already][2]. Ideally the number of rows to delete should not matter.
There is a particular indentId entry within tbl_indentmaster which is causing the entire query to fail.

I'd suggest that you first try to diagnose the issue considering point (2) is the actual culprit. You can break the DELETE query into smaller chunks and find the offending id's.

If this script is something that has to be periodically executed through code (in a larger application) then you should consider executing the query in smaller chunks there as well (probably 15 id's per query is a good start IMO). In addition to doing this I'd suggest logging errors with offending id's in a log file so you know exactly which entries are failing.

I have already grouped sata in small chunks and deleted it. I want to fing root cause of this error. I also think this is limitation of mysql. — Saharsh Shah, Dec 27 '12 at 12:15

MySQL: Foreign key constraints that exceed max depth

3 Answers3