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

Question

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

Johann NG · Answer

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

Marcus Silveira · Answer

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