Fixing incorrectly imported status

Mikhail T November 22, 2013

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
Jobin Kuruvilla [Adaptavist]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 22, 2013

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.

Mikhail T November 24, 2013

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...

Jobin Kuruvilla [Adaptavist]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 24, 2013

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

Mikhail T November 24, 2013

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!

Jobin Kuruvilla [Adaptavist]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 24, 2013

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?

Mikhail T November 24, 2013

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!

Mikhail T December 15, 2013

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...

0 votes
Vladimir Omelchenko November 24, 2013

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.

0 votes
Vladimir Omelchenko November 24, 2013

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. :)

Mikhail T November 24, 2013

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

Suggest an answer

Log in or Sign up to answer