Restoring backup failed due to foreign key constraint

Still need help?

The Atlassian Community is here for you.

Ask the community

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

  1. Stop Fisheye/Crucible in the new server
  2. Start Fisheye/Crucible in the old server
  3. Backup the database in the old server for rollback purposes
  4. Stop Fisheye/Crucible in the old server
  5. 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);
  6. Start Fisheye/Crucible in the old server
  7. Create another backup for migrate
  8. Stop Fisheye/Crucible in the old server
  9. Restore the backup in the new server
  10. Start Fisheye/Crucible in the new server
Last modified on Jul 31, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.