Summary

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

Environment

• JIRA Core / Software 7.0.x or JIRA Core / Software 7.1.x
• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Steps to Reproduce

1. Connect JIRA to Oracle as per Connecting JIRA applications to Oracle.
2. As recommended in our Supported platforms document, download the Oracle JDBC driver 12.1.0.2 from Oracle's website and replace the existing ojdbc7 driver from the $JIRA_INSTALL/lib folder with the 12.1.0.2.
3. Create a project in JIRA and generate an XML Backup via /secure/admin/XmlBackup!default.jspa.

Expected Results

Sequence definitions for Active Object tables are exported.
This can be inspected by extracting the XML Backup and browsing the activeobjects.xml file.
Using the table AO_B9A0F0_APPLIED_TEMPLATE 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_B9A0F0_APPLIED_TEMPLATE">
    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="2" precision="11"/>
    <column name="PROJECT_ID" primaryKey="false" autoIncrement="false" sqlType="2" precision="20"/>
    <column name="PROJECT_TEMPLATE_MODULE_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="PROJECT_TEMPLATE_WEB_ITEM_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
  </table>

Actual Results

The primary keys for the tables in the activeobjects.xml file have their autoIncrement attribute set to false.
Using the previous example (AO_B9A0F0_APPLIED_TEMPLATE.ID)

  <table name="AO_B9A0F0_APPLIED_TEMPLATE">
    <column name="ID" primaryKey="true" autoIncrement="false" sqlType="2" precision="11"/>
    <column name="PROJECT_ID" primaryKey="false" autoIncrement="false" sqlType="2" precision="20"/>
    <column name="PROJECT_TEMPLATE_MODULE_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="PROJECT_TEMPLATE_WEB_ITEM_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
  </table>

Business Impact
Importing this XML Backup into a new JIRA instance will corrupt the data and break several JIRA functionalities. For instance, creating a new project after importing a corrupted XML Backup will fail with the following exception:

2016-01-20 13:26:02,663 http-nio-8080-exec-3 ERROR admin 806x17642x1 6f8mxx 10.1.3.200,10.12.32.85 /rest/project-templates/1.0/templates [c.a.jira.project.ProjectCreateRegistrarImpl] The handler with id com.atlassian.jira.project-templates-plugin:apply-project-template-handler threw an exception while handling a notification about a project being created
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
        - name:Oracle
        - version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
        - minor version:1
        - major version:12
Driver:
        - name:Oracle JDBC driver
        - version:11.2.0.2.0
java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("JIRA716"."AO_B9A0F0_APPLIED_TEMPLATE"."ID")

Notes

• This was reproducible on JIRA 7.0.10 and 7.1.6.
• The existing implementation invokes DatabaseMetaData#getTables to retrieve the sequences in oracle with the types parameter being set as "SEQUENCE". However, due to a security patch - CVE-2016-3506, the "SEQUENCE" is not treated as legal input anymore.

Workaround

• For regular backups, we recommend using the native database tools, as highlighted in our Backing up Data document.
• If an XML backup is required (e.g. to migrate to another database), use a different Oracle jdbc driver version temporarily (e.g. 12.1.0.1):
  • Which version of JDBC drivers support which version of Oracle database?