nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Update Resolution for closed issues at DB level

Tarun Sapra

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

Accepted answers

All comments

Christian Czaia [Decadis AG]

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

RambanamP

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

Nic Brough -Adaptavist-

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

Backup the database first, as you could destroy it
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
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:

Use Jira to do it, not SQL. Bulk edit is easiest, but REST, the CLI or plugins can do it too
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

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.

Tarun Sapra

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-

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.

Christian Czaia [Decadis AG]

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.

Tarun Sapra

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

Tarun Sapra

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration