Database Migration from HSQLDB to Oracle Fails with "value too large" Error on CRU_INLINE_COMMENT_TO_FRX_REV table
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Database migration from embedded HSQLDB to Oracle fails with following error reported in fisheye-debug-<date>.log:
Exception
1
Database error at cru_inline_comment_to_frx_rev:4999 (table:row) of the input: ORA-12899: value too large for column "FISHEYE"."CRU_INLINE_COMMENT_TO_FRX_REV"."CRU_LINE_RANGE" (actual: 129, maximum: 100)Cause
Some of the columns in HSQLDB database have values larger than the set limit because this content was created in a relatively older version of Fisheye (2.3.3 in the above example).
As per Fisheye 2.4.x, limits have been enforced via the UI which should prevent the creation of such records:
CRUC-3863 - Validate field lengths in the UI where they have a limit.
HSQLDB is not supported/recommended for production instances due to a number of reasons. The problem reported here is related to the fact that HSQLDB does not enforce column length, which could have been avoided by migrating to an external database when in production.
Resolution
There is a bug report for the CRU_LINE_RANGE column width to be increased to 255, as 100 seems to be a bit less:
FE-4320 - Increase the column width for CRU_LINE_RANGE column in CRU_INLINE_COMMENT_TO_FRX_REV table
Watch it to be notified of its progress and feel free to comment there.
To help resolve the current exception, use the attached sql script that should help in truncating the larger columns. This script will alter the line range of the comments. The comments themselves will be safe - just the number of lines in the files attached to the comments will be reduced.
To apply the patch, follow these steps:
1. Shut down Fisheye.
2. Make a backup of your <FISHEYE_INST> and <FISHEYE_HOME> directories in case anything goes wrong with the script.
3. Copy the following code block and save it into a file called fixupdb.sql
fixupdb.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
CRU_LINE_RANGE,
length(CRU_LINE_RANGE) as CRU_LINE_RANGE_count,
LTRIM(substring(RIGHT(CRU_LINE_RANGE,100), LOCATE(',',RIGHT(CRU_LINE_RANGE, 100),0)+1)) as truncated,
length(LTRIM(substring(RIGHT(CRU_LINE_RANGE,100), LOCATE(',',RIGHT(CRU_LINE_RANGE, 100),0)+1))) as truncated_count
from cru_inline_comment_to_frx_rev
where length(cru_line_range) > 100;
update cru_inline_comment_to_frx_rev
set CRU_LINE_RANGE = LTRIM(substring(RIGHT(CRU_LINE_RANGE,100), LOCATE(',',RIGHT(CRU_LINE_RANGE, 100),0)+1))
where length(cru_line_range) > 100;
commit;
select
CRU_LINE_RANGE,
length(CRU_LINE_RANGE) as CRU_LINE_RANGE_count,
LTRIM(substring(RIGHT(CRU_LINE_RANGE,100), LOCATE(',',RIGHT(CRU_LINE_RANGE, 100),0)+1)) as truncated,
length(LTRIM(substring(RIGHT(CRU_LINE_RANGE,100), LOCATE(',',RIGHT(CRU_LINE_RANGE, 100),0)+1))) as truncated_count
from cru_inline_comment_to_frx_rev
where length(cru_line_range) > 100;
4. Put this script in your <FISHEYE_HOME> and run it on the internal database. The command will be something like:
1
java -Xms1024m -Xmx1024m -jar <FISHEYE_HOME>/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:<FISHEYE_HOME>/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" <FISHEYE_HOME>/fixupdb.sqlWhere <FISHEYE_HOME> is your install directory.
4. After that, if the script succeeds, you should be able to start Fisheye again and retrigger the migration.
Was this helpful?