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?

Answer 1

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

Answer 2

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.