Summary

Creating an XML Backup using Oracle JDBC driver 12.1.0.2 doesn't properly export the Active Objects tables sequence definitions.

Environment

• Confluence 5.9.9
• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Steps to Reproduce

1. Download the Oracle JDBC driver 12.0.1.2 from Oracle's website and place into <confluence_install>/confluence/WEB-INF/lib/.
2. Connect Confluence to Oracle via direct JDBC as per Database Setup for Oracle
3. Generate an XML backup via Confluence Admin > Backup & Restore.

Expected Results

Sequence definitions for Active Object tables are exported.
This can be inspected by extracting the XML Backup and browsing the plugin-data/com.atlassian.activeobjects.confluence.spi/activeObjectsBackupRestoreProvider.pdata file.
Using the table AO_9412A1_AOUSER as an example, we would expect the autoIncrement attribute for the ID column definition (the primary key) set to true, as following:

  <table name="AO_9412A1_AOUSER">
    <column name="CREATED" primaryKey="false" autoIncrement="false" sqlType="93" scale="6"/>
    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="2" precision="20"/>
    <column name="LAST_READ_NOTIFICATION_ID" primaryKey="false" autoIncrement="false" sqlType="2" precision="20"/>
    <column name="TASK_ORDERING" primaryKey="false" autoIncrement="false" sqlType="2005" precision="2147483647"/>
    <column name="UPDATED" primaryKey="false" autoIncrement="false" sqlType="93" scale="6"/>
    <column name="USERNAME" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
  </table>

Actual Results

The primary keys for the tables in the plugin-data/com.atlassian.activeobjects.confluence.spi/activeObjectsBackupRestoreProvider.pdata file have their autoIncrement attribute set to false.
Using the previous example (AO_9412A1_AOUSER.ID)

  <table name="AO_9412A1_AOUSER">
    <column name="CREATED" primaryKey="false" autoIncrement="false" sqlType="93" scale="6"/>
    <column name="ID" primaryKey="true" autoIncrement="false" sqlType="2" precision="20"/>
    <column name="LAST_READ_NOTIFICATION_ID" primaryKey="false" autoIncrement="false" sqlType="2" precision="20"/>
    <column name="TASK_ORDERING" primaryKey="false" autoIncrement="false" sqlType="2005" precision="2147483647"/>
    <column name="UPDATED" primaryKey="false" autoIncrement="false" sqlType="93" scale="6"/>
    <column name="USERNAME" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
  </table>

Business Impact
Importing this XML Backup into a new Confluence instance will corrupt the data and break several Confluence functionalities, including inline tasks and plugins that rely on Active Objects such as Team Calendars. Administrators can expect errors to be printed in Confluence logs which indicate that sequences are missing. The below snippet is an example following an XML import into a Postgres database:

2016-03-31 13:40:14,740 ERROR [http-bio-8090-exec-91] [atlassian.event.internal.AsynchronousAbleEventDispatcher] run There was an exception thrown trying to dispatch event [com.atlassian.mywork.host.event.BeforeCountNewNotificationsEvent@7b8de54f] from the invoker [SingleParameterMethodListenerInvoker{method=public void com.atlassian.mywork.host.service.ClientServiceImpl.verifyAuth(com.atlassian.mywork.host.event.BeforeCountNewNotificationsEvent), listener=com.atlassian.mywork.host.service.ClientServiceImpl@1cbfb81d}]
 -- referer: http:/confluence.example.com/display/SPACE/Page+Name | url: /rest/mywork/latest/status/notification/count | userName: example_user
java.lang.RuntimeException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:PostgreSQL
	- version:9.3.10
	- minor version:3
	- major version:9
Driver:
	- name:PostgreSQL Native Driver
	- version:PostgreSQL 9.2 JDBC4 (build 1002)

org.postgresql.util.PSQLException: ERROR: relation "AO_9412A1_USER_APP_LINK_ID_seq" does not exist
  Position: 16

Notes

This was reproducible on Confluence 5.9.9 but likely affects all prior versions supporting Oracle 12c (Confluence 5.7+)

Workaround 1

Modify the plugin-data/com.atlassian.activeobjects.confluence.spi/activeObjectsBackupRestoreProvider.pdata file such that the primary key of each AO table has autoIncrement set to "true". This will ensure that once imported, the correct sequences will be created for the primary keys.

Workaround 2

When generating an XML backup, temporarily revert to the Oracle JDBC 12.1.0.1 driver instead.