Database update without stopping Jira services Edited

Can we update customfield table "Stringvalue" column for one of the single list custom field which has LOV's without bring Jira services down.

If yes, please let me know the impact on Application

 

Note : I have done this in our test environment and i didnt notice any impact on the app, however i would like know suggestions/recommendations from experts here.

scenario:

i have a custom field called Dept. which has lov's 

engg

ops

tech

admin

which inturn will have stringvalues associated with these lov's and stores in Jira DB under customfield value table column "String value".

 

now i have to replace below 3 options

tech

ops

admin

with option "SA".

can i query for these 3 options indivually based on string values(option id) and update with "SA" in DB and re-index projects that are associated with this custom field?

 

1 answer

1 vote

No, absolutely not.

JIRA caches all sorts of things, and you should never, never, never alter a JIRA database when it is running.  In general, you should not mess with the database even when it's not running, because it's not as simple as most  people think.

What you should do here is correct the values using a bulk edit.  If you insist on doing it with database changes (don't), then take JIRA down, take a full database backup, make the changes, restart it and run a *full* re-index (project indexing won't be enough for your changes)

What if i have this custom field is associated with around 2 lakh issues ? Jira UI provides only 1000 issues to bulk edit.

Do it in batches, or script it over the REST interface, or script it in ScriptRunner or one of the other automation add-ons.  (p.s. I've no idea what a lakh is, so I'm guessing it's larger than 1,000)

Sorry i mean to say 2,00,000 issues(two hundred thousand).

I've learned new things today, thank you :-)

doing it in batches would be nightmare, I tried using script and unless we run project re-indexing the new values doesn’t show up in the jira UI.

Depends on how you write the script.  If you do it in something like ScriptRunner, the system will look after the indexing for you.  So will batches of bulk edits, and scripting over REST.  Because they all go through the API.

I don't know what you're scripting here, but it's not indexing as it goes.   I suspect you'd need a full re-index, not just a project one.

Lol (for the lakh).

I have done this couple of time with my understanding on Jira DB schemes but really i never faced any issues in Jira, but this time I am little curious (It's never too late…) know the impacts on changing things at the database level. What exactly happens when you do such changes at the DB level? Since Jira caches everything and it doesn’t understand the changes made at Jira DB? If so it shouldn’t reflect or fetch any value when you query (in UI) for new updated value right? And after re-indexing projects it shouldn’t reflect the new value in the view screen of the issue.

Thanks Nic, learning new things.

Do we loss data (updated or new incoming issues) by doing DB changes? 

Yes, you can easily corrupt or destroy data.  The code expects exclusive write access to the database, and if you're poking it at the same time as JIRA is writing, it can go horribly wrong.  It's rare, yes, but it is possible to break entire tables.

More common is that JIRA is caching (in several ways) data that you might be wanting to change, and there's two ways that can break - if the cache is inconsistent with the database, then the users can be looking at the wrong data, and the other, more painful case is that the cache data goes back into the database, stomping on your changes.

The index is different - it will serve up wrong data to the users until you've done a re-index (you need a full one to remove the records you've destroyed - background and project re-indexing will make most of it right, but you may still find old data that you've effectively deleted popping up).  But an inconsistent index will not do any damage to anything, just mislead the users until a re-index is done.

The first time I ran into this, it took me a while to work out what it had done.  The second time I ran into it, it was pretty spectacular - the jiraissue table became unreadable, which means "go get the last backup".  The admin hadn't even written to jiraissue, just customfieldvalue, but it broke, and it broke hard.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

216 views 1 18
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