patchgeneric.sql Fails to Help Upgrading Fisheye from Version 1.6.6 to Version 2.8.0
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
Symptoms
Execution of patchgeneric.sql fails as follow:
1
java -Xmx2048m -jar <FISHEYE_HOME>/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:<FISHEYE_INST>/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patchgeneric.sql1
2
3
SQL Error at 'patchgeneric.sql' line 3:
"delete from FR_EXTRA where fileRevision not in (Select REVISION_ID from REVISION)"
Integrity constraint violation FKD00FBDDBD91F70A1 table: REVISION_COMMENTCause
Integrity constraint violation FKD00FBDDBD91F70A1 table: REVISION_COMMENT
This is happening because there is file revisions in the Fisheye/Crucible database for file versions that have not been indexed by Fisheye. This could happen with Crucible 1.6.6 but in the later versions it became mandatory for a crucible revision to have its associated file version indexed by Fisheye.
Resolution
Copy the following into files and save accordingly
patch-Revision_Comment.sql
1
2
delete from revision_comment where rc_id in (select RC_ID from REVISION_COMMENT where frx_id in (select frx_id from FR_EXTRA where fileRevision not in (Select REVISION_ID from REVISION)));
commit;patchgeneric.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
delete from COMMENT_FIELD where COMMENT_ID not in (select COMMENT_ID from COMMENT);
delete from REVISION where uploadItem not in (select UPLOAD_ID from UPLOAD)
delete from FR_EXTRA where fileRevision not in (Select REVISION_ID from REVISION);
delete from FR_EXTRA where fromRevision not in (Select REVISION_ID from REVISION);
delete from COMPLETED_FRX where PARTICIPANT_ID not in (select PARTICIPANT_ID from REVIEW_PARTICIPANT);
delete from COMPLETED_FRX where FRX_ID not in (select FRX_ID from FR_EXTRA);
delete from FEINDEX_MSG where FR_ID not in (Select REVISION_ID from REVISION);
delete from FR_DETAIL where id not in (Select REVISION_ID from REVISION);
delete from PATCH_REVISION where PATCH_ID not in (select PATCH_ID from PATCH);
delete from PATCH_REVISION where REVISION_ID not in (select REVISION_ID from REVISION);
delete from RECIPIENT where NOTIFICATION_ID not in (Select NOTIFICATION_ID from NOTIFICATION);
delete from REPLY where COMMENT_ID not in (select COMMENT_ID from COMMENT);
delete from REVIEW_COMMENT where COMMENT_ID not in (select COMMENT_ID from COMMENT);
delete from REVISION_COMMENT where COMMENT not in (select COMMENT_ID from COMMENT);
delete from REVISION_COMMENT where revision not in (Select REVISION_ID from REVISION);
delete from REVISION_COMMENT where FRX_ID not in (select FRX_ID from FR_EXTRA);
delete from Comment where review_id not in (select review_ID from review);
delete from FR_EXTRA where REVIEW_ID not in (select review_ID from review);
delete from INVITEES where REVIEW_ID not in (select review_ID from review);
delete from NOTIFICATION where REVIEW not in (select review_ID from review);
delete from PATCH where REVIEW not in (select review_ID from review);
delete from REVIEW where PARENTREVIEW not in (select review_ID from review);
delete from review where project not in (select project_id from cruproject);
delete from REVIEW_COMMENT where REVIEW_ID not in (select review_ID from review);
delete from REVIEW_FIELD where REVIEW_ID not in (select review_ID from review);
delete from REVIEW_PARTICIPANT where REVIEW_ID not in (select review_ID from review);
delete from REVPERMAID where REVIEW_ID not in (select review_ID from review);
commit;Execute first patch-Revision_Comment.sql using:
1
java -Xmx2048m -jar <FISHEYE_HOME>/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:<FISHEYE_INST>/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patch-Revision_Comment.sqlThen execute again the original command using patchgeneric.sql
1
java -Xmx2048m -jar <FISHEYE_HOME>/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:<FISHEYE_INST>/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patchgeneric.sqlRelated
Was this helpful?