Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-61007

AO sequences are missing in XML backups created using the Oracle JDBC driver 12.1.0.2

XMLWordPrintable

      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

              aswan@atlassian.com Andrew S (Inactive)
              dconrad Danilo Conrad
              Votes:
              6 Vote for this issue
              Watchers:
              40 Start watching this issue

                Created:
                Updated:
                Resolved: