How to remove mistyped Estimate

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

Please ensure that a backup have been done prior to the change below. Below modification is not guaranteed to be supported by Atlassian and any changes should be done in own risk


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.





Last modified on Mar 2, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.