• 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

0 vote

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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,333 views 14 20
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot