Update Resolution for closed issues at DB level

Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

Hi,

How do i update resolution at DB level, using sql scripting.

there are some resolutions - lets say - A, B, C , D and i want all of them to be changed to resolution "closed", so i wrote this query -

update jiraissue set resolution = 7 where resolution in (select id from resolution where pname in ('A', 'B', 'C', 'D'));

7 is the resolution id of closed. But this query seems to have done something wrong as on my dashboard now i can't see the project list anymore.

Please share your opinion.

Thanks

4 answers

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

You could well have damaged your database, but, to try to rescue this case, restart Jira and then run a full re-index

If you change data in a Jira database, then there are three basic rules

  1. Backup the database first, as you could destroy it
  2. NEVER do it while Jira is running, it caches stuff, and even if your SQL is perfectly safe, you can lose data and get corruptions
  3. After restarting Jira, most changes absolutely require you to reindex. Ideally the full re-index (I've only seen this once so far, but someone had a broken index that background re-indexing did not fix)

In the long term, if you need to do this again, then:

  1. Use Jira to do it, not SQL. Bulk edit is easiest, but REST, the CLI or plugins can do it too
  2. There is a minor trick - a lot of workflow set a "do not allow edit" flag on status like "closed". You won't be able to edit those unless you remove the flag, reopen the issues, do something in code or add a "edit" transition back to closed that has the resolution on-screen.
Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

yes i know about jira.editable wrokflow status property, but the change was reuiqred across multiple projects thus I didn't want to make changes in diff. workflows or use global transitions that loop back on the same status just for the sake of updating workflow but I guess as the KB article suggests it's one of the few workarounds available.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

I know it's not simple.

Have you restarted your Jira and re-indexed it? You need to do that if you don't want to lose any data.

Your SQL looks ok to me, but you really do need Jira offline and re-indexed to stand any chance of this working.

Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

yes after reindexing also the project list didn't appear. it's a test instance thus i am reverting back to the older stable state and will try the groovy script plugin instead of directly manipulating the db

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

The SQL you ran on it's own shouldn't have broken the project list. But if you had Jira up while it was done, it sounds like it has done something horrid and unpredictable :-(

0 votes
Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

yes after reindexing also the project list didn't appear. it's a test instance thus i am reverting back to the older stable state and will try the groovy script plugin instead of directly manipulating the db

0 votes
RambanamP
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.
July 25, 2013

directly updating DB is not suggestable, always first try to update through jira api in case it is not feasible then you have to look into update directly on DB,

one way, you can update resoulution by using bulk edit

another way, check the following document to bulk edit resolutions

https://confluence.atlassian.com/display/JIRAKB/HowTo%3A+Bulk+Edit+Resolution

some one already raised an issue with atlassian

https://jira.atlassian.com/browse/JRA-7302

Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 25, 2013

Using bulk edit isn't the best possible approach as the final workflow state is non-editable, ofcourse we can make it editable but the thing is that this change needs to be implementated across multiple projects. Thus updating each and every workflow isn't a great idea.

Christian Czaia _Decadis AG_
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.
July 25, 2013

Modifying each workflow is a pain, you're right but once you get the time here's sth. I did with all my workflows:

I replaced the jira.issue.editable property with the following:

jira.permission.edit.group = jira-administrators

That way the issues aren't editable for any users but I can do whatever I want and I have to do bulk-edits A LOT.

0 votes
Christian Czaia _Decadis AG_
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.
July 25, 2013

My opinion is to avoid messing with the DB directly! I would use Jamie's script runner plugin which has a built-in script to bilk-edit resolution...

https://jamieechlin.atlassian.net/wiki/display/GRV/Built-In+Scripts

Cheers

Suggest an answer

Log in or Sign up to answer