-
Bug
-
Resolution: Unresolved
-
Low (View bug fix roadmap)
-
None
-
6.4.14, 7.3.0, 7.0.11, 7.12.1, 8.0.0
-
6.04
-
29
-
Severity 2 - Major
-
3
-
Summary
Site restore fails due to active objects import for some add-ons on oracle.
The import fails due to the restore attempting to create duplicate sequences in the database. This is caused by the naming of tables and columns in active objects entities used by the add-on.
Environment
- Database: Oracle 11g 11.2.0.1.0 / 11.2.0.4.0
- Database Driver Version: 11.2.0.2.0 / 11.2.0.4.0 / 12.1.0.1.0
- Add-on: Smart Attachments - others likely affected.
Steps to Reproduce
- Install JIRA with Oracle database configured in setup.
- Install add-on (Smart Attachments)
- Create XML backup
- Restore XML backup
Expected Results
All data is restored without issue as it would be on other supported databases (Postgres, MySQL, etc).
Actual Results
Data restore fails when restoring active objects tables.
The below exception is thrown in the xxxxxxx.log file:
2017-03-22 13:07:09,192 JiraImportTaskExecutionThread-1 ERROR dmcmorris 786x603x1 4xuhp1 192.168.56.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 plugin Smart Attachments(com.stiltsoft.jira.smart-attachments) #1.15.1 (table AO_D4B4E5_FV_COMMENT_THREAD):Error executing update for SQL statement 'CREATE SEQUENCE "AO_D4B4E5_FV_COMMEN1480854115" INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1' 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.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:73) at com.atlassian.activeobjects.backup.ActiveObjectsTableCreator.create(ActiveObjectsTableCreator.java:57) at com.atlassian.dbexporter.importer.TableDefinitionImporter.doImportNode(TableDefinitionImporter.java:50) at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:44) 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:583) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:757) 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: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
Notes
This appears to be caused by how active objects creates sequence names in Oracle as mention in this issue:
A restore of a backup fails in Oracle 11.2g due to a bug in the way sequence names are generated which causes duplicate sequences to be generated. Attached is a patch file that contains a unit test that demonstrate the problem.
This happens when:
- there are two entities name A and A_B
- entity A has a field named B_ID,
- entity A_B has a field named ID
The above makes a sequence for both tables named A_B_ID_SEQ_ID (which may or may not be shortened - the log snippet below is using the shortened name).
The sequence generation net.java.ao.schema.DefaultSequenceNameConverter simply concatenates the table name and field name, which means both of the above entities generates the same sequence, and thus causing the restore to fail.
Workaround
Have the add-on developer change column names and add an upgrade task so that duplicated sequences are not generated at restoration.
As a workaround to the failing XML restoration, restore using Oracle dump instead.
![[Ecosystem JIRA] Bug - A problem which impairs or prevents the functions of the product.](https://ecosystem.atlassian.net/rest/api/2/universal_avatar/view/type/issuetype/avatar/15303?size=medium "[Ecosystem JIRA] Bug - A problem which impairs or prevents the functions of the product."){kind=small}
[JRASERVER-64915] Site restore fails on Oracle with due to duplicate sequences being created by active objects
Hi there,
Would be great if this could be resolved. It seems to require a very simple fix actually, but the impact is monstrous. We (TM4J) had a number of customers of our add-on not being able to backup/restore Jira instances over the last years because of this exact problem.
Please, provide any feedback so we can understand what are the next steps.
Thank you.
We've noticed that in the created backup file for our field "THREAD_ID" (table AO_D4B4E5_FV_COMMENT) there is a parameter autoIncrement="true", but it should be - autoIncrement="false". If we manually change the value to "false" then it will restore from backup without any errors. On other database types this parameter is always set autoIncrement="false".
Hey,
We've got a few customers struggling with this one and some complaints raised. From our side, this would be very difficult and risky to fix. Can you please let us know if this can be fixed?
Even so we can get back to the customers and set some expectations.
Much appreciated,
Dee