How to retrieve a deleted Custom Field Context scheme 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 will cover the steps to recover a deleted custom field context/configuration scheme.
You will need either a full Database backup that contains the custom field context data or a full XML backup that contains the deleted custom field context in order to restore it into a new intermediary instance or an existing lower environment.
Environment
This article is applicable to all versions of Jira 7.x and 8.x.
Diagnosis
When a custom field context is deleted, its field options Id's are deleted from the database table customfieldoption. As a result, any field options unique to that context are removed from the instance. Previously selected options remain associated with issues in the database, they are just no longer visible in the UI as the option Id has been removed.
Since the context field options are identified by a sequential numeric Id and not their names inside the database, recreating the context with the same name and options will not re-associate issues with their old values. Therefore, to recover them, it's necessary to retrieve the information from a database prior to their deletion.
Solution
Restore the context data directly from the database. There are two options:
Recovery option 1 - Full database or XML backup restore
With this option, we'd need to restore the most recent backup taken prior to the deletion of the custom field context.
Pros: No direct database manipulation, fewer steps to be performed.
Cons: Data that was created after the most recent backup will be lost.
Recovery option 2 - Restore the most recent backup prior to the change to a test system to retrieve the IDs of the missing options
With this option you'll retrieve the original context data from an intermediate instance. Next, you'll recreate the options exactly as they were on the UI. Even though these new options have the same names and values, they will have new Ids, so to completely restore the options to the issues you'll replace the old deleted Id references with new ones. As a result, the custom field will become visible again when viewing an issue and for the issues in that context.
Pros: No data loss expected from the deletion time to restore time
Cons: Downtime when restarting; requires direct database manipulation
1. Restore either the database backup or an XML backup of the data taken before the custom field context was deleted into an intermediate instance.
We recommend following the guidelines of Creating a test environment for Jira when performing the restore. From here on, we'll call this server the source. We'll refer to the instance of Jira that had the custom field context scheme deleted as the target.
2. Into the source server, go to Issues > Custom Fields > Affected Custom Field > Configure and grab its ID from the URL (e.g., customFieldId=10300)
3. Run the following query on the source system database to retrieve the custom field options' Ids. Use the field id noted in step 2. Take notes of the options and their ids, we'll use them later:
1
SELECT id,customvalue FROM customfieldoption where customfield=10300; 4. Execute the query above with the same custom field id in the target database to compare results and see what are the missing options.
5. Next, move to the target UI, and recreate the context and its options, exactly as they are in the source system UI.
6. Run this query on the target to see the new custom values created for the options in the database. Note that the custom field id is likely different from the source system, be sure to grab the correct Id from the URL to use in the below query:
1
SELECT id,customvalue FROM customfieldoption where customfield=10300;ℹ️ Take note of the new custom values and ids as well.
7. Manually fix the custom field options relationship with issues by directly setting the new equivalent options id in place of the deleted options on the database. Steps:
7.1. Stop Jira
Important: Perform a full database backup of the target system before proceeding!
7.2. Run this query on the target's database to fix the options on those issues where the context was deleted:
1
update customfieldvalue set stringvalue='10106' where stringvalue ='10103';
- Where stringvalue='10106': → new option id, created on the target and noted from step 6
- Where stringvalue='10103' → old equivalent option id, noted from the source database on step 3
You can use something like the following SQL query to exclude archived issues from being updated ( for sufficiently large data sets ):
UPDATE customfieldvalue cfv SET stringvalue=
'10106'
FROM jiraissue ji WHERE cfv.issue = ji.id AND ji.archived !=
'Y'
AND cfv.stringvalue =
'10103'
;
You'll want to double-check the datatype of the archived column in the jiraissue table as it can vary depending on the database engine in use
7.3. Restart Jira
7.4. The issues should have their custom field options back. View an issue and verify that you can you see the custom field and the selected option for it.
Was this helpful?