Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-72543

Restore from Cloud to Server fails with Error executing update for SQL statement 'ALTER TABLE public."AO_2C4E5C_MAILBLOCKLISTS" ADD CONSTRAINT "fk_ao_2c4e5c_mailblocklists_project_id" FOREIGN KEY ("PROJECT_ID") REFERENCES public."PROJECT"("ID")'

XMLWordPrintable

      Summary

      The restore from Cloud to Server fails at 90% during the Active Objects restore with the following error: 

      2021-06-23 17:37:15,705-0300 JiraImportTaskExecutionThread-1 ERROR anonymous 1056x34x1 1ofulst 0:0:0:0:0:0:0:1 /secure/SetupImport.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_2C4E5C_MAILBLOCKLISTS" ADD CONSTRAINT "fk_ao_2c4e5c_mailblocklists_project_id" FOREIGN KEY ("PROJECT_ID") REFERENCES public."PROJECT"("ID")'

      Steps to Reproduce

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

      Expected Results

      Backup should be restored with no issues.

      Actual Results

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

      2021-06-23 17:37:15,705-0300 JiraImportTaskExecutionThread-1 ERROR anonymous 1056x34x1 1ofulst 0:0:0:0:0:0:0:1 /secure/SetupImport.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_2C4E5C_MAILBLOCKLISTS" ADD CONSTRAINT "fk_ao_2c4e5c_mailblocklists_project_id" FOREIGN KEY ("PROJECT_ID") REFERENCES public."PROJECT"("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)
	at com.atlassian.activeobjects.backup.ActiveObjectsForeignKeyCreator.create(ActiveObjectsForeignKeyCreator.java:43)
	at com.atlassian.activeobjects.backup.ForeignKeyAroundImporter.after(ForeignKeyAroundImporter.java:26)
	at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:47)
	at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:70)
	at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:151)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:574)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:749)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:323)
	at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.unsafeCall(DataImportAsyncCommand.java:82)
	at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:64)
	at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:30)
	at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:177)
	at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:149)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.PROJECT" does not exist
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
	at com.atlassian.jira.ofbiz.sql.StatementWrapper.executeUpdate(StatementWrapper.java:29)
	at com.atlassian.jira.diagnostic.connection.DiagnosticStatement.lambda$executeUpdate$8(DiagnosticStatement.java:70)
	at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:70)
	at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
	at com.atlassian.jira.diagnostic.connection.DiagnosticStatement.executeUpdate(DiagnosticStatement.java:70)
	at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:34)
	... 19 more

      Notes

      • This issue is very similar to JRASERVER-69564
      • The problem is caused by a table in activeobjects.xml: 
            <foreignKey fromTable="AO_2C4E5C_MAILBLOCKLISTS" fromColumn="PROJECT_ID" toTable="PROJECT" toColumn="ID"/>

      Workaround

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

        <table name="AO_2C4E5C_MAILBLOCKLISTS">
    <column name="BLOCK_STRING" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="CREATED_TIMESTAMP" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="CREATED_USER_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="4" precision="10"/>
    <column name="IS_DOMAIN" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
    <column name="PROJECT_ID" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="UPDATED_TIMESTAMP" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="UPDATED_USER_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <foreignKey fromTable="AO_2C4E5C_MAILBLOCKLISTS" fromColumn="PROJECT_ID" toTable="PROJECT" toColumn="ID"/>
  </table>

  <data tableName="AO_2C4E5C_MAILBLOCKLISTS">
    <column name="BLOCK_STRING"/>
    <column name="CREATED_TIMESTAMP"/>
    <column name="CREATED_USER_ID"/>
    <column name="ID"/>
    <column name="IS_DOMAIN"/>
    <column name="PROJECT_ID"/>
    <column name="UPDATED_TIMESTAMP"/>
    <column name="UPDATED_USER_ID"/>
  </data>

      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 table entries: 
         <table name="AO_2C4E5C_MAILBLOCKLISTS">
    <column name="BLOCK_STRING" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="CREATED_TIMESTAMP" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="CREATED_USER_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="4" precision="10"/>
    <column name="IS_DOMAIN" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
    <column name="PROJECT_ID" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="UPDATED_TIMESTAMP" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="UPDATED_USER_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <foreignKey fromTable="AO_2C4E5C_MAILBLOCKLISTS" fromColumn="PROJECT_ID" toTable="PROJECT" toColumn="ID"/>
  </table>

         <data tableName="AO_2C4E5C_MAILBLOCKLISTS">
    <column name="BLOCK_STRING"/>
    <column name="CREATED_TIMESTAMP"/>
    <column name="CREATED_USER_ID"/>
    <column name="ID"/>
    <column name="IS_DOMAIN"/>
    <column name="PROJECT_ID"/>
    <column name="UPDATED_TIMESTAMP"/>
    <column name="UPDATED_USER_ID"/>
  </data>
      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

              psouza Pedro Souza
              psouza Pedro Souza
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: