Child pages
  • How to remove mistyped Estimate
Skip to end of metadata
Go to start of metadata

Symptoms

A mistype during the Estimate of an Issue during a Sprint is causing Burndown Chart to spike. See screenshot for example:

Cause

An Issue estimation input was incorrect. After updating the Estimate the Burndown Chart will still spike as it is a representation of the historical changes.

There is a request open for the ability to correct mistypes in the Estimate here: GHS-8477 - Getting issue details... STATUS  

Workaround

  • The only way to correct this at the moment is through direct database modifications.
  • Please be sure to shutdown JIRA and take a backup of your data prior to making any changes. 
  • Additionally, it is worth noting that Atlassian recommends correcting any mistype in the the Original Estimate by updating the Remaining Estimate field. With this approach, there is no negative impact other than the proportions on the Burndown Chart.

(info) Since JIRA 6.1 the pkey column is no longer used. See instructions applicable to the version of JIRA.

 Instructions for versions higher than JIRA 6.1
  • Identify the Issue where the estimate was mistyped and note the Project and Issuenum IDs.
  • Run the following SQL to identify the Project ID:
select id, pname from project;
  • Next, identify the Issuenum ID:
select issuenum from jiraissue where project=<PROJECT_ID> and summary=<SUMMARY>;

(info) Replace <PROJECT_ID> with value found in query above, and replace <SUMMARY> with the applicable Issue's summary, to easily locate it.

  • Now we are ready to look up the estimates:
select * from jiraissue where project = <PROJECT_ID> and issuenum=<ISSUE_ID>;

(info) Replace <PROJECT_ID> and <ISSUE_ID> with the values found earlier.

 

  • The values stored in 'timeoriginalestimate' and 'timeestimate' are in seconds. In this example the Original Estimate value was by mistake set to 9 weeks and later the Remaining Estimate set to 9 hours.
  • For this example, we will remove the Original Estimate and Remaining Estimate values so the Burndown Chart will no longer spike due to the mistype.
  • Run the following SQL:

    update jiraissue set timeoriginalestimate='0',timeestimate='0' where project=<PROJECT_ID> and issuenum=<ISSUE_ID>;

    (info) Replace <PROJECT_ID> and <ISSUE_ID> with the values found earlier.

     

  • With the jiraissue table updated, the last step is to remove the entries referring to the changes made, stored in changeitem and changegroup tables. To locate the entries that are relevant here, run the following SQL:
select pkey, CG.ID, CI.ID, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING from changegroup CG left join changeitem CI on CG.id = CI.groupid left join jiraissue JI on CG.issueid = JI.ID where JI.issuenum = <ISSUE_ID> order by CG.CREATED asc;

(i) Replace <ISSUE_ID> with the value found earlier.

  • This should return results similar to these, where Issue Key is 'SSPI-13':

 Instructions for versions prior to JIRA 6.1
  • Identify the Issue where the estimate was mistyped and note the Issue Key.
  • Run the following SQL:

    select pkey,timeoriginalestimate,timeestimate from jiraissue where pkey='SSPI-13';
  • This should return results similar to these, where the Issue Key is 'SSPI-13': 
pkeytimeoriginalestimatetimeestimate
SSPI-13129600032400
  • The values stored in 'timeoriginalestimate' and 'timeestimate' are in seconds. In this example the Original Estimate value was by mistake set to 9 weeks and later the Remaining Estimate set to 9 hours.
  • For this example, we will remove the Original Estimate and Remaining Estimate values so the Burndown Chart will no longer spike due to the mistype.
  • Run the following SQL:

    update jiraissue set timeoriginalestimate='0',timeestimate='0' where pkey='SSPI-13';
  • With the jiraissue table updated, the last step is to remove the entries referring to the changes made, stored in changeitem and changegroup tables. To locate the entries that are relevant here, run the following SQL:
select pkey, CG.ID, CI.ID, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING from changegroup CG left join changeitem CI on CG.id = CI.groupid left join jiraissue JI on CG.issueid = JI.ID where JI.pkey = 'SSPI-13' order by CG.CREATED asc;
  • This should return results similar to these, where Issue Key is 'SSPI-13':

 

 

  • With this information we can now remove the rows in the changegroup table with id=11600 and 11601 and any rows in the table changeitem with groupid=11600 and 11601. These changes represent the initial mistype of 9 weeks in the Original Estimate field as well as the update to Remaining Estimate of 9 hours.
delete from changeitem where groupid = '11600';
delete from changeitem where groupid = '11601';
delete from changegroup where id = '11600';
delete from changegroup where id = '11601';
  • Last step is to clear the contents of the <jira-home>/caches directory to rebuild the indexes. On next JIRA startup, please be sure to run a Re-Index of JIRA.

(info) Please be sure to take a backup of your data prior to making any changes at the database level.

Help us improve!

 


 

 


  • No labels

5 Comments

  1. Wow, that's a lot of work for one typo!

    1. It certainly is. Careful with your estimates (wink)

      As noted above "Atlassian recommends correcting any mistype in the the Original Estimate by updating the Remaining Estimate field. With this approach, there is no negative impact other than the proportions on the Burndown Chart."

  2. You should change the query to make it easier to read:

    select pkey, CG.ID as GroupID, CI.ID as ItemID, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING from changegroup CG left join changeitem CI on CG.id = CI.groupid left join jiraissue JI on CG.issueid = JI.ID where JI.pkey = 'SSPI-13' order by CG.CREATED asc;

    You can also delete them both in a single statement:

    delete CI, CG from changeitem CI left join changegroup CG ON CI.groupid = CG.ID where CG.ID in (11600,11601);
  3. I believe this statement is completely false:

     there is no negative impact other than the proportions on the Burndown Chart.

    It breaks the Sprint report. It breaks the Epic report. Is there any sprint-related report that it does not break?  This is the basis of why I believe this is a major issue: one mistype invalidates every sprint-based report.

  4. I have a useless burndown chart for current sprint due to human error entering RTE. We are using online Jira, so we have no access to the database. Pelle, saying "Careful with your estimates" is a lousy excuse for "We are too lazy and arrogant to fix this". Next time I start a project I must set a prerequisite "No mistakes nor human errors allowed" for myself and my project team.