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:
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: duplicate, entry, error, message

[9 Sep 2009 7:21] Adam Miel
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:
test INT,
UNIQUE KEY(test));



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]
[9 Sep 2009 8:02] Valeriy Kravchuk
Thank you for a reasonable feature request.
[12 May 2010 14:06] Ryan Martin
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.
[23 May 2014 4:48] Hong Zheng
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.
[19 Jul 2018 7:52] Fungo Wang
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.
[23 Apr 2020 9:16] MySQL Verification Team
8.0.19 now shows more info.  Can this bug be closed?