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

jimmy letord February 27, 2013

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

3 votes
Answer accepted
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.
February 27, 2013

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

jimmy letord February 27, 2013

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?

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.
February 27, 2013

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?

jimmy letord February 27, 2013

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.

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.
February 27, 2013

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"
jimmy letord February 28, 2013

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

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.
February 28, 2013

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.

jimmy letord February 28, 2013

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

jimmy letord March 3, 2013

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?

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.
March 3, 2013

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

jimmy letord March 3, 2013

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.

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.
March 3, 2013

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

jimmy letord March 3, 2013

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.

0 votes
Naren
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.
February 27, 2013

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!

jimmy letord February 27, 2013

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?

Naren
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.
February 27, 2013

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