[#JRA-14085] SearchRequest entity should use a extemely-long field types on Oracle 10g

JIRA

SearchRequest entity should use a extemely-long field types on Oracle 10g

Created: 03/Dec/07 10:06 PM Updated: 21/Apr/08 06:21 PM

Component/s:

Backend / Domain Model

Affects Version/s:

3.11

Fix Version/s:

None

Time Tracking:

Not Specified

Issue Links:

Duplicate

This issue duplicates:

JRA-8293 Import fails if searchrequest:request data too large

Participants:	Brad Baker [Atlassian] and Ian Daniel [Atlassian]
Since last comment:	12 weeks, 5 days ago
Resolution Date:	04/Dec/07 09:39 PM
Labels:

Description

« Hide

Currently the SearchRequest entity is defined like this :

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
       ...
        <field name="request" col-name="reqcontent" type="very-long"/>

    </entity>

On Oracle 10g this is defined as varchar(4000). This means that search requests cant be bigger than that.

It could be this :

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
       ...
        <field name="request" col-name="reqcontent" type="extremely-long"/>

    </entity>

We had a customer who migrated from MYSQL (very-long ~ 32K) to ORACLE 10G and had problems because they had search requests > 4K

We should up the limit for Oracle 10g in the next release of JIRA.

We may want to consider other enities that use 'very-long' when we do this.

All

Comments

Work Log

Change History

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Ian Daniel [Atlassian] - 20/Apr/08 08:21 PM - edited

User symptom: Importing JIRA XML data into Oracle fails with an error like the following:

Error importing data: org.apache.commons.lang.exception.NestableRuntimeException:
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: 
while inserting: [GenericEntity:SearchRequest][id,11362][project,10020][author,rfc-admin]
[request,<?xml version='1.0' encoding='UTF-8'?> <searchrequest name='RFC New Lite'> ... 
(ORA-01461: can bind a LONG value only for insert into a LONG column ))

The data was exported from a JIRA instance using a database other than Oracle.

[ Permlink | « Hide ]

Ian Daniel [Atlassian] - 20/Apr/08 08:29 PM

Workaround

So the definition of the "SearchRequest" entity (table) is as follows, taken from WEB-INF/classes/entitydefs/entitymodel.xml:

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
        <field name="id" type="numeric"/>

        <field name="name" col-name="filtername" type="long-varchar"/>
        <field name="author" col-name="authorname" type="long-varchar"/>
        <field name="description" type="very-long"/>
        <field name="user" col-name="username" type="long-varchar"/>
        <field name="group" col-name="groupname" type="long-varchar"/>
        <field name="project" col-name="projectid" type="numeric"/>
        <field name="request" col-name="reqcontent" type="very-long"/>

        <prim-key field="id"/>

        <relation type="one" title="Parent" rel-entity-name="Project">
            <key-map field-name="project" rel-field-name="id"/>
        </relation>

        <index name="sr_author">
            <index-field name="author"/>
        </index>
        <index name="sr_group">
            <index-field name="group"/>
        </index>
    </entity>

So if something such as a filter definition that was greater than 4K was made in your old database, when it is exported and re-imported to Oracle is is bigger than the maximum allowed column size and hence the import fails.

The workaround for this problem is to edit <jiradir>/WEB-INF/classes/entitydefs/entitymodel.xml and change the definition of the SearchRequest entity (around line 433) as follows to use "extremely-long":

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
        <field name="id" type="numeric"/>

        <field name="name" col-name="filtername" type="long-varchar"/>
        <field name="author" col-name="authorname" type="long-varchar"/>
        <field name="description" type="very-long"/>
        <field name="user" col-name="username" type="long-varchar"/>
        <field name="group" col-name="groupname" type="long-varchar"/>
        <field name="project" col-name="projectid" type="numeric"/>
        <field name="request" col-name="reqcontent" type="extremely-long"/>

        <prim-key field="id"/>

        <relation type="one" title="Parent" rel-entity-name="Project">
            <key-map field-name="project" rel-field-name="id"/>
        </relation>

        <index name="sr_author">
            <index-field name="author"/>
        </index>
        <index name="sr_group">
            <index-field name="group"/>
        </index>
    </entity>

On Oracle 10g, "extremely-long" maps to the Oracle type CLOB which allows for very big column sizes.

The down-side of this "workaround" is that it will have to be re-applied when the JIRA code base is updated, until the bug (this issue) is fixed.

If this doesn't fix the problem, please create an issue in our support system, https://support.atlassian.com, and attach the XML data file that you cannot import so that we can try to replicate the problem here and investigate further. You can anonymize the data if necessary.

[ Show » ]

Ian Daniel [Atlassian] - 20/Apr/08 08:29 PM

Workaround

JIRA uses the Open 4 Business (ofbiz) DB framework to provide cross platform database support. It defines its entities (tables) in a "virtual" database type and then these are "translated" into actual database types for each DB platform. So the definition of the "SearchRequest" entity (table) is as follows, taken from WEB-INF/classes/entitydefs/entitymodel.xml:

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
        <field name="id" type="numeric"/>

        <field name="name" col-name="filtername" type="long-varchar"/>
        <field name="author" col-name="authorname" type="long-varchar"/>
        <field name="description" type="very-long"/>
        <field name="user" col-name="username" type="long-varchar"/>
        <field name="group" col-name="groupname" type="long-varchar"/>
        <field name="project" col-name="projectid" type="numeric"/>
        <field name="request" col-name="reqcontent" type="very-long"/>

        <prim-key field="id"/>

        <relation type="one" title="Parent" rel-entity-name="Project">
            <key-map field-name="project" rel-field-name="id"/>
        </relation>

        <index name="sr_author">
            <index-field name="author"/>
        </index>
        <index name="sr_group">
            <index-field name="group"/>
        </index>
    </entity>

The "request" field is defined as "very-long" and on Oracle 10G this translates to "varchar2(4000)", hence the 4K limit. Other databases map it to a longer type. For example, in MYSQL "very-long" maps to the MYSQL type of "text" which is 32K in size. So if something such as a filter definition that was greater than 4K was made in your old database, when it is exported and re-imported to Oracle is is bigger than the maximum allowed column size and hence the import fails. The workaround for this problem is to edit <jiradir>/WEB-INF/classes/entitydefs/entitymodel.xml and change the definition of the SearchRequest entity (around line 433) as follows to use "extremely-long":

 <entity entity-name="SearchRequest" table-name="searchrequest" package-name="">
        <field name="id" type="numeric"/>

        <field name="name" col-name="filtername" type="long-varchar"/>
        <field name="author" col-name="authorname" type="long-varchar"/>
        <field name="description" type="very-long"/>
        <field name="user" col-name="username" type="long-varchar"/>
        <field name="group" col-name="groupname" type="long-varchar"/>
        <field name="project" col-name="projectid" type="numeric"/>
        <field name="request" col-name="reqcontent" type="extremely-long"/>

        <prim-key field="id"/>

        <relation type="one" title="Parent" rel-entity-name="Project">
            <key-map field-name="project" rel-field-name="id"/>
        </relation>

        <index name="sr_author">
            <index-field name="author"/>
        </index>
        <index name="sr_group">
            <index-field name="group"/>
        </index>
    </entity>

On Oracle 10g, "extremely-long" maps to the Oracle type CLOB which allows for very big column sizes. The down-side of this "workaround" is that it will have to be re-applied when the JIRA code base is updated, until the bug (this issue) is fixed. If this doesn't fix the problem, please create an issue in our support system, https://support.atlassian.com, and attach the XML data file that you cannot import so that we can try to replicate the problem here and investigate further. You can anonymize the data if necessary.

[ Permlink | « Hide ]

Brad Baker [Atlassian] - 21/Apr/08 06:21 PM

One caveat on the workaround is that it will only work IF the database it empty and JIRA re-creates the database table. If you have already started JIRA and hence the database table is created, it wont be be updated to the new column definition.