Project import fails due to missing custom field options in Jira server
The information in this page relates to Database Manipulation in JIRA. Consequently, Atlassian Support cannot guarantee to provide any support for the steps described in this page as database manipulation is not covered under Atlassian Support Offerings. Please be aware that this material is provided for your information only and that you use it at your own risk.
Project Import is blocked as JIRA can't find custom field options to be imported.
The Project Import validator returns a similar error to this:
The custom field 'Select List' requires option 'One Two Three' for the import but it does not exist in the current JIRA instance.
This usually affects Select List custom fields that have options.
The custom field options are available in the target instance (so the above error message is kind of misleading).
The problem is actually with the source data.
Related bugs are tracked here:
Alwaysyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
It is important not to perform these changes directly in a production database (except that the source instance will no longer be used after the Project Import). Instead, we recommend setting up a staging server using an XML backup from the source instance, performing the changes there then generating a new backup from that staging server for Project Import.
The staging server should then be destroyed as this workaround, while serving Project Import well (in other words data will be imported correctly into the target instance), also messes up the custom field option association of the staging server itself (so it shouldn't be used any more - explained below).
So the workaround is to modify the database of a copy of the source instance to "fix" the custom field option association specifically for the Project Import only.
You're supposed to have a staging server ready at this step. The following database modification will be performed there.
In this example, we use a custom field with the name of 'Car'. Replace 'Car' with the name of your affected custom field.
Run this query to identify the ID of the custom field, using its name (case-sensitive):
select ID, cfname from customfield where cfname = 'Car';
Take note of the custom field ID in column ID.
Run this query to identify which custom field configuration is being used by the custom field (replace 10110 with the custom field ID from step 1):
select distinct customfieldconfig from customfieldoption where customfield = 10110; // if you want to view more details, run this query instead: // select * from customfieldoption where customfield = 10110;
Take note of the field config ID(s) in column customfieldconfig (each ID is a different context of the custom field that can be seen from the GUI, and they have one or more options).
Run this query to identify which custom field config scheme uses which custom field configuration (replace 10110 with the custom field ID from step 1):
select cc.customfield, cc.PROJECT, fit.ISSUETYPE, fit.FIELDCONFIGSCHEME, fit.FIELDCONFIGURATION from fieldconfigschemeissuetype fit inner join configurationcontext cc on fit.fieldconfigscheme = cc.fieldconfigscheme where cc.customfield = 'customfield_10110';
Sample outputs and explanations:
According to the above outputs:
- customfieldconfig10401 (query#2) refers to fieldconfiguration10401 (query#3), which uses fieldconfigscheme10401 (query#3). JIRA looks for 10401 and finds it (query#2), so this is fine
- customfieldconfig 10400 (query#2) refers to fieldconfiguration 10400 (query#3), which uses fieldconfigscheme 99999 (query#3). JIRA looks for 99999 but finds 10400 (query#2), so this is the problem that affects Project Import
What we're supposed to do is to replace 10400 with 99999 using the following query (replace 10110 with the custom field ID from step 1):
update customfieldoption set customfieldconfig = 99999 where customfield = 10110 and customfieldconfig = 10400;
If the custom field has a default value, there should be an update on genericconfiguration table:
update genericconfiguration set datakey=9999 where datakey in (select distinct customfieldconfig from customfieldoption where customfield =(select ID from customfield where cfname = 'Car'));
- If there're more association discrepancies, run the Update query for each of them, following the above pattern.
- You don't need to Update wherefieldconfiguration=fieldconfigschemefor query#3.
- There may be more entries in query#3 than query#2. This is normal if the field has one or more contexts that do not have any options. Just ignore those entries.
The Update in query#4 will causeproblemto the staging server (of the source instance) itself, as JIRA will look (in the other direction) for 99999 infieldconfiguration(for other operations/purposes), which may refer to a different or non-existent configuration. This is how the data are messed,howeverit doesn't affect Project Import (which doesn't look in that direction).
Generate a new XML backup from the staging server.
Use the backup to restore the project into the target instance (once done destroy it).
If you have difficulty performing the above steps, please raise a Support ticket and attach the outputs of the SQL queries in Steps 1, 2, 3.