[#JRA-4361] Cannot store workflows of over 4000 characters in Oracle

JIRA

Cannot store workflows of over 4000 characters in Oracle

Created: 30/Aug/04 10:49 PM Updated: 26/May/08 05:57 AM

Component/s:

Backend / Domain Model, Workflow

Affects Version/s:

3.0 Pro Preview

Fix Version/s:

None

Time Tracking:

Not Specified

Issue Links:

Duplicate

This issue is duplicated by:

~~JRA-5297~~ Workflow size limited to 4000 characters in Oracle

~~JRA-5132~~ copying workflow

Reference

This issue relates to:

JRA-2152 Error reporting of Oracle Text Field Size limitations

Participants:	Jeff Turner [Atlassian], Joe Blotner, Justin Koke [Atlassian], Noah Campbell, Pierre Fortin and Stew S
Since last comment:	118 weeks, 3 days ago
Support reference count:	8
Labels:

Description

« Hide

Oracle has the infamous problem of not being able to store more than 4000 characters in a VARCHAR2 text field. JIRA 3 stores edited workflows in a VARCHAR2 field, meaning one cannot copy and edit most workflows when using JIRA with Oracle.

All

Comments

Work Log

Change History

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Jeff Turner [Atlassian] - 30/Aug/04 10:51 PM

There is a workaround of sorts for this. If a workflow can be constructed on another JIRA instance (eg. JIRA Standalone), then it can be saved to disk, and made known to JIRA by editing WEB-INF/classes/workflows.xml. This is documented at:

http://confluence.atlassian.com/display/JIRA/JIRA+3+workflows+in+Oracle

[ Permlink | « Hide ]

Pierre Fortin - 03/Sep/04 12:19 PM

Would it be possible to store the files as lob in the database, instead of varchar2?

[ Permlink | « Hide ]

Jeff Turner [Atlassian] - 05/Sep/04 10:49 PM

Pierre,

It's possible, but I think the problem with LOBs was that the code becomes unportable across different databases. We'll have to investigate.

[ Permlink | « Hide ]

Noah Campbell - 09/Nov/04 04:16 PM

You solved the problem with comment fields being too big. Isn't that code resuable here?

[ Permlink | « Hide ]

Jeff Turner [Atlassian] - 10/Nov/04 06:05 PM

As far as I know, the comment field is still limited to 4000 characters in Oracle?

[ Permlink | « Hide ]

Joe Blotner - 11/Jan/05 11:13 AM

Something else that may be (somewhat helpful)... Another bug (feature?) in JIRA allows one to build workflows in an Oracle database and export the XML, even if the WF is larger than 4000 characters.

When you exceed the 4000 char limit, you may notice that returning to your Steps page for your workflow, the item you just added is displayed – as if the data was stored somewhere. So, basically, the cache is out of sync w/ the database. Usually, this is a bad thing, but it can help in this case. Keep editing your workflow and ignoring the error messages, periodically exporting the workflow to XML as you go. As long as the server doesn't get restarted, you can keep working and building your workflow.

Of course, all future modifications must occur directly in the XML, but you can at least get started.

[ Permlink | « Hide ]

Jeff Turner [Atlassian] - 12/Jan/05 01:28 AM

I think we've (at least some helpful users) have found a solution to this - the Oracle 10g driver has an option to allow varchars to be stored as CLOBs (without Oracle-specific code). See:

http://jira.atlassian.com/browse/JRA-5297?page=comments#action_28039

[ Permlink | « Hide ]

Stew S - 07/Apr/06 10:12 AM

I was reading the Confluence page on this issue: http://confluence.atlassian.com/display/JIRA/Large+text+fields+in+Oracle

It said that the file atlassian-jira/WEB-INF/classes/entitydefs/fieldtype-oracle.xml

had been updated since V3.2 so that the problematice lines read:

<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>

But my copy of this file still show them described as VARCHAR2(4000).

And my copy of fieldtype-oracle10g.xml shows that the datatype "very-long" is still VARCHAR2(4000).

I hope this helps!

[ Permlink | « Hide ]

Justin Koke [Atlassian] - 11/Apr/06 09:34 PM

Hi Stew,

If you are still having a problem getting JIRA (version??) to work with Oracle (version??). Please post an issue at https://support.atlassian.com and we will help you through any possible issues.

Regards,
Justin