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

Importing XML backup from Cloud to Jira Server fails while restoring Active Object with following SQL exception. The error occurs while adding a foreign key constraint for table AO_56464C_APPROVAL_SOURCE, AO_56464C_APPROVER_LIST

There was a problem restoring ActiveObjects data for the <unknown plugin> plugin. Caught exception with following message: ERROR: relation "public.JIRAISSUE" does not exist. Please check the log for details.

Environment

Jira Server/Data Center  

Diagnosis

The following appears in atlassian-jira.log: 

 2022-07-23 16:03:30,933 http-nio-8080-exec-15 INFO xxxx xxxxx xxxx  /secure/admin/XmlRestore.jspa [c.a.j.w.action.util.ImportAll] Running ImportAll

 Restoration Looks good until 90%:

2022-07-23 16:04:11,013 JiraImportTaskExecutionThread-1 INFO xxxx xxxxx xxxx  /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 90% complete...

Later in the log, the restoration is crashed due to an SQL Exception while restoring the Active Objects "ERROR: relation "public.JIRAISSUE" does not exist"

2022-07-23 16:04:18,542 JiraImportTaskExecutionThread-1 ERROR xxxx xxxxx xxxx /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore
com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with <unknown plugin>:Error executing update for SQL statement 'ALTER TABLE public."AO_56464C_APPROVAL_SOURCE" ADD CONSTRAINT "fk_ao_56464c_approval_source_issue_id" FOREIGN KEY ("ISSUE_ID") REFERENCES public."JIRAISSUE"("ID")'
	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26)
	at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:46)
	at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:37)

Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_E8B6CC_ORGANIZATION_MAPPING" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_21F425_MESSAGE_AO" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_WATCHER" does not exist

Cause

This issue is caused by the following JSDCLOUD-11543 - Getting issue details... STATUS

Solution

Please follow the below steps for removing the entries that cause the issue and retry the import: 

1.  Unzip Jira cloud backup

   unzip cloudbackup.zip
   Archive:  cloudbackup.zip
     inflating: entities.xml
     inflating: activeobjects.xml

2.  Expand xml to multiple lines:
   

   xmllint activeobjects.xml --format > activeobjects_fixed.xml

3. Remove the foreign key entries from activeobjects_fixed.xml
   

   <foreignKey fromTable="AO_56464C_APPROVAL_SOURCE" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID" />
   <foreignKey fromTable="AO_56464C_APPROVER_LIST" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID" /> 
   <foreignKey fromTable="AO_60DB71_LEXORANK" fromColumn="FIELD_ID" toTable="LEXORANK_FIELD_ID" toColumn="ID" />

4.  Make another copy of activeobjects.xml in the multiline format:
   

   xmllint activeobjects.xml --format > testing.xml

5. Now we will Diff the fixed file to the original to ensure only 3 lines were removed (output should look like below):

   diff testing.xml activeobjects_fixed.xml
   <foreignKey fromTable="AO_56464C_APPROVAL_SOURCE" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID" />
   <foreignKey fromTable="AO_56464C_APPROVER_LIST" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID" />
   <foreignKey fromTable="AO_60DB71_LEXORANK" fromColumn="FIELD_ID" toTable="LEXORANK_FIELD_ID" toColumn="ID" />

6. Delete the old active objects file and rename the fixed one to activeobjects.xml
   

   mv activeobjects_fixed.xml activeobjects.xml

7. Zip up the XML backup files again

   zip -r fixed.zip activeobjects.xml entities.xml

8. Move the backup to the import directory on the destination instance and perform the import again