Search code examples
mysqlforeign-keyssql-deletecascading-deletes

MySQL: Foreign key constraints that exceed max depth

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?

Solution

  • 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:

    1. 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.
    2. 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.