Unable to Transition Workflow due to Duplicate entry error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
Attempting to transition a ticket results in a Workflow error
The following appears in the atlassian-jira.log:
1
2
2013-06-17 14:44:57,635 http-bio-8079-exec-12 ERROR admin 884x507x1 d6aukd 0:0:0:0:0:0:0:1%0 /secure/WorkflowUIDispatcher.jspa [atlassian.jira.workflow.OSWorkflowManager] Caught exception while attempting to perform action 21 from workflow 12123 on issue 'TST-5'
com.opensymphony.workflow.StoreException: Could not move to history step for #12123: root cause: while inserting: [GenericEntity:OSHistoryStep][id,12003][caller,admin][startDate,2013-06-12 16:15:01.0][status,Not Done][owner,][finishDate,2013-06-17 14:44:57.632][actionId,21][stepId,1][dueDate,null][entryId,11000] (SQL Exception while executing the following:INSERT INTO OS_HISTORYSTEP (ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, DUE_DATE, FINISH_DATE, STATUS, CALLER) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate entry '12003' for key 'PRIMARY'))Diagnosis
System ran into issues with the sequence_value_item table. Similar to the issue mentioned here:
Causes
JIRA tracks workflow transition history via the OS_CURRENTSTEP and OS_HISTORYSTEP tables. Due to the
sequence_value_itembeing reset, old ID values were re-assigned, resulting in duplicate errors.Jira's database is a cluster with a loadbalancer that doesn't honor session affinity or doesn't replicate in real time where queries from the same user session land on different database server node with different content.
Resolution
First, ensure that you have resolved any issues with the sequence_value_item table.
Once that is resolved, you can clean up the workflow tables with the following:
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Shutdown JIRA
Confirm the number of entries duplicated between the
OS_CURRENTSTEPandOS_HISTORYSTEPtables:1select count(id) from OS_CURRENTSTEP where id in (select id from OS_HISTORYSTEP);Once done, remove the duplicate entries from the
OS_CURRENTSTEPtable:1delete from OS_CURRENTSTEP where id in (select id from OS_HISTORYSTEP);Restart Jira
For the second cause , if Jira database is behind a loadbalancer for redundancy/high availability make sure that requests routing and session affinity is well configured and the user's requests land on the same node.
Was this helpful?