Jira Agile reports not showing correct data

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

  1 2 3 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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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 :

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

4. Start Jira

5. Run Full reindex