Backup restore fails with a syntax error in Jira

Still need help?

The Atlassian Community is here for you.

Ask the community

Summary

  • Restoring a backup that contains Active Objects data fails - In the example below it JIRA Agile data. 

(info) Active Objects provides persistent data storage for plugins in JIRA

The following appears in the atlassian-jira.log:

2012-05-01 14:29:55,525 JiraImportTaskExecutionThread-1 ERROR bumblebee 860x2246x1 1cikk9a 172.28.14.223 /secure/admin/XmlRestore.jspa [jira.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin GreenHopper(com.pyxis.greenhopper.jira) #5.9.6:Error executing update for SQL statement 'CREATE TABLE public."AO_60DB71_COLUMN" (
    "ID" BIGSERIAL,
    "MAXIM" DOUBLE PRECISION,
    "MINIM" DOUBLE PRECISION,
    "NAME" VARCHAR(255),
    "POS" INTEGER,
    "RAPID_VIEW_ID" BIGINT,
)'
com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin GreenHopper(com.pyxis.greenhopper.jira) #5.9.6:Error executing update for SQL statement 'CREATE TABLE public."AO_
60DB71_COLUMN" (
    "ID" BIGSERIAL,
    "MAXIM" DOUBLE PRECISION,
    "MINIM" DOUBLE PRECISION,
    "NAME" VARCHAR(255),
    "POS" INTEGER,
    "RAPID_VIEW_ID" BIGINT,
)'
        at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:30)
        at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:53)
        at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:42)
        at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:84)
        at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:62)
        at com.atlassian.dbexporter.importer.TableDefinitionImporter.doImportNode(TableDefinitionImporter.java:50)
        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:158)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:384)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:547)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:221)
        at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:65)
        at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:28)
        at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:147)
        at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at com.atlassian.multitenant.impl.DefaultMultiTenantManager.runForTenant(DefaultMultiTenantManager.java:61)
        at com.atlassian.multitenant.juc.MultiTenantExecutors$WrappedRunnable.run(MultiTenantExecutors.java:160)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 194
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:37)
        ... 22 more


Diagnosis

  • The XML Backup contains Active Objects data.
    • This can be verified by extracting the archive and confirming the presence of a activeobjects.xml data file.


Cause

  • The primaryKey parameter is set to false for Active Objects tables.


Resolution

  1. Unzip your XML backup archive. It should result in 2 files: entities.xml and activeobjects.xml
  2. Edit the activeobjects.xml file
    1. Notice the table that failed to restore does not have a primaryKey defined:

      column name="ID" primaryKey="false"

      (info) In this example the "ID" column should be set as the primary key.

    2. To set a primaryKey for all "ID" columns you can run a find/replace command to set each tables "ID" column to have the primary key constraints enabled:

      column name="ID" primaryKey="true"
    3. Note that some tables may not have the "ID" column as the primaryKey. Below, the column "ISSUE_ID" is the primary key constraint and NOT the column "ID". If the restore fails on tables that do not have a "ID" column present, you may need to set another column as the primary key constraint:

      column name="ISSUE_ID" primaryKey="true"
    4. Check that all the tables only have one primaryKey set to true,, if so, proceed with step 3. Otherwise, the restore will throw a "Entities may only have one primary key" error during the restore.

  3. Zip up the entities.xml and activeobjects.xml files again
  4. Point the restore at the newly modified XML backup


Last modified on Nov 23, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.