Summary

After performing an XML restore, Advanced Roadmaps plans are not visible, although the source instance has plans. This article discusses one of the causes of this.

Environment

Jira Software Server / Data Center 8.0.0 and any higher version.

Diagnosis

To verify this cause, check the points below:

• Run the SQL query below on the Jira database of the destination/target instance and confirm that the Advance Roadmaps plans were not restored.

  select "TITLE" from "AO_D9132D_PLAN";

• Inspect the atlassian-jira.log and look for the ERROR "Error during ActiveObjects restore" during the XML restore. In the below example, an ERROR occurred while performing a restore on the Active Object table. The error is related to third party plugin Structure.

  2023-11-14 01:41:22,137+0000 JiraImportTaskExecutionThread-1 ERROR Migration 854x523x1 test 152.58.208.153,100.81.8.11 /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 plugin Structure(com.almworks.jira.structure) #8.2.2 (table AO_8BAD1B_PROPERTY):
      at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26)
      at com.atlassian.dbexporter.importer.DataImporter$BatchInserter.flush(DataImporter.java:430)
      at com.atlassian.dbexporter.importer.DataImporter$BatchInserter.close(DataImporter.java:435)
      at com.atlassian.dbexporter.importer.DataImporter.importTable(DataImporter.java:121)
      at com.atlassian.dbexporter.importer.DataImporter.access$000(DataImporter.java:42)
      at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:72)
      at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:65)
      at com.atlassian.dbexporter.jdbc.JdbcUtils.withConnection(JdbcUtils.java:29)
      at com.atlassian.dbexporter.importer.DataImporter.doImportNode(DataImporter.java:65)
      at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:49)
      at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:73)
      at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:170)
      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:829)
  Caused by: java.sql.BatchUpdateException: Batch entry 1 INSERT INTO public."AO_8BAD1B_PROPERTY" ("C_KEY", "C_VALUE") VALUES ('maintenance.scheduled.nextRunTime', '1699930800000') was aborted: ERROR: duplicate key value violates unique constraint "AO_8BAD1B_PROPERTY_pkey"
    Detail: Key ("C_KEY")=(maintenance.scheduled.nextRunTime) already exists.  Call getNextException to see other errors in the batch.
      at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:171)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2298)
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:520)
      at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:878)
      at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1644)
      at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345)
      at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345)
      at com.atlassian.jira.ofbiz.sql.StatementWrapper.executeBatch(StatementWrapper.java:154)
      at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeBatch$17(DiagnosticPreparedStatement.java:146)
      at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91)
      at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62)
      at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeBatch(DiagnosticPreparedStatement.java:146)
      at com.atlassian.dbexporter.importer.DataImporter$BatchInserter.flush(DataImporter.java:423)
      ... 24 more
  Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_8BAD1B_PROPERTY_pkey"
    Detail: Key ("C_KEY")=(maintenance.scheduled.nextRunTime) already exists.
      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297)
      ... 36 more 

  
  

Cause

During the Active Objects restore phase of the XML restore, one of the AO tables of the Structure plugin failed to restore due to a database error. This resulted in the XML restore process failing and affected the restoration of other AO tables, including the AO_D9132D_PLAN, which contains the Advanced Roadmaps plans.

Solution

To make the plans visible, the restoration of Active Objects table specifically AO_D9132D_PLAN must be completed without errors, if any errors are encountered during restore, it impacts the restore of other Active Object tables.

In the example above involving the third-party plugin Structure, it was necessary to clear the 'AO_8BAD1B_PROPERTY' table and disable the Structure plugin before attempting the restore again. Following these steps, as advised by the vendor's support team, enabled the successful restoration and visibility of Advanced Roadmap plans.

If similar errors occur with a different Active Object table, contacting the relevant vendor for resolution is recommended.