MySQL Bugs: #3443: Better foreign key constraint error messages

Bug #3443	Better foreign key constraint error messages
Submitted:	12 Apr 2004 2:19	Modified:	23 Sep 2005 17:53
Reporter:	Matthias Urlichs	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	All	OS:
Assigned to:	Osku Salerma	CPU Architecture:	Any

Description:
The error

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

doesn't say which row contains the bad reference. That makes debugging the application needlessly difficult.

How to repeat:
obvious ...

Suggested fix:
Add the column and database.table names. For example:

ERROR 1216: Cannot add or update child row(BAR,BAZ) in table TEST.FOO: the foreign key constraint fails

I like to extend the "suggested fix":

Suggested fix:
Add the column, database.table, and _constraint_ names.

For example:
ERROR 1216: Cannot add or update child row(BAR,BAZ) in table TEST.FOO: the
foreign key constraint BARBAZCONSTR fails

Currently it's very cumbersome to debug those messages. I'd vote for increasing the priority of this feature request.

I vote for this too. When you're loading many rows of data and get this message, not knowing which constraint, let alone which row!, makes this very difficult to track down!

Having just done a lot of work with referential integrity in MySQL once again I vote to increase the priority of this request. Everytime we get this error we have to waste a lot of time trying to work out what caused the error. Put it this way: Everytime we hit this bug it costs us money!  We need something like Oracle where it actually tells you what constraint is violated.

Im voting on this one also, I got this error message an hour ago and haven't yet been able to find the constraint. This is probely due too that Im working on a database where I do not have the initial SQL create statements available. It would be very nice if the error message could give a hint on what constraint that causes my inserts to fail.

Kristoffer,

please note that

SHOW INNODB STATUS

prints a detailed description of the latest FOREIGN KEY error.

Regards,

Heikki

Umm, if you already have the error textually, then passing it along to the connection's error handler shouldn't be too difficult ...

Anyway, MySQL is a multi-user database. "The last foreign key error" may not always be particularly helpful, especially when the error message is caused by a legacy application and/or logged somewhere, and can only be analyzed hours later. Been there, done that. :-/

Taking this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29797

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30251

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30267

Pushed to 5.0.14.

Noted in 5.0.14 changelog.

My web hosting provider runs a 5.0.67 MySQL database and this bug seems to be present in that version. May somebody check whether the current production release has this bug or not?

Thx!