Database update without stopping Jira services

[Vijay Sv]

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?

[Nic Brough -Adaptavist-]

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)

[Vijay Sv]

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

[Nic Brough -Adaptavist-]

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)

[Vijay Sv]

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

[Nic Brough -Adaptavist-]

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

[Vijay Sv]

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.

[Nic Brough -Adaptavist-]

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.

[Vijay Sv]

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.

[Vijay Sv]

Thanks Nic, learning new things.

[Vijay Sv]

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

[Nic Brough -Adaptavist-]

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.

[Vijay Sv]

Thanks for the info @Nic Brough -Adaptavist-.