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 vote

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 Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

94 views 0 5
Read article

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