Backup restore fails with a syntax error in Jira
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
- Restoring a backup that contains Active Objects data fails - In the example below it JIRA Agile data.
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
Environment
Jira Data Center on any version
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
- Unzip your XML backup archive. It should result in 2 files: entities.xml and activeobjects.xml
- Edit the activeobjects.xml file
Notice the table that failed to restore does not have a primaryKey defined:
column name="ID" primaryKey="false" In this example the "ID" column should be set as the primary key.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"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"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.
- Zip up the entities.xml and activeobjects.xml files again
- Point the restore at the newly modified XML backup