Large text fields in Oracle

JIRA 3 stores workflows in text format inside the database. In Oracle, VARCHAR2 text fields have a maximum size of 4000 characters. Most medium to large workflows therefore cannot be stored in Oracle. The same limitation prevents other text fields (issue summaries, descriptions and comments) from being over 4000 characters.

Fortunately Oracle have worked around the VARCHAR2 limitation in their latest Oracle 10g JDBC driver. This fix (described online here) works with Oracle 9 and 10, with some doubt regarding Oracle 8.

該当有無の確認

For new installations of JIRA 3.2 and above, please follow the normal Oracle configuration notes. This page does not apply to you.

When upgrading from 3.1 or earlier to 3.2 and above, you will likely need to change your config files. Please note the example of upgrading JIRA with Oracle in the Upgrade docs. This page may be of use as a reference.

For installing earlier versions (3.1.1 and earlier), here is how to apply the fix:

Download the Oracle 10g driver

From Oracle's site download the ojdbc14.jar (or applicable) JDBC driver, and copy it to your app server's lib/ directory (eg. common/lib/ for Tomcat). Remove the old JDBC jar used previously.

Rebuild JIRA to use CLOBs

If running JIRA Standalone, edit atlassian-jira/WEB-INF/classes/entitydefs/fieldtype-oracle.xml. If running a JIRA webapp, copy webapp/WEB-INF/classes/entitydefs/fieldtype-oracle.xml to edit-webapp/WEB-INF/classes/entitydefs/fieldtype-oracle.xml and edit it there. Locate the lines:

<field-type-def type="very-long" sql-type="VARCHAR2(4000)" java-type="String"></field-type-def>
<field-type-def type="extremely-long" sql-type="VARCHAR2(4000)" java-type="String"></field-type-def>

and change them to:

<field-type-def type="very-long" sql-type="CLOB" java-type="String"></field-type-def>
<field-type-def type="extremely-long" sql-type="CLOB" java-type="String"></field-type-def>

(in JIRA 3.0.x and earlier the names may be different - make the change for all VARCHAR2(4000) entries)

If not running Standalone, rebuild the webapp by running build.sh / build.bat in the root directory. Do not yet redeploy the webapp.

Set the SetBigStringTryClob flag

In your application server, where the JDBC DataSource is configured, a parameter needs to be added to tell the Oracle JDBC driver to handle CLOBs in a standard way.

Tomcat 4 and 5.0:

Edit conf/server.xml (Tomcat 4) or conf/Catalina/localhost/jira.xml (Tomcat 5.0), locate the section where the 'jdbc/JiraDS' DataSource is set up, and add:

<parameter>
  <name>connectionProperties</name>
  <value>SetBigStringTryClob=true</value>
</parameter>

For instance, in JIRA Standalone one would then have:

    <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/JiraDS">
      <parameter>
        <name>driverClassName</name>
        <value>oracle.jdbc.driver.OracleDriver</value>
      </parameter>
      <parameter>
        <name>url</name>
        <value>jdbc:oracle:thin:@<database host machine>:<port>:<SID></value>
      </parameter>
      <parameter>
        <name>username</name>
        <value>...</value>
      </parameter>
      <parameter>
        <name>password</name>
        <value>...</value>
      </parameter>
      
<parameter>
  <name>connectionProperties</name>
  <value>SetBigStringTryClob=true</value>
</parameter>

      <parameter>
        <name>factory</name>
        <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
    </ResourceParams>

Tomcat 5.5

In Tomcat 5.5, the format for the added section would be:

<connectionProperties>SetBigStringTryClob=true</connectionProperties>

Orion / OC4J

For Orion/OC4J, edit config/data-sources.xml, and add the property as a nested tag:

<data-source
    class="<datasource driver class>"
    name="<name>"
    location="<location>"
    xa-location="<xa-location>"
    ejb-location="<ejb-location>"
    url="<url>"
    connection-driver="<driver>"
    username="<login>"
    password="<password>"
    inactivity-timeout="30"
>
  <property name="SetBigStringTryClob" value="true" />
</data-source>

Other app servers

Consult the relevant JIRA app server guide and the app server documentation to find how to add the property.

Use CLOBs in the database

You now have JIRA configured to use CLOBs, and the JDBC driver configured to handle them correctly. However the database in use still uses VARCHARs.

これを修正するには:

• generate an XML backup of the data (with the app server configured without the SetBigStringTryClob flag). Backing up the Oracle database using native tools is also a good idea.
• drop and recreate the Oracle database (or create a new parallel database to import into).
• restart JIRA (recreating the database with CLOBs), deploying the new (rebuilt) webapp.
• import the XML data.

Alternatively (and assuming Oracle supports it), an SQL ALTER TABLE command can be issued to change the various column types (which columns can be seen from warning messages in the logs when JIRA is started after making the above changes).

Done!

JIRA should now restart without warnings, and you should be able to add 4000+ character comments and descriptions.

If you have any feedback on this process, please let us know