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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.