How to bulk update the names of select options in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article presents a way to change the names (descriptions) of many select options at once in Jira. If the amount of option names to change is not "big enough", you should opt for the UI approach of renaming individual options one by one, instead.
Please note this is not about changing the selections or Ids of the options — only their displayed names.
The solution presented here consists of direct database manipulation of data (DML) and is not supported by Atlassian.
You should perform database backups and validate the solution in a test environment before applying it to production. You may reach out to the Community should you have questions on non-supported procedures.
Environment
All versions of Jira Core 8.x.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The solution consists of 3 major steps:
Identify the records to update
Update the records
Flush the cache
Note you should run all steps for each environment you work on, as the database records ids may be different.
Since we're changing just the names of the options and not their Ids or selected values in issues, no reindex is necessary. Jira fetches the selected option from the index (when searching or showing search results), but fetches the option names from the local node's cache.
1) Identify the records to update
With the custom field id, run the following query on the database (example id 10300):
1
2
3
4
5
select cf.customfield, cf.id, fcs.configname, cf.customvalue
from customfieldoption cf
join fieldconfigscheme fcs on fcs.id = cf.customfieldconfig
where cf.customfield = 10300
order by fcs.configname, cf.customvalue;Example output
1
2
3
4
5
6
7
8
9
10
11
12
customfield | id | configname | customvalue
-------------+-------+----------------+--------------------
10300 | 10200 | Example scheme | Old option A
10300 | 10201 | Example scheme | Old option B
10300 | 10202 | Example scheme | Old option C
10300 | 10203 | Example scheme | Old option D
10300 | 10204 | Example scheme | Old option E
10300 | 10205 | Example scheme | Old option F
10300 | 10206 | Example scheme | Old option G
10300 | 10207 | Test | Another option
10300 | 10208 | Test | Yet another option
(9 rows)
Notice all field configurations will show up.
2) Update the records
Based on the ids returned from step 1, you may write the update commands you need:
1
2
3
4
5
6
7
update customfieldoption set customvalue = 'New Option A' where id = 10200;
update customfieldoption set customvalue = 'New Option B' where id = 10201;
update customfieldoption set customvalue = 'New Option C' where id = 10202;
update customfieldoption set customvalue = 'New Option D' where id = 10203;
update customfieldoption set customvalue = 'New Option E' where id = 10204;
update customfieldoption set customvalue = 'New Option F' where id = 10205;
update customfieldoption set customvalue = 'New Option G' where id = 10206;Again mind the ids are from the desired options and field configurations.
3) Flush the cache
After the database changes have been completed, you should flush Jira's cache to avoid confusion as the previous option names will still show up.
The safest and often quickest way to do this is to perform a Jira restart.
a) Jira restart
A Jira restart will rebuild all the caches from the database again, including the ones from CachedOptionsManager that we're interested here.
If you're running Jira Data Center, you may perform a rolling restart (one node at a time) to avoid outage and this will suffice. The caches from CachedOptionsManager are local to each node and not copied over from running nodes upon restart (like the user and group caches, for example).
b) Cache flush through apps
Some apps have built-in scripts or features to flush cache. This works the same as option b and a restart is still the best option.
Was this helpful?