• Community
  • Products
  • Jira
  • Questions
  • JIRA Upgrade : ERROR with ORACLE 9i dump import to 11G due to lack of control on Oracle DB Text max length in JIRA 3.13.5

JIRA Upgrade : ERROR with ORACLE 9i dump import to 11G due to lack of control on Oracle DB Text max length in JIRA 3.13.5

Hi,

I'm upgrading a 3.13.5 JIRA to 5.2.10.

The first step is to import an ORACLE 9i dump into a ORACLE 11G in test environment.

We have problems with some columns size like :

ORA-12899: value too large for column "JIRADB"."CUSTOMFIELDVALUE"."STRINGVALUE" (actual: 260, maximum: 255)

ORA-12899: value too large for column "JIRADB"."JIRAISSUE"."SUMMARY" (actual: 267, maximum: 255)

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "JIRADB"."CUSTOMFIELDVALUE"."STRINGVALUE" (actual: 260, maximum: 255)

Column 1 15169056

Column 2 394725

Column 3 11333

Column 4

Column 5 content-content-content...

Column 6

Column 7

Column 8

Column 9

I found this bug :

https://jira.atlassian.com/browse/JRA-12304

Would it be ok if I apply these queries on the ORACLE 9i instance before extracting the dump? :

ALTER TABLE CUSTOMFIELDVALUE MODIFY (STRINGVALUE VARCHAR2(255 CHAR));

ALTER TABLE JIRAISSUE MODIFY (SUMMARY VARCHAR2(255 CHAR));

I would like to modify the ORACLE 9i instance in order not to have to apply these ALTER TABLE during the production upgrade.

Another solution would be to modify the dump, but it would still require a manual intervention on the production upgrade D-day.

Thanks for your advices to resolve this problem.

Cheers


1 answer

This widget could not be displayed.

Hi Johann,

That Alter Table is probably the easiest way to solve this.

Just make sure you have a backup of the database before doing that in case there are any problems.

Hope this helps,

Marcus

Hi Marcus,

I checked the fieldtype-oracle10g.xml file for JIRA 3.13.5, 4.4.5 and 5.2.10

In 3.13.5 and 4.4.5, ORACLE datatypes of VARCHAR2(X ) are not specified in BYTE or CHAR. As a consequence these datatypes would be considered as BYTE. I'm not a DBA but according to https://jira.atlassian.com/browse/JRA-12304 the default BYTE or CHAR is set by the parameter NLS_LENGTH_SEMANTICS

In 5.2.10, almost all ORACLE datatypes of VARCHAR2(X CHAR) are defined as CHAR.

I intended to do a manual database upgrade but this bug is troublesome.

Could please review the following workaround which I have been thinking of?

1)Pre-upgrade phase 3.13.5 to 4.4.5 :

Apply ALTER TABLE on required columns to set use of CHAR on Oracle 9i instance

Export Oracle 9i instance

2)Upgrade phase 3.13.5 to 4.4.5 :

Do the manual upgrade until 4.4.5, backup the Oracle 11G database

Do an XML dump export

3)Normalize 4.4.5 Database

Modifiy the fieldtype-oracle10g.xml for 4.4.5 war build in order to use CHAR for all ORACLE VARCHAR2 datatypes according to 5.2.10 settings.

Configure 4.4.5 to start on an empty ORACLE 11G database.

Import the previous XML dump

4)Upgrade 4.4.5 to 5.2.10

Any suggestion would be appreciated.

Thanks

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

97 views 1 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you