how can i do if i want to change the issue status in database?

i had try to change the field issuestatus in table jiraissue but when i make search the status hasn't change. have i to change other table like OS_CURRENTSTEP, OS_HISTORYSTEP or changeitem?

2 answers

1 accepted

2 votes
Accepted answer

Yes.

But don't. Status especially is one of the worst things to try to do. It is NOT a field for a start, it's an indicator of a position in a workflow, and you need to update the workflow tables as well as the issue.

If you are going to change the database, then you MUST:

  1. Take Jira offline
  2. Get a backup
  3. Prove the backup
  4. Run your SQL
  5. Restart Jira
  6. Re-index it

I can't emphasise the offline or backup elements enough - this is dangerous and can destroy your installation.

To fix the current damage you've done, run the integrity checker. Then go back, work out exactly what you need to hack in the database, test the changes properly in a test system, book the downtime and backups and try again.

Or, do it properly, and use the workflow

Yes i unsterdant but to the beginning i want to when i change a customfield the status change and the only one solution i found is to change value in database. Have you any ideas to help me?

As before. You *must* have Jira offline if you amend the database. Even if it works sometimes, it won't always, and it's really not safe.

You should do this properly and use the workflow.

Could you describe the actual problem you have that lead you to hack the database instead of dealing with it properly?

I have one customfield which have the same fonction that the status. when i choose some values in this customfield i need to close the issue. i need to keep this customfield because user use it and change the way to work couldn't be easy. it's just to make automatic these little things.

Ok. It's very important to understand that your custom field is a field - it is some specific information about an issue. Status is NOT a field, it is something you can see as a result of moving an issue through a workflow.

I am not sure I've read your requirement correctly, so I'm going to explain what I think you've said and try to answer that - feel free to correct it all!

  • You have a custom field, let's call it "state of play" for now.
  • It's a simple select list, with options of "apple", "banana" and "cherry"
  • When a user goes into an issue and edits the field, setting it to "cherry", then you want the issue to move to the closed status

If I have got this right, then what you need to do is:

  • Make sure your workflow has transitions from all status to "closed" (ideally, use a common transition, so it's simple and consistent)
  • Find or write a listener - the spec for this is "when the edit event is caught, use progressWorkflowAction(close) on the issue to push it through to closed"

You have understood.

I understand the first point and it's easy but listener is more difficult to set up. Are there any plugin to installed?

Otherwise the reindexing works well. I know it's not safe but it's easly than listener. But if you help me maybe i can do it properly. I use jira 4.3.4 and i have just 3 listener but no one can help me. All of my status can change to "closed".

I'm not sure I'd say a listener is more difficult to set up. If you do this change with SQL, you need to shut down and back up Jira and reindex it. That's ok for a one-off change, but I don't think you want to have potentially hours of downtime every time someone changes a single field. (Also, we've not mentioned that you won't have any history of the change unless you insert some with SQL)

It sounds like you've got most of it set up, and it's just the listener you need. The problem here is that you need a listener to do something very specific, and I don't know of any generalised listeners you could use.

There is one in the "Jira Toolkit" called the auto-transition-listener which is close - it responds to events and pushes issues through transitions when it gets them. The missing part is simply your "look at state-of-play and execute the push if you see cherry in it". The code is open so you can download it and use it as an example.

Thanks you very much for your help. I tell you if i have another questions. =)

Hi. i have activated the plugin but listener not appear. i see it in the jar file. i tried to make it manualy, move file and write the way in the listener textfield but it doesn't works. can you tell me how to make it work?

Sorry, which listener? The auto-transition one in the Jira Toolkit? How have you installed it - via download in the unversal plugin manager?

yes the auto-transition one in the Jira toolkit.

i download it on atlassian marketplace. i installed to putting it on the JIRA 4.3.4\atlassian-jira\WEB-INF\lib folder then activated it.

Did you get the correct version of the plugin for Jira 4.3.4?

Also, you've installed it in the wrong place, whatever version you've got - I seem to remember that the plugin is installable from at least 4.2 onwards. Docs are at https://ecosystem.atlassian.net/wiki/display/JTOOL/JIRA+Toolkit+Plugin

Thanks you, it works but i don't think it do what i want. it just listen events like created issue, updated issue and some other and not modification of customfield. If you have another ideas i hear you. thanks you for your help.

Have tried reindexing JIRA after applying this SQL query. Or did you run by shutting down the JIRA instance. If not, reindexing JIRA should show you the updated the status for the issue.

EDIT: Posted after Nic's answer. Can anyone plz convert it into comment, as I can't from my side!

i see the update in my issue but not in my research witout shutting down. i don't know if it keep the old value somewhere. do you think i need to restart Jira?

As Nic mentioned, you should restart and/or reindex once you are done with exeuting the SQL query.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Featured Groups

Want to know how Atlassians monitor their enterprise deployments?

At Atlassian, we believe in our own products – that's why we use them, even at the enterprise level. Doing so gives us first-hand experience of how they perform at scale; to do this, we monitor each ...

101 views 3 9
View post

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