MySQL Bugs: #47207: Not enough information in error Duplicate entry xxx for key 1

Bug #47207	Not enough information in error Duplicate entry xxx for key 1
Submitted:	9 Sep 2009 7:21	Modified:	9 Sep 2009 8:02
Reporter:	Adam Miel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	duplicate, entry, error, message

Description:
Hello, it is maybe a minor notice but it is annoying for me
I use many MYSQL stored procedures and funcions in my applications, and when I get an error "Duplicate entry xxx for key 1" it is sometimes difficult to get known in which table it has happened (ideally it would be to show number of line in procedures but this would be more difficult I think)

I suggest to show info abiut table/index name in this error, for example:
Duplicate entry xxx for key 1 ([index name]) in table [table]

How to repeat:
CREATE TABLE test (
test INT,
UNIQUE KEY(test));

INSERT INTO test VALUES(1);

INSERT INTO test VALUES(1);

I get: Duplicate entry '1' for key 1

Suggested fix:
I suggest to show info abiut table/index name in this error, for example:
Duplicate entry xxx for key 1 ([index name]) in table [table]

Thank you for a reasonable feature request.

I've run into the same issue with duplicate key errors that are caused by triggers. The error message does not make it clear whether the duplicate key is in the main table, or in one of the tables affected by the triggers. Prepending a table name to the index name in the error message would be very helpful, and a mention of the function, procedure, or trigger responsible would also aid diagnosis.

It's been almost 5 years since this issue was reported. I am using 5.5.28 in production. We have been seeing Duplicate entry in our log files. Since we have so many tables, it's extremely difficult to find out of which table the constraint was violated. I think the severity of this request should be raised.

Qualified table names in error msg will be very helpful to find out which table is duplicated.

Especially when there are tiggers involved, and duplicated happened for the triggering table. Usually the users may forget that they had created tigger long time ago, and complain mysql is insane, cause mysql keep throw out duplicate error and there is no duplicated key for the table they directly operating.

Btw, the facebook branch already has this feature https://github.com/facebook/mysql-5.6/commit/2c7f71758ca49bcd2433bea2fb15387b3fde65f6.

8.0.19 now shows more info.  Can this bug be closed?

See:
  https://bugs.mysql.com/bug.php?id=92530
  https://github.com/mysql/mysql-server/commit/045af147eb32a657c0c7a8d10abb08dcd6055332