JIRA ActiveObjects import error

[Ondrej Berger]

Hi everybody,

we have a production JIRA 4.4.4 (WAR) running on W2003, using MSSQL database. Every month we try to restore production instance on Linux testing enviroment (same plugins, configuration etc), but running on PostgreSQL database. Everything else is the same in both enviroments.

We are using GreenHopper 5.8.4 in both enviroments and data of this plugin causes xml restore errors. In log there is:

2012-04-03 21:12:50,517 JiraImportTaskExecutionThread-1 INFO anonymous 794x32x1 9kjds0 0:0:0:0:0:0:0:1 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] Importing data is 90% complete...
2012-04-03 21:17:17,132 JiraImportTaskExecutionThread-1 INFO anonymous 794x32x1 9kjds0 0:0:0:0:0:0:0:1 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] Finished storing Generic Values.
2012-04-03 21:17:18,209 JiraImportTaskExecutionThread-1 ERROR anonymous 794x32x1 9kjds0 0:0:0:0:0:0:0:1 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] Error importing data: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin GreenHopper(com.pyxis.greenhopper.jira) #5.8.4:The following sql caused an error:
CREATE TABLE public."AO_60DB71_COLUMN" (
    "ID" BIGSERIAL,
    "MAXIM" DECIMAL(32,16),
    "MINIM" DECIMAL(32,16),
    "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.8.4:The following sql caused an error:
CREATE TABLE public."AO_60DB71_COLUMN" (
    "ID" BIGSERIAL,
    "MAXIM" DECIMAL(32,16),
    "MINIM" DECIMAL(32,16),
    "NAME" VARCHAR(255),
    "POS" INTEGER,
    "RAPID_VIEW_ID" BIGINT,
)
---

	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:32)
	at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:85)
	at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:57)
	at com.atlassian.dbexporter.importer.TableDefinitionImporter.doImportNode(TableDefinitionImporter.java:48)
	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:155)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:351)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:492)
	at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:214)
	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(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	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(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 190
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:196)
	at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:81)
	... 21 more
2012-04-03 21:17:18,211 JiraImportTaskExecutionThread-1 INFO anonymous 794x32x1 9kjds0 0:0:0:0:0:0:0:1 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] Importing data is 100% complete...
2012-04-03 21:17:18,211 JiraImportTaskExecutionThread-1 INFO anonymous 794x32x1 9kjds0 0:0:0:0:0:0:0:1 /secure/SetupImport.jspa [jira.bc.dataimport.DefaultDataImportService] JIRA Data Import has finished.

JIRA data were restored correctly except of GH. I tried to replace ActiveObjects library in testing enviroment (as described in https://developer.atlassian.com/display/AO/Installing+Active+Objects) but there were ClassDefNotFound from AO plugin on dependecies from AO SPI JAR and AO plugin was unable to start. I installed SPI JAR to WEB-INF/lib and removed old one, ane AO plugin to plugins in JIRA_HOME. My Tomcat points to WAR file directly, so I replaced SPI JAR in WAR file. But from log it looks like a AO SPI file is on some other location and old version of SPI is loaded which causes mentioned exception. So I was unable to replace AO version in order to try if it helps.

I'd like to ask if there is some way how to upgrade AO in JIRA 4.4.4 WAR edition in order to try if GH data restores correctly or some other way how to get GH restored.

Thank you

Ondrej Berger

[Ondrej Berger · Accepted solution]

Problem happened here was that XML export of activeobjects form JIRA didn't contain definitions of primary key columns so creating tables failed. Details can be found on https://support.atlassian.com/browse/JSP-122825

[Samuel Le Berrigaud]

You should open a support ticket at http://support.atlassian.com