Tag: 250

MySQL Cascade Delete Problem

MySQL 5.1.51 introduced a nasty little bug that has the potential to really cause some production servers some ill — cascading delete and updates can sometimes fail if you’re dealing with “too much” data at once. Where “too much” is a relatively small amount. You may get bit if you have a one-to-many relationship.

A note of warning to MySQL users using 5.1.51, you may want to downgrade to 5.1.50 for a little bit.

There’s a problem with 5.1.51 in which cascading deletes or updates throw an error. Not good if you have constraints and one to many relationships.

ERROR 1030 (HY000): Got error -1 from storage engine

The mysql error log will say something like:

InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 250

This is a confirmed bug in MySQL and is repeatable. The error will cause a transaction rollback. Not good news for people running production systems.

See MySQL Bug #357255.

While the problem was quickly identified and apparently resolved, as the defect report is closed, it does not look like the 06-Oct-2010 change has made it out to the production baseline as of the time of this writing.

Additionally, the MySQL pre-release snapshots on labs.mysql.com show there is a mysql-5.1.52 pending with a September date, and this hasn’t made it to general production yet.

That leaves one to speculate that the fix will appear in the 5.1.53 version, and we won’t be seeing that for a month or two. Yikes.