Jira server throws a Syntax error when restoring from a XML backup

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

問題

  • 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

診断

  • 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.

原因

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

ソリューション

  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, 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
最終更新日 2019 年 9 月 25 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.