duplicate value for a record with unique key

Posted in 2011

Summary

A developer saw "duplicate value for a record with unique key" (ISAM error) from an ESQL/C routine on IDS 11.50 (HP-UX) when two or more programs ran it concurrently; the failing statement was a prepared INSERT into Appl_Execution, whose appl_execution_id is a SERIAL with a unique index. Respondents asked for the full procedure, index/constraint details and version info; Art Kagel suggested inserting 0 rather than NULL to get the next generated serial. The poster asked for clarification but the thread ended with complaints about insufficient context and no confirmed resolution.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Error Codes & Troubleshooting, Data Types & Schema Design

HARISH S — Sat, 12 Mar 2011 04:56:01 — source: IIUG Forums & Mailing Lists

Hi All, I'm a newbie in informix. I'm getting the below error when two or more programs are using the library function func_batchpro at the same time. Please see below the code in the library where error is occuring Can any one please advice whats going wrong here. The error is Function func_batchpro returns[-1] SQL(100,0,0) [] ISAM error: duplicate value for a record with unique key. -------------------------------------------------------------------------------- --------------- /* for avoiding the database lock issues */ EXEC SQL SET LOCK MODE TO WAIT 60; SELECT function_Id,last_change_userid FROM dinf_com:Appl_Function WHERE Application_id = ? AND Function_Name = ?; EXECUTE GetFnDetails INTO :oFnId,:oLstChngUsrId USING :pApplnId,:pFnName; INSERT into dinf_com:Appl_Execution(failed_appl_exec_id, Function_Id,Key_Data_txt,BookMark_Value,Execution_End_Ts, Last_Change_UserId,Lock_Cnt) VALUES(NULL,?,?,0,CURRENT,?,?); EXEC SQL EXECUTE ApplnExec_ins USING :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; INSERT INTO dinf_com:Appl_Exec_Stat_Log(appl_execution_id, appl_exec_stat_cd,status_change_userId,status_change_ts) VALUES (?,?,?,CURRENT); EXEC SQL EXECUTE ApplExecStatLog_ins USING :oApplExecId,:iApplExecStatCd, :oLstChngUsrId; -------------------------------------------------------------------------------- ----------- structure of table Appl_Execution Column name Type Nulls appl_execution_id serial no failed_appl_exec_+ integer yes function_id integer no execution_start_ts datetime year to second no key_data_txt varchar(250,0) yes bookmark_value integer yes execution_end_ts datetime year to second yes last_change_userid char(10) no lock_cnt smallint no -------------------------------------------------------------------------------- ----------- indexes for table Appl_Execution Index_name Owner Type/Clstr Access_Method Columns appl_execution_p01 informix unique/No btree appl_execution_id appl_execution_f01 informix dupls/No btree function_id appl_execution_f02 informix dupls/No btree failed_appl_exec_+ -------------------------------------------------------------------------------- -----------

↪ replying to HARISH S

Mark Tyrer — Sat, 12 Mar 2011 07:35:41 — source: IIUG Forums & Mailing Lists

Hi, on what column(s) is the unique index against? > To: ids@iiug.org > From: harish_2070@yahoo.co.in > Subject: duplicate value for a record with unique key [23053] > Date: Fri, 11 Mar 2011 23:56:01 -0500 > > Hi All, > > I'm a newbie in informix. > I'm getting the below error when two or more programs are using the library > function func_batchpro at the same time. > > Please see below the code in the library where error is occuring > Can any one please advice whats going wrong here. > > The error is > Function func_batchpro returns[-1] SQL(100,0,0) [] ISAM error: duplicate value > for a record with unique key. > > > -------------------------------------------------------------------------------- --------------- > > /* for avoiding the database lock issues */ > > EXEC SQL SET LOCK MODE TO WAIT 60; > > SELECT function_Id,last_change_userid> > FROM dinf_com:Appl_Function > > WHERE Application_id = ? AND Function_Name = ?; > > EXECUTE GetFnDetails INTO :oFnId,:oLstChngUsrId > > USING :pApplnId,:pFnName; > > INSERT into dinf_com:Appl_Execution(failed_appl_exec_id, > Function_Id,Key_Data_txt,BookMark_Value,Execution_End_Ts, > Last_Change_UserId,Lock_Cnt) VALUES(NULL,?,?,0,CURRENT,?,?);> > EXEC SQL EXECUTE ApplnExec_ins USING > :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; > > INSERT INTO dinf_com:Appl_Exec_Stat_Log(appl_execution_id,> > appl_exec_stat_cd,status_change_userId,status_change_ts) > > VALUES (?,?,?,CURRENT); > > EXEC SQL EXECUTE ApplExecStatLog_ins USING :oApplExecId,:iApplExecStatCd, > > :oLstChngUsrId; > > > -------------------------------------------------------------------------------- ----------- > > structure of table Appl_Execution > > Column name Type Nulls > > appl_execution_id serial no > failed_appl_exec_+ integer yes > function_id integer no > execution_start_ts datetime year to second no > key_data_txt varchar(250,0) yes > bookmark_value integer yes > execution_end_ts datetime year to second yes > last_change_userid char(10) no > lock_cnt smallint no > > > -------------------------------------------------------------------------------- ----------- > indexes for table Appl_Execution > > Index_name Owner Type/Clstr Access_Method Columns > > appl_execution_p01 informix unique/No btree appl_execution_id > > appl_execution_f01 informix dupls/No btree function_id > > appl_execution_f02 informix dupls/No btree failed_appl_exec_+ > > > -------------------------------------------------------------------------------- ----------- > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Mark Tyrer

HARISH S — Sat, 12 Mar 2011 09:25:25 — source: IIUG Forums & Mailing Lists

appl_execution_id column is having the unique index

↪ replying to Mark Tyrer

HARISH S — Sat, 12 Mar 2011 09:27:29 — source: IIUG Forums & Mailing Lists

appl_execution_id column is having the unique index

↪ replying to Mark Tyrer

Mark Tyrer — Sat, 12 Mar 2011 09:50:04 — source: IIUG Forums & Mailing Lists

Hi, What indexes do you have on Appl_Exec_Stat_Log Please keep the history in your posts Regards Mark > To: ids@iiug.org > From: m_tyrer@hotmail.com > Subject: RE: duplicate value for a record with unique key [23054] > Date: Sat, 12 Mar 2011 02:35:42 -0500 > > Hi, on what column(s) is the unique index against? > > > To: ids@iiug.org > > From: harish_2070@yahoo.co.in > > Subject: duplicate value for a record with unique key [23053] > > Date: Fri, 11 Mar 2011 23:56:01 -0500 > > > > Hi All, > > > > I'm a newbie in informix. > > I'm getting the below error when two or more programs are using the library > > function func_batchpro at the same time. > > > > Please see below the code in the library where error is occuring > > Can any one please advice whats going wrong here. > > > > The error is > > Function func_batchpro returns[-1] SQL(100,0,0) [] ISAM error: duplicate > value > > for a record with unique key. > > > > > > > -------------------------------------------------------------------------------- --------------- > > > > /* for avoiding the database lock issues */ > > > > EXEC SQL SET LOCK MODE TO WAIT 60; > > > > SELECT function_Id,last_change_userid> > > > FROM dinf_com:Appl_Function > > > > WHERE Application_id = ? AND Function_Name = ?; > > > > EXECUTE GetFnDetails INTO :oFnId,:oLstChngUsrId > > > > USING :pApplnId,:pFnName; > > > > INSERT into dinf_com:Appl_Execution(failed_appl_exec_id, > > Function_Id,Key_Data_txt,BookMark_Value,Execution_End_Ts, > > Last_Change_UserId,Lock_Cnt) VALUES(NULL,?,?,0,CURRENT,?,?);> > > > EXEC SQL EXECUTE ApplnExec_ins USING > > :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; > > > > INSERT INTO dinf_com:Appl_Exec_Stat_Log(appl_execution_id,> > > > appl_exec_stat_cd,status_change_userId,status_change_ts) > > > > VALUES (?,?,?,CURRENT); > > > > EXEC SQL EXECUTE ApplExecStatLog_ins USING :oApplExecId,:iApplExecStatCd, > > > > :oLstChngUsrId; > > > > > > > -------------------------------------------------------------------------------- ----------- > > > > structure of table Appl_Execution > > > > Column name Type Nulls > > > > appl_execution_id serial no > > failed_appl_exec_+ integer yes > > function_id integer no > > execution_start_ts datetime year to second no > > key_data_txt varchar(250,0) yes > > bookmark_value integer yes > > execution_end_ts datetime year to second yes > > last_change_userid char(10) no > > lock_cnt smallint no > > > > > > > -------------------------------------------------------------------------------- ----------- > > indexes for table Appl_Execution > > > > Index_name Owner Type/Clstr Access_Method Columns > > > > appl_execution_p01 informix unique/No btree appl_execution_id > > > > appl_execution_f01 informix dupls/No btree function_id > > > > appl_execution_f02 informix dupls/No btree failed_appl_exec_+ > > > > > > > -------------------------------------------------------------------------------- ----------- > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to HARISH S

Art Kagel — Sun, 13 Mar 2011 00:35:11 — source: IIUG Forums & Mailing Lists

First, if you want us to properly review this procedure, you will have to provide the entire text, not just isolated snippets. Second, since you are getting a duplicate key error, you must have a UNIQUE index somewhere in this process, and you don't show us any unique indexes or UNIQUE or PRIMARY key constraints, so we have no idea what's happening. You will have to post more detailed information if you want help. Also, ALWAYS post your version and platform information, it often affects the answers you get and how helpful they will be to you. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 11, 2011 at 11:56 PM, HARISH S <harish_2070@yahoo.co.in> wrote: > Hi All, > > I'm a newbie in informix. > I'm getting the below error when two or more programs are using the library > function func_batchpro at the same time. > > Please see below the code in the library where error is occuring > Can any one please advice whats going wrong here. > > The error is > Function func_batchpro returns[-1] SQL(100,0,0) [] ISAM error: duplicate > value > for a record with unique key. > > > > -------------------------------------------------------------------------------- --------------- > > /* for avoiding the database lock issues */ > > EXEC SQL SET LOCK MODE TO WAIT 60; > > SELECT function_Id,last_change_userid> > FROM dinf_com:Appl_Function > > WHERE Application_id = ? AND Function_Name = ?; > > EXECUTE GetFnDetails INTO :oFnId,:oLstChngUsrId > > USING :pApplnId,:pFnName; > > INSERT into dinf_com:Appl_Execution(failed_appl_exec_id, > Function_Id,Key_Data_txt,BookMark_Value,Execution_End_Ts, > Last_Change_UserId,Lock_Cnt) VALUES(NULL,?,?,0,CURRENT,?,?);> > EXEC SQL EXECUTE ApplnExec_ins USING > :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; > > INSERT INTO dinf_com:Appl_Exec_Stat_Log(appl_execution_id,> > appl_exec_stat_cd,status_change_userId,status_change_ts) > > VALUES (?,?,?,CURRENT); > > EXEC SQL EXECUTE ApplExecStatLog_ins USING :oApplExecId,:iApplExecStatCd, > > :oLstChngUsrId; > > > > -------------------------------------------------------------------------------- ----------- > > structure of table Appl_Execution > > Column name Type Nulls > > appl_execution_id serial no > failed_appl_exec_+ integer yes > function_id integer no > execution_start_ts datetime year to second no > key_data_txt varchar(250,0) yes > bookmark_value integer yes > execution_end_ts datetime year to second yes > last_change_userid char(10) no > lock_cnt smallint no > > > > -------------------------------------------------------------------------------- ----------- > indexes for table Appl_Execution > > Index_name Owner Type/Clstr Access_Method Columns > > appl_execution_p01 informix unique/No btree appl_execution_id > > appl_execution_f01 informix dupls/No btree function_id > > appl_execution_f02 informix dupls/No btree failed_appl_exec_+ > > > > -------------------------------------------------------------------------------- ----------- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf300fb2837236ca049e525daf

↪ replying to HARISH S

Art Kagel — Sun, 13 Mar 2011 00:36:32 — source: IIUG Forums & Mailing Lists

You should be inserting a zero into that column to get the next generated serial number not a NULL. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Mar 12, 2011 at 4:25 AM, HARISH S <harish_2070@yahoo.co.in> wrote: > appl_execution_id > column is having the unique index > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --485b397dcf7d37749b049e526268

↪ replying to Mark Tyrer

HARISH S — Sun, 13 Mar 2011 03:03:21 — source: IIUG Forums & Mailing Lists

Hi All, I apologize for not providing the whole details. Please see the whole program as you requested.The error is occurring with the below statement EXEC SQL EXECUTE ApplnExec_ins USING :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; ###############Indexes for table appl_execution################### Index_name Type Columns 609_3344 unique appl_execution_id 609_3606 dupls function_id 609_3607 dupls failed_appl_exec_id #######Indexes for table Appl_Exec_Stat_Log#################### Index_name Type Columns 635_3521 unique appl_execution_id appl_exec_stat_cd 635_3651 dupls appl_execution_id 635_3668 dupls appl_exec_stat_cd Im using Informix Dynamic Server Version 11.50 in HP UX platform ######################START OF PROGRAM################################## EXEC SQL INCLUDE "probatch.h"; extern batlog_t gLog; int FnBatchStart(pApplnId,pFnName,pKeyData,pApplExecId,pCmtFreq) EXEC SQL BEGIN DECLARE SECTION; char pApplnId[APPLN_ID_LEN]; char pKeyData[KEY_DATA_LEN]; char pFnName[APPLN_FN_NAME_LEN]; int *pApplExecId; int *pCmtFreq; EXEC SQL END DECLARE SECTION; { /********************** Declare Section ***************************/ int lRc = FAIL; EXEC SQL BEGIN DECLARE SECTION; int iApplExecStatCd = BATCH_IN_PROG; int oApplExecId = ZERO; int oCmtFreq = ZERO; int oFnId = ZERO; int iLockCnt = TRUE; /* Assigns 1 */ char oLstChngUsrId[LST_UPDT_USR_ID_LEN]; EXEC SQL END DECLARE SECTION; ProDEBUG(2) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Inside the Function FnBatchStart with Application_Id[%s] KeyData[%s]" " Function_Name[%s]",pApplnId,pKeyData,pFnName); /***************** Preparing the SQL Statements ************************/ lRc = FnBatchStart_init(); /****************** Fetching the Function Details *********************/ /* Added to avoid the database lock issues */ EXEC SQL SET LOCK MODE TO WAIT 60; if(SUCCESS == lRc) { EXEC SQL EXECUTE GetFnDetails INTO :oFnId,:oLstChngUsrId USING :pApplnId,:pFnName; ProDEBUG(3) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Executing GetFnDetails with ApplicationId[%s] FunctionName[%s]" " returns SQLCODE[%d] FunctionId[%d] Last_Change_UserId[%s]", pApplnId,pFnName,SQLCODE,oFnId,oLstChngUsrId); if(SQLCODE) { lRc = FAIL; } else /* SQL SUCCESS */ { lRc = SUCCESS; } }/* End if(SUCCESS == lRc) */ /************ Inserting Records in the Appl_Execution table ********/ if(SUCCESS == lRc) { EXEC SQL EXECUTE ApplnExec_ins USING :oFnId,:pKeyData,:oLstChngUsrId,:iLockCnt; ProDEBUG(3) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Executing ApplnExec_ins with inputs FunctionId[%d] KeyData[%s]" " Last_Change_UsetId[%s] LockCount[%d] returns SQLCODE[%d]",oFnId, pKeyData,oLstChngUsrId,iLockCnt,SQLCODE); if(SQLCODE) { lRc = FAIL; } else /* SQL SUCCESS */ { /* Assigning the Value to the Return Value Parameter */ *pApplExecId = sqlca.sqlerrd[1]; /* Assigning the value to the Program Variable */ oApplExecId = sqlca.sqlerrd[1]; lRc = SUCCESS; } }/* End if(SUCCESS == lRc) */ /********* Inserting Records in the Appl_Exec_Stat_Log table *****/ if(SUCCESS == lRc) { EXEC SQL EXECUTE ApplExecStatLog_ins USING :oApplExecId,:iApplExecStatCd, :oLstChngUsrId; ProDEBUG(3) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Executing ApplExecStatLog_ins with ApplnExecId[%d] ApplExecStatCd[%d]" " LstChngUsrId[%s] returns SQLCODE[%d]",oApplExecId,iApplExecStatCd, oLstChngUsrId,SQLCODE); if(SQLCODE) { lRc = FAIL; } else /* SQL SUCCESS */ { lRc = SUCCESS; } }/* End if(SUCCESS == lRc) */ /***************** Fetching the Commit Frequency ****************/ if(SUCCESS == lRc) { EXEC SQL EXECUTE CmtFreq_qry INTO :oCmtFreq USING :oFnId; ProDEBUG(3) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Executing CmtFreq_qry with Input FunctionId[%d] returns" " SQLCODE[%d] Commit Frequency[%d]",oFnId,SQLCODE,oCmtFreq); if(SQLCODE) { lRc = FAIL; } else /* SQL SUCCESS */ { /* Assigning the Value to the Return Value Parameter */ *pCmtFreq = oCmtFreq; lRc = SUCCESS; } }/* End if(SUCCESS == lRc) */ /*************** Return the values from the function ***********/ ProDEBUG(2) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Function FnBatchStart returns[%d]",lRc); return(lRc); }/* End FnBatchStart */ /**********************************************************************/ /**************** FUNCTION FnBatchStart_init **************************/ /**********************************************************************/ /* FILE NAME : FnBatchStart.ec */ /* FUNCTION NAME : FnBatchStart_init() */ /* PURPOSE : This function prepares all the SQL */ /* Statements of FnBatchStart function. */ /* INPUT PARAMETER : NONE */ /* OUTPUT PARAMETER : -1(FAIL)/0(SUCCESS) */ /* GLOBAL VARIABLE MODIFIED : gLog */ /* FUNCTIONS CALLED : NONE */ /**********************************************************************/ int FnBatchStart_init() { /********************** Declare Section ***************************/ EXEC SQL BEGIN DECLARE SECTION; char *lPtrStmt = NULL; int lRc = FAIL; EXEC SQL END DECLARE SECTION; /******************** Log Message **********************************/ ProDEBUG(2) PrologWriteLog(&gLog,__LINE__,__FILE__,__func__, "Inside the Function FnBatchStart_init"); /*********************** Preparing the Queries *********************/ lPtrStmt = "INSERT into dinf_com:Appl_Execution(failed_appl_exec_id," " Function_Id,Key_Data_txt,BookMark_Value,Execution_End_Ts," " Last_Change_UserId,Lock_Cnt) VALUES(NULL,?,?,0,CURRENT,?,?)"; EXEC SQL PREPARE ApplnExec_ins FROM :lPtrStmt; if(SQLCODE) { PrologWriteSQL(&gLog,__LINE__,__FILE__,__func__, "Preparing ApplnExec_ins returns Error"); lRc = FAIL; }/* end of if(SQLCODE)*/ else/* SUCCESS */ { lRc = SUCCESS; } /**********************************************************************/ if(SUCCESS == lRc) { lPtrStmt = "SELECT function_Id,last_change_userid " " FROM dinf_com:Appl_Function " " WHERE Application_id = ? AND Function_Name = ?"; EXEC SQL PREPARE GetFnDetails FROM :lPtrStmt; if(SQLCODE) { PrologWriteSQL(&gLog,_

↪ replying to Art Kagel

HARISH S — Mon, 14 Mar 2011 12:34:32 — source: IIUG Forums & Mailing Lists

Hi Art, Could you please be a bit descriptive about the problem cause which you mentioned. Thanks Harish

↪ replying to HARISH S

HARISH S — Fri, 18 Mar 2011 10:00:15 — source: IIUG Forums & Mailing Lists

anyone please respond

↪ replying to HARISH S

Jonathan — Fri, 18 Mar 2011 19:46:53 — source: IIUG Forums & Mailing Lists

On Fri, Mar 18, 2011 at 03:00, HARISH S <harish_2070@yahoo.co.in> wrote: > anyone please respond > There is nothing to respond to in this email! You must give enough context for a question like this to be meaningful. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001517447aa415b92d049ec56870

Glossary

ISAM error