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

Importing a cloud XML backup to server fails due to table constraint mismatch

    XMLWordPrintable

Details

    Description

      NOTE: This bug report is for JIRA Server. Using JIRA Cloud? See the corresponding bug report.

      Issue Summary

      When importing a Cloud backup into JIRA Server, the import would fail with the following error:

      2016-12-12 12:03:44,578 JiraImportTaskExecutionThread-1 ERROR xxxxx 723x638x1 1b1wxhy 80.193.64.226,172.31.12.212 /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_C7F17E_PROJECT_LANGUAGE ADD CONSTRAINT fk_ao_c7f17e_project_language_project_lang_revision_id FOREIGN KEY (PROJECT_LANG_REVISION_ID) REFERENCES PUBLIC.AO_C7F17E_PROJECT_LANG_REVISION(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:69)
      	at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:151)
      	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:554)
      	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:728)
      	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:324)
      	at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:60)
      	at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:28)
      	at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:157)
      	at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:133)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      	at java.lang.Thread.run(Thread.java:745)
      Caused by: org.h2.jdbc.JdbcSQLException: Table "AO_C7F17E_PROJECT_LANG_REVISION" not found; SQL statement:
      ALTER TABLE PUBLIC.AO_C7F17E_PROJECT_LANGUAGE ADD CONSTRAINT fk_ao_c7f17e_project_language_project_lang_revision_id FOREIGN KEY (PROJECT_LANG_REVISION_ID) REFERENCES PUBLIC.AO_C7F17E_PROJECT_LANG_REVISION(ID) [42102-185]
      	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
      	at org.h2.message.DbException.get(DbException.java:179)
      	at org.h2.message.DbException.get(DbException.java:155)
      	at org.h2.schema.Schema.getTableOrView(Schema.java:438)
      	at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:72)
      	at org.h2.command.CommandContainer.update(CommandContainer.java:78)
      	at org.h2.command.Command.executeUpdate(Command.java:254)
      	at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:130)
      	at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:115)
      	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
      	at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
      	at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:34)
      	... 18 more
      

      or

      com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
      Database:
      	- name:PostgreSQL
      	- version:9.3.14
      	- minor version:3
      	- major version:9
      Driver:
      	- name:PostgreSQL Native Driver
      	- version:PostgreSQL 9.1 JDBC4 (build 903)
      org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_60DB71_SPRINT_pkey"
        Detail: Key ("ID")=(<any numbers>) already exists.
      

      The error is caused because JIRA is trying to create a plugin table that has a constraint when the table providing the constraint does not exist and does not exist in the exported entities.xml file from Cloud.

      Environment

      Database H2, MySQL, Oracle and Postgres

      (though looking at this being a constraint issue, it would likely impact all database types)

      Expected Behavior

      JIRA would import the data with no issues at all.

      Actual Result

      Import would always fail.

      Workaround

      1. Extract the backup file from JIRA Cloud.
      2. Open the file activeobjects.xml in a text editor
        • You would need an editor that can edit very large files else it would be truncated.
      3. Find and remove the line:
        <foreignKey fromTable="AO_C7F17E_PROJECT_LANGUAGE" fromColumn="PROJECT_LANG_REVISION_ID" toTable="AO_C7F17E_PROJECT_LANG_REVISION" toColumn="ID" />
        
      1. Save the file.
      2. Zip the entities.xml and activeobjects.xml files back together.
      3. Attempt to re-import.

      Attachments

        Issue Links

          Activity

            People

              mwinsen Marty
              ijimoh Ismael Olusula Jimoh (Inactive)
              Votes:
              12 Vote for this issue
              Watchers:
              45 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: