Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-28272

XML backup of ActiveObjects plugin data is not portable between database types

XMLWordPrintable

      Symptoms

      If an XML backup is done from one Confluence instance running on one type of database type, and is then imported into another Confluence instance using a different database type, then chances are the import will fail due to incompatible types.

      This appears to be a problem when importing between a database type where booleans are a valid column type, into a database where boolean is not a column type. At this stage it's confirmed to definitely happen when importing an export from PostgreSQL into Oracle.

      This is currently happening with the data stored for the Space Sidebar. This will show a the following error message in the browser:

      Import failed. Check your server logs for more information. com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Confluence Space IA (Left Sidebar)(com.atlassian.confluence.plugins.confluence-space-ia) #5.0 (table AO_187CCC_SIDEBAR_LINK):There has been a SQL exception importing row #0 for table 'AO_187CCC_SIDEBAR_LINK' see the cause of this exception for more detail about it.

      And the following stack trace in the log file:

      2013-02-26 22:19:53,208 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
 -- referer: http://localhost/setup/setup-restore-start.action | url: /setup/setup-restore-local.action | userName: anonymous | action: setup-restore-local
com.atlassian.confluence.importexport.ImportExportException: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Confluence Space IA (Left Sidebar)(com.atlassian.confluence.plugins.confluence-space-ia) #5.0 (table AO_187CCC_SIDEBAR_LINK):There has been a SQL exception importing row #0 for table 'AO_187CCC_SIDEBAR_LINK' see  the cause of this exception for more detail about it.
	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:62)
	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importPluginData(FileBackupImporter.java:367)
	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:138)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:156)
	at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
	at com.atlassian.confluence.importexport.xmlimport.RestorePluginStateStoreTransactionCallbackDecorator.doInTransaction(RestorePluginStateStoreTransactionCallbackDecorator.java:47)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:149)
	at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:75)
	at com.atlassian.confluence.importexport.DefaultImportExportManager.performImportInternal(DefaultImportExportManager.java:79)
	at com.atlassian.confluence.importexport.DefaultImportExportManager.performImport(DefaultImportExportManager.java:69)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at sun.proxy.$Proxy117.performImport(Unknown Source)
	at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:86)
	at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
	at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:35)
	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 java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Confluence Space IA (Left Sidebar)(com.atlassian.confluence.plugins.confluence-space-ia) #5.0 (table AO_187CCC_SIDEBAR_LINK):There has been a SQL exception importing row #0 for table 'AO_187CCC_SIDEBAR_LINK' see  the cause of this exception for more detail about it.
	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newRowImportSqlException(ImportExportErrorServiceImpl.java:36)
	at com.atlassian.dbexporter.importer.DataImporter.importTable(DataImporter.java:131)
	at com.atlassian.dbexporter.importer.DataImporter.access$000(DataImporter.java:33)
	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:73)
	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:62)
	at com.atlassian.dbexporter.jdbc.JdbcUtils.withConnection(JdbcUtils.java:31)
	at com.atlassian.dbexporter.importer.DataImporter.doImportNode(DataImporter.java:61)
	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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at sun.proxy.$Proxy361.restore(Unknown Source)
	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:58)
	... 30 more
Caused by: java.sql.SQLException: Invalid column type: 16
	at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3916)
	at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4541)
	at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4523)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1281)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setNull(NewProxyPreparedStatement.java:157)
	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setBoolean(DataImporter.java:344)
	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setValue(DataImporter.java:413)
	at com.atlassian.dbexporter.importer.DataImporter.importTable(DataImporter.java:123)
	... 60 more

      Steps to Reproduce

      1. Create a full system XML backup from Confluence running on PostgreSQL
      2. Install a new Confluence instance on Oracle, and import the data exported from the PostgreSQL instance

      Cause

      The data types saved in the activeObjectsBackupRestoreProvide.pdata file are defined exactly by the physical schema of the database. When restoring, Confluence attempts to reconstruct the tables using the column types of the source database.

      In the case of Oracle importing from PostgreSQL, this triggers the Hibernate bug HHH-6759.

      Workaround

      In the case where there are boolean values causing the issue, you can fix by:

      1. Unzip the backup file
      2. Edit plugin-data/com.atlassian.activeobjects.confluence.spi/activeObjectsBackupRestoreProvider.pdata
      3. Replace any occurrence of 
        <boolean xsi:nil="true"/>

        with 

        <integer xsi:nil="true"/>
      4. Re-zip the backup
      5. Import the new fixed backup

      Test notes

      1. After importing backup created on PGSQL to Oracle, make sure that AO data has been restored from previous instance and that you can add, edit, delete AO data - WD notifications are an example of these.

            [CONFSERVER-28272] XML backup of ActiveObjects plugin data is not portable between database types

            Gonchik Tsymzhitov added a comment -

            Magic to met with that in 2020 on 7.4.4 

            Thanks for workaround

            Gonchik Tsymzhitov added a comment - Magic to met with that in 2020 on 7.4.4  Thanks for workaround

            Jonathon added a comment -

            If importing into Oracle, you may see the ORA-00955 error. I have confirmed that the workaround here resolves that error.

            Jonathon added a comment - If importing into Oracle, you may see the ORA-00955 error. I have confirmed that the workaround here resolves that error.

            Raiza Martins Dias added a comment -

            Workaround works with Jira too. Thanks..

            Raiza Martins Dias added a comment - Workaround works with Jira too. Thanks..

            Yossi Zinger added a comment -

            Workaround works great. Thanks for that!

            Yossi Zinger added a comment - Workaround works great. Thanks for that!

            Samuel Le Berrigaud added a comment -

            akazatchkov sounds good then. I was commenting from memory, and I knew some details would escape me. In general adding more informatino to a backup is not a bad thing anyway, that only more information we can base ourselves on to re-import.

            Samuel Le Berrigaud added a comment - akazatchkov sounds good then. I was commenting from memory, and I knew some details would escape me. In general adding more informatino to a backup is not a bad thing anyway, that only more information we can base ourselves on to re-import.

            Samuel Le Berrigaud added a comment -

            The interesting thing is that this cases should be tested as part of AO's integration testing. This is indeed a tricky bit in AO's backup/restore. I haven't personally had a look at the code for a little while though...

            As for using the logical type, I don't think this will be possible... but I might be wrong. One thing to keep in mind with AO's backup/restore is that is backs up data for all plugins which use AO without knowing about them. Those plugins could even be disabled or even uninstalled that the backup would happen. There could be a way by reading the physical to logical type when backing up maybe without knowing about the plugins... That would certainly give a bit more flexibility and strengthen the backup/restore.

            Samuel Le Berrigaud added a comment - The interesting thing is that this cases should be tested as part of AO's integration testing. This is indeed a tricky bit in AO's backup/restore. I haven't personally had a look at the code for a little while though... As for using the logical type, I don't think this will be possible... but I might be wrong. One thing to keep in mind with AO's backup/restore is that is backs up data for all plugins which use AO without knowing about them. Those plugins could even be disabled or even uninstalled that the backup would happen. There could be a way by reading the physical to logical type when backing up maybe without knowing about the plugins... That would certainly give a bit more flexibility and strengthen the backup/restore.

              dma@atlassian.com David Ma
              dmason David Mason (Inactive)
              Affected customers:
              1 This affects my team
              Watchers:
              11 Start watching this issue

                Created:
                Updated:
                Resolved: