Update Resolution for closed issues at DB level

Tarun Sapra Community Champion Jul 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 votes

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

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 Champion Jul 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

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 :-(

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

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

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
Tarun Sapra Community Champion Jul 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

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,121 views 13 19
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