Database Migration from HSQLDB to Oracle Fails with "value too large" Error on CRU_INLINE_COMMENT_TO_FRX_REV table
Database migration from embedded HSQLDB to Oracle fails with following error reported in fisheye-debug-<date>.log:
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)
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-3863Getting issue details... STATUS
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.
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-4320Getting issue details... STATUS
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. Download the script fixupdb.sql attached, put this script in your <FISHEYE_HOME> and run it on the internal database. The command will be something like:
java -Xms1024m -Xmx1024m -jar <FISHEYE_HOME>/lib/hsqldb-220.127.116.11.jar --inlineRC "URL=jdbc:hsqldb:file:<FISHEYE_HOME>/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" <FISHEYE_HOME>/fixupdb.sql
Where <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.