Using Oracle 10g drivers to solve the 4000 character limitation

As you might be aware Oracle has a 4000 character limitation on VARCHAR2 fields. Which causes quite a few headaches when dealing with custom workflows or working with issues that have long descriptions, comments or custom field values.

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. We strongly recommend using Oracle 10g drivers and the setup described below if you are using Oracle 9i or 10g.

Please follow the Upgrading JIRA Safely instructions, keeping the following in mind:

Use Oracle 10g driver

This configuration will work only with Oracle 10g drivers. Therefore, 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.

Create a new database for JIRA 3.2

Please create a new database for JIRA 3.2 and configure JIRA's data source to use it. Please do not point JIRA 3.2 at your old database.

Set the SetBigStringTryClob flag

When configuring the data source for the new database set the SetBigStringTryClob flag to true. The way this must be done depends on your application server

JIRA Standalone, 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:

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
    username="jirauser"
    password="jirauser"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@localhost:1521:jiradb"

connectionProperties="SetBigStringTryClob=true">

</Resource>

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 oracle10g field-type-name

Please specify oracle10g (not oracle) as the field-type-name when editing WEB-INF/classes/entityengine.xml

After the data is re-imported and JIRA upgrades the data the 4000 character limitation should disappear.