During Jira site import, if you have a deleted issue with a sprint or custom field value, the import will fail

XMLWordPrintable

    • 16
    • Minor
    • 2

      Issue Summary

      When doing a Jira site import into Jira Cloud, if you have an issue that is registered in any sprints and for some reason, that issue no longer exists, the import will fail.

      Steps to Reproduce

      1. Create an issue
      2. Associate with a sprint or a assign the issue a custom field value
      3. Delete the issue through the database
      4. Create a backup
      5. Import the backup in Cloud

      Note: Deleting the issue directly through the database in this context is just to replicate the behavior for this bug, this shouldn't be done in a production environment.

      Expected Results

      Site import goes through.

      Actual Results

      Jira site import fails with the following exception.

      The below exception is thrown in the Jira Cloud logs:

      Completed import stages for taskId 10071 with failure There was a problem configuring the database for import. Please contact Atlassian support. org.postgresql.util.PSQLException:

ERROR: insert or update on table "issuesprint_60db71" violates foreign key constraint "issuesprint_60db71_issue_id_fkey" Detail: Key (issue_id)=(xxxxxx) is not present in table "jiraissue".

      OR the following stacktrace may also be seen when a custom field value is associated with a missing jira issue

      Caught PSQLException for insert into "public"."AO_54307E_REQUESTTYPE_TO_ISSUE" ("ISSUE_ID", "PORTAL_KEY", "REQUEST_TYPE_KEY", "SEARCH_INDEX", "UPDATED_TIMESTAMP")
select "customfieldvalue"."issue", case when "customfieldvalue"."stringvalue" like ? then substr("customfieldvalue"."stringvalue",1,(strpos("customfieldvalue"."stringvalue",?)-1 - ?)) else ? end, case when "customfieldvalue"."stringvalue" like ? then substr("customfieldvalue"."stringvalue",(strpos("customfieldvalue"."stringvalue",?)-1 + ?)+1) when (not "customfieldvalue"."stringvalue" like ?) then "customfieldvalue"."stringvalue" else ? end, "customfieldvalue"."stringvalue", case when "customfieldvalue"."updated" is not null then EXTRACT(EPOCH FROM "customfieldvalue"."updated") * 1000 else -1 end from "public"."customfieldvalue" "customfieldvalue" where "customfieldvalue"."customfield" = ? and "customfieldvalue"."stringvalue" is not null and "customfieldvalue"."issue" is not null and "customfieldvalue"."id" in (select max("temp"."id") from "public"."customfieldvalue" "temp" where "temp"."issue" = "customfieldvalue"."issue" and "temp"."customfield" = "customfieldvalue"."customfield")com.atlassian.servicedesk.bootstrap.upgradeanddowngrade.upgradetasks.AUT_0036_RequestTypeToIssueMigrationUpgradeTask.doUpgrade(AUT_0036_RequestTypeToIssueMigrationUpgradeTask.java:61)

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "AO_54307E_REQUESTTYPE_TO_ISSUE" violates foreign key constraint "fk_ao_54307e_requesttype_to_issue_issue_id"
  Detail: Key (ISSUE_ID)=(10000) is not present in table "jiraissue".

      Workaround

      You can find out any issues that have custom field values but don't exist anymore using the following query:

      select c.issue,c.customfield,c.stringvalue,j.id 
from customfieldvalue c
join jiraissue j ON j.id = c.issue
where c.issue not in (select j.id from jiraissue);

OR

--Specific for Sprint values

select c.issue,c.customfield,c.stringvalue,j.id 
from customfieldvalue c
join jiraissue j ON j.id = c.issue
join customfield a ON a.id = c.id
where c.issue not in (select j.id from jiraissue) and a.customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-sprint';

      Create a backup of Jira, extract the XML files.

      Use the following grep to find the line number:

      grep -in '<CustomFieldValue id="9498796"' entities.xml

      Result:

      153008318:    <CustomFieldValue id="9498796" ....

      Use the following SED command to remove the line:

      sed -i '153008318d' entities.xml

      Repack the XML file into a zip, and proceed with the import on Cloud.

            Assignee:
            Unassigned
            Reporter:
            Daniel Brito [Atlassian]
            Votes:
            1 Vote for this issue
            Watchers:
            14 Start watching this issue

              Created:
              Updated:
              Resolved: