Problem

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.

Diagnosis

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.

Cause

This problem can have a few causes.

1. This is caused by this bug:  JRASERVER-63226 - Getting issue details... STATUS
2. The values associated to the issues are associated to a different context than the issue's context

Workaround

Cause 1:

1. 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.

2. 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).

3. 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

4. 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).

5. Generate a new XML backup from the staging server.

6. Use the backup to restore the project into the target instance (once done destroy it).

Cause 2:

Edit the issues and select any valid values for the current context.

The SQL query below should display all the problematic issues

with contexts as (
select CF.cfname, FCS.configname, FCSIT.issuetype, CC.project, FCS.id 
from fieldconfigscheme FCS
join customfield CF on concat('customfield_',CF.id) = FCS.fieldid
join fieldconfigschemeissuetype FCSIT on FCSIT.fieldconfigscheme = FCS.id 
join configurationcontext CC on CC.fieldconfigscheme = FCS.id
where CF.cfname = 'Custom Cascade'
),
fullContext as (
select concat(concat(P.pkey,'-'),I.issuenum) as Issue, I.id as Issueid, C.cfname, C.configname, C.issuetype, C.project, C.id as ContextID
from jiraissue I
join project P on P.id = I.project 
join contexts C on I.issuetype = C.issuetype and I.project = C.project
),
typeContext as (
select concat(concat(P.pkey,'-'),I.issuenum) as Issue, I.id as Issueid, C.cfname, C.configname, C.issuetype, C.project, C.id as ContextID
from jiraissue I
join project P on P.id = I.project 
join contexts C on I.issuetype = C.issuetype and C.project is null
left join fullContext FC on FC.Issue = concat(concat(P.pkey,'-'),I.issuenum)
where FC.Issue is null
),
projectContext as (
select concat(concat(P.pkey,'-'),I.issuenum) as Issue, I.id as Issueid, C.cfname, C.configname, C.issuetype, C.project, C.id as ContextID
from jiraissue I
join project P on P.id = I.project 
join contexts C on I.project = C.project and C.issuetype is null
left join fullContext FC on FC.Issue = concat(concat(P.pkey,'-'),I.issuenum)
where FC.Issue is null
),
globalContext as (
select concat(concat(P.pkey,'-'),I.issuenum) as Issue, I.id as Issueid, C.cfname, C.configname, C.issuetype, C.project, C.id as ContextID
from jiraissue I
join project P on P.id = I.project 
join contexts C on C.project is null and C.issuetype is null
left join fullContext FC on FC.Issue = concat(concat(P.pkey,'-'),I.issuenum)
left join typeContext TC on TC.Issue = concat(concat(P.pkey,'-'),I.issuenum)
left join projectContext PC on PC.Issue = concat(concat(P.pkey,'-'),I.issuenum)
where FC.Issue is null
and TC.Issue is null
and PC.Issue is null
),
issueContexts as (
select * 
from fullContext
union
select * 
from typeContext
union
select * 
from projectContext
union
select * 
from globalContext
)
select IC.issue,IC.cfname,IC.configname as IssueContext,CFOP.customvalue as ParentValue, CFOC.customvalue as ChildValue, FCS.configname as ValueContext
from issueContexts IC 
join customfieldvalue CFV on CFV.issue = IC.Issueid
join customfield CF on CF.id = CFV.customfield and CF.cfname = IC.cfname
join customfieldoption CFOC on cast(CFOC.id as varchar) = CFV.stringvalue and CFOC.parentoptionid is not null 
join customfieldoption CFOP on CFOP.id = CFOC.parentoptionid 
join fieldconfigscheme FCS on FCS.id = CFOC.customfieldconfig 
where IC.contextid != CFOC.customfieldconfig 
order by 1

If you need to run this in Oracle, you should only need to change the casting "cast(CFOC.id as varchar)" to "to_char(CFOC.id)"