MySQL Bugs: #35487: Duplicate Entry For Key error reports truncated key value

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

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'} )

Reporter adds:

The second key should have been

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

thereby avoiding the error message altogether.

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.

5.0.51 version seems NOT to have fixed the issue.

The duplicate key value is retrieved and printed in the MySQL layer of code. InnoDB only returns the index number.

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)