-
Bug
-
Resolution: Fixed
-
Highest
-
7.6.3, 7.10.0, 7.11.0, 7.6.7
-
7.06
-
19
-
Severity 2 - Major
-
26
-
-
Summary
Importing from Cloud to Server fails with mismatched datatype error
Steps to Reproduce
- Export data from a cloud instance
- Set up or access a test Server instance (7.6.7, 7.6.3, 7.10, 7.11 all reproduce the problem)
- Attempt to import the data
Expected Results
The import works without problems
Actual Results
The import stops at 90% and then fails producing an error. Tables appear to be partially generated during the process, so a new import requires deleting and recreating the application DB beforehand.
The error below can be observed in both catalina.out and atlassian-jira.log
2018-08-03 12:54:25,073 JiraImportTaskExecutionThread-1 ERROR admin 2848x568x1 yp50xh 127.0.0.1 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with <unknown plugin>:Error executing update for SQL statement 'ALTER TABLE dbo.AO_54307E_REQUESTTYPE_TO_ISSUE ADD CONSTRAINT fk_ao_54307e_requesttype_to_issue_issue_id FOREIGN KEY (ISSUE_ID) REFERENCES dbo.JIRAISSUE(ID)' at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26) at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:46) at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:37) at com.atlassian.activeobjects.backup.ActiveObjectsForeignKeyCreator.create(ActiveObjectsForeignKeyCreator.java:43) at com.atlassian.activeobjects.backup.ForeignKeyAroundImporter.after(ForeignKeyAroundImporter.java:26) at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:47) at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:69) at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:151) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:584) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:758) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:331) at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:60) at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:28) at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:157) at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:133) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column 'dbo.JIRAISSUE.ID' is not the same data type as referencing column 'AO_54307E_REQUESTTYPE_TO_ISSUE.ISSUE_ID' in foreign key 'fk_ao_54307e_requesttype_to_issue_issue_id'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:680) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:34) ... 18 more
Workaround
Remove the following line from activeobject.xml file:
<foreignKey fromTable="AO_54307E_REQUESTTYPE_TO_ISSUE" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID"/>
The following steps can be used to perform and validate this change:
- Unzip cloud backup
unzip cloudbackup.zip Archive: cloudbackup.zip inflating: entities.xml inflating: activeobjects.xml
- Expand xml to multiple lines:
xmllint activeobjects.xml --format > activeobjects_fixed.xml
- Remove the foreign key entry from activeobjects (note I did my testing on OSX so you may have to alter the sed command and remove the '' after -i for Linux):
sed -i '' '/foreignKey fromTable\=\"AO_54307E_REQUESTTYPE_TO_ISSUE/d' activeobjects_fixed.xml - Let's check our work so far. To start we'll make another copy of activeobjects in the multiline format:
xmllint activeobjects.xml --format > testing.xml
- Now we will Diff the fixed file to the original to ensure only 1 line was removed (output should look like below):
diff testing.xml activeobjects_fixed.xml 604d603 < <foreignKey fromTable="AO_54307E_REQUESTTYPE_TO_ISSUE" fromColumn="ISSUE_ID" toTable="JIRAISSUE" toColumn="ID"/>
- Delete old activeobjects file and rename the fixed one to activeobjects.xml
mv activeobjects_fixed.xml activeobjects.xml
- Zip backup back together
zip -r fixed.zip activeobjects.xml entities.xml
- Move the backup to the import directory on the destination instance and perform the import