JCMA migration error: "project-export Couldn't migrate Request Type with id <request type ID> linked to Issue Type with id <issue type ID>"
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Jira Cloud Migration Assistant app supports the migration of JSM (Service Management/former Service Desk) Projects. JSM-specific related errors might surface during a JCMA plan execution.
This error is one of the potential ones an admin may face while migrating data from a Jira Server/DC instance along with JSM Projects over to an Atlassian Jira Cloud site.
Environment
- JCMA 1.6.6 and higher
- Jira Server/Data Center 7.6.0 and higher
- Jira Service Management Server/DC 3.9.0 and higher
Error
This error message below will surface when there is a JSM Project Request Type associated with an Issue Type that either was deleted or is not associated with the Issue Type Scheme linked to that project.
One situation where this error can happen is when an Issue Type is associated with a Request Type and later on, that Issue Type is unlinked from the Issue Type Scheme. That change isn't propagated into the Request Types section, keeping the previous reference there.
<date> <time> ERROR <project key> project-export Couldn't migrate Request Type with id <request type ID> linked to Issue Type with id <issue type ID>. Add the Issue Type with id <issue type ID> to the Issue type Scheme of the project with id <project ID> to resolve.
Solution
Detection
Get the invalid Issue Types
First, we must identify which Issue Types are being referenced in the error message.
This SQL query will help with that. It'll bring all Issue Types associated with the Request Types of the provided project.
And it'll also list which Issue Types are invalid or not associated with the Issue Type Scheme of that project.
Look for the entry "Issue Type invalid or not associated with the Issue Type Scheme" in the query results.
Replace both placeholders for the <project key> value with the project key of the impacted project (from the JCMA error message)
Edit the lines 12 and 27 to include the <project key>
WITH request_type_issue_type_project AS (
SELECT vpf."ID" AS request_type_id
, vpf."NAME" AS request_type_name
, p.pkey AS project_key
, vp."NAME" AS project_name
, vpf."ISSUE_TYPE_ID" AS issue_type_id
, it.pname AS issue_type_name
FROM "AO_54307E_VIEWPORT" vp
JOIN "AO_54307E_VIEWPORTFORM" vpf ON (vpf."VIEWPORT_ID" = vp."ID")
JOIN project p ON (p.pname = vp."NAME")
LEFT JOIN issuetype it ON (it.id = vpf."ISSUE_TYPE_ID"::text)
WHERE p.pkey = '<project key>'
), issue_type_project AS (
SELECT p.id AS project_id
, p.pname AS project_name
, p.pkey AS project_key
, it.id AS issue_type_id
, it.pname AS issue_type_name
FROM project p
LEFT JOIN configurationcontext cc ON (p.id = cc.project)
LEFT JOIN fieldconfigscheme fcs ON (cc.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfigschemeissuetype fcsit ON (fcsit.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfiguration fc ON (fcsit.fieldconfiguration = fc.id)
LEFT JOIN optionconfiguration oc ON (oc.fieldconfig = fc.id)
LEFT JOIN issuetype it ON (oc.optionid = it.id::text)
WHERE cc.customfield = 'issuetype'
AND p.pkey = '<project key>'
AND oc.fieldid = 'issuetype'
)
SELECT rt.request_type_id AS "Request Type ID"
, rt.request_type_name AS "Request Type Name"
, rt.project_key AS "Project Key"
, rt.project_name AS "Project Name"
, rt.issue_type_name AS "Issue Type Name"
, COALESCE(it.issue_type_name, 'Issue Type invalid or not associated with the Issue Type Scheme') AS "Affected Issue Type"
FROM request_type_issue_type_project rt
LEFT JOIN issue_type_project it ON (rt.issue_type_id::text = it.issue_type_id);
Edit the lines 17 and 31 to include the <project key>
SELECT rt.request_type_id AS "Request Type ID"
, rt.request_type_name AS "Request Type Name"
, rt.project_key AS "Project Key"
, rt.project_name AS "Project Name"
, rt.issue_type_name AS "Issue Type Name"
, COALESCE(it.issue_type_name, 'Issue Type invalid or not associated with Issue Type Scheme') AS "Affected Issue Type"
FROM ( SELECT vpf.ID AS request_type_id
, vpf.NAME AS request_type_name
, p.pkey AS project_key
, vp.NAME AS project_name
, vpf.ISSUE_TYPE_ID AS issue_type_id
, it.pname AS issue_type_name
FROM AO_54307E_VIEWPORT vp
JOIN AO_54307E_VIEWPORTFORM vpf ON (vpf.VIEWPORT_ID = vp.ID)
JOIN project p ON (p.pname = vp.NAME)
LEFT JOIN issuetype it ON (it.id = CAST(vpf.ISSUE_TYPE_ID AS UNSIGNED))
WHERE p.pkey = '<project key>' ) rt
LEFT JOIN ( SELECT p.id AS project_id
, p.pname AS project_name
, p.pkey AS project_key
, it.id AS issue_type_id
, it.pname AS issue_type_name
FROM project p
LEFT JOIN configurationcontext cc ON (p.id = cc.project)
LEFT JOIN fieldconfigscheme fcs ON (cc.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfigschemeissuetype fcsit ON (fcsit.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfiguration fc ON (fcsit.fieldconfiguration = fc.id)
LEFT JOIN optionconfiguration oc ON (oc.fieldconfig = fc.id)
LEFT JOIN issuetype it ON (CAST(oc.optionid AS UNSIGNED) = it.id)
WHERE cc.customfield = 'issuetype'
AND p.pkey = '<project key>'
AND oc.fieldid = 'issuetype') it ON (rt.issue_type_id = it.issue_type_id)
Edit the lines 12 and 27 to include the <project key>
WITH request_type_issue_type_project AS (
SELECT vpf.ID AS request_type_id
, vpf.NAME AS request_type_name
, p.pkey AS project_key
, vp.NAME AS project_name
, vpf.ISSUE_TYPE_ID AS issue_type_id
, it.pname AS issue_type_name
FROM AO_54307E_VIEWPORT vp
JOIN AO_54307E_VIEWPORTFORM vpf ON (vpf.VIEWPORT_ID = vp.ID)
JOIN project p ON (p.pname = vp.NAME)
LEFT JOIN issuetype it ON (it.id = CAST(vpf.ISSUE_TYPE_ID AS NUMBER))
WHERE p.pkey = '<project key>'
), issue_type_project AS (
SELECT p.id AS project_id
, p.pname AS project_name
, p.pkey AS project_key
, it.id AS issue_type_id
, it.pname AS issue_type_name
FROM project p
LEFT JOIN configurationcontext cc ON (p.id = cc.project)
LEFT JOIN fieldconfigscheme fcs ON (cc.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfigschemeissuetype fcsit ON (fcsit.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfiguration fc ON (fcsit.fieldconfiguration = fc.id)
LEFT JOIN optionconfiguration oc ON (oc.fieldconfig = fc.id)
LEFT JOIN issuetype it ON (CAST(oc.optionid AS NUMBER) = it.id)
WHERE cc.customfield = 'issuetype'
AND p.pkey = '<project key>'
AND oc.fieldid = 'issuetype'
)
SELECT rt.request_type_id AS "Request Type ID"
, rt.request_type_name AS "Request Type Name"
, rt.project_key AS "Project Key"
, rt.project_name AS "Project Name"
, rt.issue_type_name AS "Issue Type Name"
, COALESCE(it.issue_type_name, 'Issue Type invalid or not associated with Issue Type Scheme') AS "Affected Issue Type"
FROM request_type_issue_type_project rt
LEFT JOIN issue_type_project it ON (rt.issue_type_id = it.issue_type_id);
Edit the lines 12 and 27 to include the <project key>
WITH request_type_issue_type_project AS (
SELECT vpf.ID AS request_type_id
, vpf.NAME AS request_type_name
, p.pkey AS project_key
, vp.NAME AS project_name
, vpf.ISSUE_TYPE_ID AS issue_type_id
, it.pname AS issue_type_name
FROM AO_54307E_VIEWPORT vp
JOIN AO_54307E_VIEWPORTFORM vpf ON (vpf.VIEWPORT_ID = vp.ID)
JOIN project p ON (p.pname = vp.NAME)
LEFT JOIN issuetype it ON (it.id = CAST(vpf.ISSUE_TYPE_ID AS NVARCHAR(max)))
WHERE p.pkey = '<project key>'
), issue_type_project AS (
SELECT p.id AS project_id
, p.pname AS project_name
, p.pkey AS project_key
, it.id AS issue_type_id
, it.pname AS issue_type_name
FROM project p
LEFT JOIN configurationcontext cc ON (p.id = cc.project)
LEFT JOIN fieldconfigscheme fcs ON (cc.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfigschemeissuetype fcsit ON (fcsit.fieldconfigscheme = fcs.id)
LEFT JOIN fieldconfiguration fc ON (fcsit.fieldconfiguration = fc.id)
LEFT JOIN optionconfiguration oc ON (oc.fieldconfig = fc.id)
LEFT JOIN issuetype it ON (CAST(oc.optionid AS NVARCHAR(MAX)) = it.id)
WHERE cc.customfield = 'issuetype'
AND p.pkey = '<project key>'
AND oc.fieldid = 'issuetype'
)
SELECT rt.request_type_id AS "Request Type ID"
, rt.request_type_name AS "Request Type Name"
, rt.project_key AS "Project Key"
, rt.project_name AS "Project Name"
, rt.issue_type_name AS "Issue Type Name"
, COALESCE(it.issue_type_name, 'Issue Type invalid or not associated with the Issue Type Scheme') AS "Affected Issue Type"
FROM request_type_issue_type_project rt
LEFT JOIN issue_type_project it ON (rt.issue_type_id = it.issue_type_id);
Example of an affected environment:
The Issue Type "DeleteMe", associated with the Request Type "DelMe" is the one affected. It could've been deleted or disassociated from the Issue Type Scheme.
One can also try and edit the affected Request Type by clicking the Edit fields button. It'll present the following screen:
Resolution
The fix is to either:
- Reassociate the Issue Type with the project's Issue Type Scheme or
- Remove the affected Request Type and recreate it with a valid Issue Type association
New JCMA plan
Once you've fixed the affected Request Types, you should be good to create a new migration plan in JCMA.