Restoring backup failed due to foreign key constraint
Symptoms
Restoring backup fail with the following error:
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.:
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.
Resolution
- Stop Fisheye/Crucible in the new server
- Start Fisheye/Crucible in the old server
- Backup 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.:
DELETE 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
Last modified on Jul 31, 2018
Powered by Confluence and Scroll Viewport.