Restore from Cloud to Server fails with mismatched data type error on columns PROJECT.ID and AO_2C4E5C_MAILWHITELISTS.PROJECT_ID

Summary

The restore from Cloud to Server fails with the following message in the UI:

There was a problem restoring ActiveObjects data for the <unknown plugin> plugin. Caught exception with the following message: Column 'jiraschema.PROJECT.ID' is not the same data type as referencing column 'AO_2C4E5C_MAILWHITELISTS.PROJECT_ID' in foreign key 'fk_ao_2c4e5c_mailwhitelists_project_id'. Please check the log for details.

Steps to Reproduce

1. Export a backup from Service Desk Cloud.
2. Restore the backup into Service Desk Server.

Expected Results

Backup should be restored with no issues.

Actual Results

The restore fails, the following errors is thrown in the log (example: SQL Server):

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column 'dbo.PROJECT.ID' is not the same data type as referencing column 'AO_2C4E5C_MAILWHITELISTS.PROJECT_ID' in foreign key 'fk_ao_2c4e5c_mailwhitelists_project_id'.

Notes

• This issue is very similar to JRASERVER-67706.
• The problem is caused by a line in activeobjects.xml:

      <foreignKey fromTable="AO_2C4E5C_MAILWHITELISTS" fromColumn="PROJECT_ID" toTable="PROJECT" toColumn="ID"/>
  

Workaround

The problematic line has to be removed from activeobjects.xml file:

    <foreignKey fromTable="AO_2C4E5C_MAILWHITELISTS" fromColumn="PROJECT_ID" toTable="PROJECT" toColumn="ID"/>

The following steps can be used to perform and validate this change:

1. Unzip 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 && mv activeobjects_fixed.xml activeobjects.xml 
   

3. Remove the foreign key entry.
4. Zip backup back together.

   zip -r cloudbackup_fixed.zip activeobjects.xml entities.xml
   

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