Bug #35487 Duplicate Entry For Key error reports truncated key value
Submitted: 21 Mar 2008 18:14 Modified: 28 Mar 2008 14:37
Reporter: Mark Ostrum Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.37-community OS:Linux
Assigned to: CPU Architecture:Any

[21 Mar 2008 18:14] Mark Ostrum
Description:
On insert, reports 

ERROR 1062 (23000): Duplicate entry '20080321095738-000029212-11309-20080321095738-000029212-11309-18' for key 1

when key should be

'20080321095738-000029212-11309-20080321095738-000029212-11309-1812-0'

How to repeat:
DROP TABLE IF EXISTS `Drug2ProcedureInteraction`;
CREATE TABLE IF NOT EXISTS `Drug2ProcedureInteraction` (
  `requestID` varchar(255) NOT NULL ,
  `safetyTransactionID` varchar(64) NOT NULL ,
  `routedMedID` int(11) NOT NULL default '0',
  `seq_no` int(11) NOT NULL default '0',
  interactionSeqNo int(11) not null,
  `objectID` text,
  `consumerInteractionDetail` text,
  `consumerInteractionTitle` text,
  `interactionDetail` text,
  `interactionTitle` text,
  `severityLevel` int(11) default NULL,
  `CPT` varchar(15) default NULL,
  `CPTDescription` text,
  `ohtMasterID` int(11) default NULL,
  `ohtCategoryID` int(11) default NULL,
  `ohtDescription` text,
  `procedureDate` datetime default NULL,
 PRIMARY KEY  (`requestID`,`safetyTransactionID`,`routedMedID`, seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO Drug2ProcedureInteraction
( requestID, safetyTransactionID, routedMedID, seq_no,
consumerInteractionDetail, consumerInteractionTitle,
interactionDetail, interactionTitle, severityLevel,
interactionSeqNo, CPT, CPTDescription, ohtMasterID,
ohtCategoryID, ohtDescription, procedureDate )
VALUES (
'20080321095738-000029212-11309',
'20080321095738-000029212-11309',
1812,
0,
'<b>Levothroid Oral</b> contains <b>THYROID HORMONES</b>. <b>THYROID HORMONES</b><span class="ysl"> should be used with caution in patients with </span><b>CVS Disease</b>. Since you have reported that you have had a surgical procedure called <b>Removal of a single lobe of the lung</b> indicating you may have <b>CVS Disease</b>, contact your healthcare provider or pharmacist for additional information regarding the use of this medication. <br/><br/><br/><br/>Contact your doctor or pharmacist for additional information regarding the use of this medication in patients with this condition.',
'Surgery Precaution',
'<b>Levothroid Oral</b> is <span class="ysl">relatively contraindicated in patients with </span>in patients with <b>Disease of Cardiovascular System</b>. The following patient surgery is related to or may imply, that this condition exists: <br/><ul><li>Removal of lung, other than total pneumonectomy; single lobe (lobectomy)</li></ul>This medication belongs to the following drug classes, which are known to have clinically important considerations: <br/><ul><li><b>THYROID HORMONES</b> which is <span class="ysl">relatively contraindicated in </span><b>Disease of Cardiovascular System</b></li></ul><br/>For additional information please refer to the manufacturer''s monograph<br/>',
'Removal of lung,other than total pneumonectomy; single lobe (lobectomy) (Disease of Cardiovascular System)',
2,
1,
'32480',
'Removal of lung, other than total pneumonectomy; single lobe (lobectomy)',
NULL,
NULL,
NULL,
{ts '2004-04-09 00:00:00.000'} )

-- the duplicated insert - note the seq_no field changed from 0 to 1.

INSERT INTO Drug2ProcedureInteraction
( requestID, safetyTransactionID, routedMedID, seq_no,
consumerInteractionDetail, consumerInteractionTitle,
interactionDetail, interactionTitle, severityLevel,
interactionSeqNo, CPT, CPTDescription, ohtMasterID,
ohtCategoryID, ohtDescription, procedureDate )
VALUES (
'20080321095738-000029212-11309',
'20080321095738-000029212-11309',
1812,
1,
'<b>Levothroid Oral</b> contains <b>THYROID HORMONES</b>. <b>THYROID HORMONES</b><span class="ysl"> should be used with caution in patients with </span><b>CVS Disease</b>. Since you have reported that you have had a surgical procedure called <b>Removal of a single lobe of the lung</b> indicating you may have <b>CVS Disease</b>, contact your healthcare provider or pharmacist for additional information regarding the use of this medication. <br/><br/><br/><br/>Contact your doctor or pharmacist for additional information regarding the use of this medication in patients with this condition.',
'Surgery Precaution',
'<b>Levothroid Oral</b> is <span class="ysl">relatively contraindicated in patients with </span>in patients with <b>Disease of Cardiovascular System</b>. The following patient surgery is related to or may imply, that this condition exists: <br/><ul><li>Removal of lung, other than total pneumonectomy; single lobe (lobectomy)</li></ul>This medication belongs to the following drug classes, which are known to have clinically important considerations: <br/><ul><li><b>THYROID HORMONES</b> which is <span class="ysl">relatively contraindicated in </span><b>Disease of Cardiovascular System</b></li></ul><br/>For additional information please refer to the manufacturer''s monograph<br/>',
'Removal of lung,other than total pneumonectomy; single lobe (lobectomy) (Disease of Cardiovascular System)',
2,
1,
'32480',
'Removal of lung, other than total pneumonectomy; single lobe (lobectomy)',
NULL,
NULL,
NULL,
{ts '2004-04-09 00:00:00.000'} )
[21 Mar 2008 18:16] Mark Ostrum
Reporter adds:

The second key should have been

'20080321095738-000029212-11309-20080321095738-000029212-11309-1812-1'

thereby avoiding the error message altogether.
[22 Mar 2008 7:17] Sveta Smirnova
Thank you for the report.

But version 5.0.37 is quite old. Additionally I can not repeat described behavior. Please upgrade to current version 5.0.51a, try with it and inform us if bug still exists.
[27 Mar 2008 15:10] Mark Ostrum
5.0.51 version seems NOT to have fixed the issue.
[27 Mar 2008 16:36] Heikki Tuuri
The duplicate key value is retrieved and printed in the MySQL layer of code. InnoDB only returns the index number.
[28 Mar 2008 14:37] Susanne Ebrecht
Many thanks for writing a bug report.

Unfortunately I can't reproduce this by using newest version MySQL 5.0.51a.

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.0.51a-debug

mysql> create table t(a varchar(255) not null, b varchar(64) not null, c integer not null default 0, d integer not null default 0, t text, primary key(a,b,c,d)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values('20080321095738-000029212-11309','20080321095738-000029212-11309',1812,0, 'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('20080321095738-000029212-11309','20080321095738-000029212-11309',1812,1, 'abc');
Query OK, 1 row affected (0.00 sec)