JIRA 4.0 : Large text fields in Oracle
This page last changed on Jul 21, 2005 by jeff.
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. Does this apply to you?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 driverFrom 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 CLOBsIf 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 flagIn 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.5In Tomcat 5.5, the format for the added section would be: <connectionProperties>SetBigStringTryClob=true</connectionProperties> Orion / OC4JFor 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 serversConsult the relevant JIRA app server guide and the app server documentation to find how to add the property. Use CLOBs in the databaseYou now have JIRA configured to use CLOBs, and the JDBC driver configured to handle them correctly. However the database in use still uses VARCHARs. To fix this:
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 |
![]() |
Document generated by Confluence on Oct 06, 2009 00:26 |