Restoring backup failed due to foreign key constraint
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
Summary
Symptoms
Restoring backup fail with the following error:
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
29
30
31
32
33
34
java.io.IOException: Error talking to database: Problem with constraints script /opt/fisheye_home/sql/POSTGRESQL/schema/constraints_76.sql
at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:210)
at com.atlassian.crucible.migration.item.SQLBackup$1.restore(SQLBackup.java:177)
at com.cenqua.fisheye.ctl.Restore.run(Restore.java:191)
at com.cenqua.fisheye.ctl.Restore.main(Restore.java:267)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.cenqua.fisheye.FishEyeCtl.mainImpl(FishEyeCtl.java:98)
at com.cenqua.fisheye.FishEyeCtl.main(FishEyeCtl.java:41)
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /opt/fisheye_home/sql/POSTGRESQL/schema/constraints_76.sql
at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:317)
at com.atlassian.crucible.migration.item.DBImporter.importData(DBImporter.java:159)
at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:196)
... 9 more
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 67: "alter table cru_logitem add constraint FKC163BEFE44A616B5 foreign key (cru_review_id) references cru_review;"
(ERROR: insert or update on table "cru_logitem" violates foreign key constraint "fkc163befe44a616b5"
Detail: Key (cru_review_id)=(1) is not present in table "cru_review".), please contact http://www.atlassian.com/support/
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:483)
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:414)
at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:311)
... 11 more
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "cru_logitem" violates foreign key constraint "fkc163befe44a616b5"
Detail: Key (cru_review_id)=(1) is not present in table "cru_review".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:455)
... 13 more
Restore failed. This may leave your target FISHEYE_INST directory (/var/fisheye_inst) in an inconsistent state.
Diagnosis
From the above constraint fkc163befe44a616b5, check the constraint definition in the file FISHEYE_HOME/sql/POSTGRESQL/schema/constraints_*.sql
According to the definition of the constraint, verify the data in the database if there is any constraint violation, e.g.:
1
SELECT * FROM cru_logitem WHERE cru_review_id NOT IN (SELECT cru_review_id FROM cru_review);Cause
Database integrity issue due to constraint violation.
Solution
Resolution
Stop Fisheye/Crucible in the new server
Start Fisheye/Crucible in the old server
Backing up and restoring Fisheye data the database in the old server for rollback purposes
Stop Fisheye/Crucible in the old server
Delete the data that violate the constraint, e.g.:
1DELETE FROM cru_logitem WHERE cru_review_id NOT IN (SELECT cru_review_id FROM cru_review);Start Fisheye/Crucible in the old server
Create another backup for migrate
Stop Fisheye/Crucible in the old server
Restore the backup in the new server
Start Fisheye/Crucible in the new server
Was this helpful?