Restoring backup fail with foreign key contraint

Troubleshooting Databases

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

Restoring backup with backup client into another Stash server fail with the following error:

2014-05-29 06:27:42,316 INFO         Processed 20000 changes of 37504
2014-05-29 06:27:46,569 INFO         Processing dataset 37 of 51, containing 105 changes
2014-05-29 06:27:46,634 ERROR        stash-20140529-061155-866.tar could not be restored. Reason: Failed to execute change: Insert Row; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`stashv3`.`cs_indexer_state`, CONSTRAINT `fk_cs_indexer_state_repository` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`) ON DELETE CASCADE)
com.atlassian.stash.internal.backup.liquibase.LiquibaseChangeExecutionException: Failed to execute change: Insert Row; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`stashv3`.`cs_indexer_state`, CONSTRAINT `fk_cs_indexer_state_repository` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`) ON DELETE CASCADE)
	at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:259) ~[stash-dao-impl-2.11.6.jar:na]
	at com.atlassian.stash.internal.backup.liquibase.DatabaseUpdater.endElement(DatabaseUpdater.java:81) ~[stash-dao-impl-2.11.6.jar:na]

Diagnosis

Perform the following SQL query in the database where the backup is generated:

SELECT * FROM cs_indexer_state WHERE repository_id NOT IN (SELECT id FROM repository);

If the SQL query return a result, there are database integrity issue in the database where the backup is generated.

Cause

This is a database integrity issue.

Resolution

  1. Backup database for rollback purposes
  2. Shutdown Stash
  3. Delete the problematic data:

    DELETE FROM cs_indexer_state WHERE repository_id NOT IN (SELECT id FROM repository);
    
  4. Restart Stash
  5. Recreate the backup with backup client
Last modified on Mar 30, 2016

Was this helpful?

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