Symptoms

Attempting to transition a ticket results in a Workflow error

The following appears in the atlassian-jira.log:

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:

• Cannot insert duplicate key in object when adding content to a JIRA application

Causes

• JIRA tracks workflow transition history via the OS_CURRENTSTEP and OS_HISTORYSTEP tables.  Due to the  sequence_value_item being 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.

• Cannot insert duplicate key in object when adding content to a JIRA application

Once that is resolved, you can clean up the workflow tables with the following:

1. Shutdown JIRA

2. Confirm the number of entries duplicated between the OS_CURRENTSTEP and OS_HISTORYSTEP tables:
   

   select count(id) from OS_CURRENTSTEP where id  in (select id from OS_HISTORYSTEP);

3. Once done, remove the duplicate entries from the OS_CURRENTSTEP table:
   

   delete from OS_CURRENTSTEP where id in (select id from OS_HISTORYSTEP);

4. 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.