Backup restore fails with a syntax error in Jira
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
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
Last modified on Nov 23, 2022
Powered by Confluence and Scroll Viewport.