Backup restore fails with a syntax error in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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.
Solution
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:
1column 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:
1column 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:
1column 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
Was this helpful?