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

Type: Bug
Resolution: Fixed
Priority: Low
Fix Version/s: 5.1.3
Affects Version/s: 5.0
Component/s: None
Labels:

Bug Fix Policy:
View Atlassian Server bug fix policy

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

Create a full system XML backup from Confluence running on PostgreSQL
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:

Unzip the backup file
Edit plugin-data/com.atlassian.activeobjects.confluence.spi/activeObjectsBackupRestoreProvider.pdata

Replace any occurrence of

<boolean xsi:nil="true"/>

with

<integer xsi:nil="true"/>

Re-zip the backup
Import the new fixed backup

Test notes

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.

is caused by

CONFSERVER-28741 Import/Export of ActiveObjects plugin data should store data as logical types instead of physical types

Closed

is blocked by

AO-419 Import of database with type boolean into a database which doesn't support boolean throws SQL exception

Done

mentioned in: Page Failed to load; Page Failed to load; Wiki Page Failed to load; Page Loading...; Wiki Page Loading...

(2 mentioned in)

Gonchik Tsymzhitov added a comment - 11/Oct/2020 12:56 PM

Magic to met with that in 2020 on 7.4.4

Thanks for workaround

Gonchik Tsymzhitov added a comment - 11/Oct/2020 12:56 PM Magic to met with that in 2020 on 7.4.4 Thanks for workaround

Katherine Yabut made changes - 13/Nov/2018 4:43 AM

Workflow

Original: JAC Bug Workflow v3 [ 2879678 ]

New: CONFSERVER Bug Workflow v4 [ 2988317 ]

Owen made changes - 11/Oct/2018 8:42 AM

Workflow	Original: JAC Bug Workflow v2 [ 2785273 ]	New: JAC Bug Workflow v3 [ 2879678 ]
Status	Original: Resolved [ 5 ]	New: Closed [ 6 ]

Owen made changes - 29/Aug/2018 11:14 PM

Workflow

Original: JAC Bug Workflow [ 2714552 ]

New: JAC Bug Workflow v2 [ 2785273 ]

Owen made changes - 02/Jul/2018 6:50 AM

Workflow

Original: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2380378 ]

New: JAC Bug Workflow [ 2714552 ]

Alex Yakovlev (Inactive) made changes - 16/Oct/2017 3:48 AM

Labels

Original: active-objects admin-console affects-server bugfix bugfix_support_backlog database/hibernate hedge_bugfix kaap294 loyalty oracle space-sidebar space_ia team-hedgehogs warranty

New: active-objects admin-console affects-server bugfix_support_backlog database/hibernate hedge_bugfix kaap294 loyalty oracle space-sidebar space_ia team-hedgehogs warranty

Alex Yakovlev (Inactive) made changes - 16/Oct/2017 3:42 AM

Labels

Original: active-objects admin-console affects-server bugfix bugfix_support_backlog database/hibernate hedge_bugfix kaap294 oracle space-sidebar space_ia team-hedgehogs warranty

New: active-objects admin-console affects-server bugfix bugfix_support_backlog database/hibernate hedge_bugfix kaap294 loyalty oracle space-sidebar space_ia team-hedgehogs warranty

Katherine Yabut made changes - 22/Jun/2017 6:47 AM

Workflow

Original: Confluence Workflow - Public Facing - Restricted v5 [ 2271630 ]

New: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2380378 ]

Katherine Yabut made changes - 31/May/2017 5:32 AM

Workflow

Original: Confluence Workflow - Public Facing - Restricted v5.1 - TEMP [ 2216421 ]

New: Confluence Workflow - Public Facing - Restricted v5 [ 2271630 ]

Katherine Yabut made changes - 31/May/2017 4:55 AM

Workflow

Original: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2168259 ]

New: Confluence Workflow - Public Facing - Restricted v5.1 - TEMP [ 2216421 ]

Assignee:: David Ma

Reporter:: David Mason (Inactive)

Affected customers:: 1 This affects my team

Watchers:: 11 Start watching this issue

Created:: 27/Feb/2013 2:49 AM

Updated:: 11/Oct/2020 12:56 PM

Resolved:: 29/Apr/2013 11:13 AM

Confluence Data Center

Details

Description

Symptoms

Steps to Reproduce

Cause

Workaround

Test notes

Attachments

Issue Links

Forms

Activity

Collapse comment: Gonchik Tsymzhitov added a comment - 11/Oct/2020 12:56 PM

Expand comment: Gonchik Tsymzhitov added a comment - 11/Oct/2020 12:56 PM

People

Dates