Fixing incorrectly imported status

While importing tickets from a legacy system to Jira, several thousands tickets got imported with incorrect status. Namely, what used to have status of "Verified in QA", should've been imported with status of "QA Complete". Instead, all these tickets got imported as "Closed" (with resolution "Resolved").

Rerunning the import is impossible -- I need to fix the situation. Although using the REST API would be one approach, it seems both slow and imperfect: each ticket would first need to be transitioned from "Closed" to "Reopened" and then -- into "QA Complete".

Can I fix the problem by direct manipulation of the database? I know the exact list of tickets affected -- I imported each status-transition and can now find them all in the changeitem-table. If I:

  1. update jiraissue-table:
    changing the issuestatus-field to the id of "QA Complete" (as found in the issuestatus-table)
    resetting both the RESOLUTION and the RESOLUTIONDATE to NULL:
    update jiraissue, changegroup, changeitem
        set issuestatus=(select id from issuestatus
               where pname='QA Complete'),
            RESOLUTION=NULL, RESOLUTIONDATE=NULL
        where changegroup.issueid = jiraissue.id
        and changeitem.groupid=changegroup.ID
        and NEWSTRING='Verified in QA'
  2. update the changeitem-table changing NEWVALUE to "QA Complete" wherever NEWSTRING is "Verified in QA"
  3. Restart Jira

Will it all "just work" afterwards? I tried this in our test-instance of Jira and life seems good -- have I left some landmine for myself to blow up later, or is this a (relatively) safe thing to do?

Thank you!

3 answers

1 votes

There are other tables that needs to be modified, if you have other transitions from the current step. OS_CURRENTSTEP and OS_HISTORYSTEP are the ones I remember. But if those issues are almost done and dusted (i.e. you do not have any more transitions left), what you have done should be enough.

Only other option is to modify the workflows.

Thank you, Jobin. Would those two other tables have anything in them regarding the tickets in question, if the tickets have not been modified since the import at all?

If not, I would not need to worry about them...

Those tables are important only if you have outgoing transitions from "QA Complete" step.

I'm sorry, I don't quite understand the answer... Our workflow does allow transitions out of the "QA Complete" state.

None of the tickets incorrectly imported have made such a transition -- if only because none of them were in that state yet (due to the very error I'm tryng to correct).

Indeed, they have not been modified at all -- since the import. Am I safe? Thank you!

Then it will be an issue I guess. When you tried the 3 steps in Test, are you seeing the outgoing transitions and able to execute them on those issues?

Well, you are onto something -- though the status is now correctly showing as "QA Complete", I'm offered to "Reopen" the tickets -- rather than close. This suggests, part of Jira still thinks, they are "closed"... Oh, well, will investigate deeper -- and post here. Thanks, Jobin!

Ok, found some (incomplete) manual on the database schema . The OS_CURRENTSTEP links with jiraissue this way:

jiraissue.WORKFLOW_ID == OS_CURRENTSTEP.ENTRY_ID

The value in the OS_CURRENTSTEP.STEP_ID needs to match jiraissue.issuestatus but -- and this is, where the manual is incorrect -- whereas the issuestatus field refers to the id-field of the issuestatus table, the STEP_ID refers to the "step" in the XML-blob describing the issue's workflow... The two sets of numbers match sometimes -- such as when the workflow is Jira's built-in.

But, without the XML-parsing functions available, there is no way to programmatically get the step-ID matching the status name of a custom workflow...

Maybe a stupid suggestion but do not forget to do the reindex of JIRA after you imported the stuff and made the update it can update the issues correctly. :)

Somehow I thought, the reindexing is automatically done on startup... How would I trigger it explicitly? Thanks!

Well if you go to the administration seciton it can ask you for a reindex but if there is no popup in the top you can just click g+g (admin hotkeys) and search for reindex or index and you will see the reindex section. You can select background or lock jira and rebuild indexes. The background one might not work if reindexes are broken then you will just need to lock the JIRA instance and do full reindex.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,096 views 13 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot