Summary

Jira reports (Sprint report , burndown chart..) might show incorrect curves when a ticket has a corrupt workflow transition. For instance, even though tickets were closed during the sprint, we can see that the remaining value doesn't drop.

Environment

8.X

Diagnosis

• Check if a ticket has the same transition for instance, we can check if a workflow is inserting 'Done' status as a source and destination transition which is an incorrect state

  SELECT * 
  FROM CHANGEITEM 
  WHERE FIELD='status' AND OLDSTRING='Done' AND NEWSTRING='Done';

• To get more details on the tickets that might have problems in a specific Sprint, replace SPRINT-NAME and STATUS-VALUE with actual data from your instance, example : sprint 1 and Done respectively

  SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
  FROM changegroup cg 
  inner join jiraissue i on cg.issueid = i.id
  inner join project p on i.project = p.id
  inner join changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
  inner join app_user au on cg.author = au.user_key
  WHERE cg.issueid in (select jissue.id
  from
  CustomFieldValue cfv,
  CustomField cf,
  jiraissue jissue,
  project p,
  "AO_60DB71_SPRINT" ao
  where
  cf.cfname='Sprint'and
  cf.Id = cfv.CustomField and
  jissue.id = cfv.issue and
  jissue.project = p.Id and
  cf.cfname='Sprint' and
  ao."ID" = CAST(cfv.stringvalue as int) and
  ao."NAME" like '%SPRINT-NAME%') 
  and NEWSTRING ='STATUS-VALUE'
  order by 1,3,4;

Cause

• There is an extra post-function in Done (or other) causing problem: Set issue status to the linked status of the destination workflow step causing Jira to insert erroneous data in the CHANGEITEM table.

Solution

1. Stop Jira
2. Create a database backup

3. Delete the duplicate status value, in case it's Done status the query would be : 

   DELETE FROM CHANGEITEM WHERE FIELD='status' AND OLDSTRING='Done' AND NEWSTRING='Done';

4. Start Jira
5. Run Full reindex