Restoring backup fail with foreign key constraint
Symptoms
Restoring backup with backup client into another Bitbucket Server instance 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 bitbucket-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 (`bitbucketv3`.`cs_indexer_state`, CONSTRAINT `fk_cs_indexer_state_repository` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`) ON DELETE CASCADE)
com.atlassian.bitbucket.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 (`bitbucketv3`.`cs_indexer_state`, CONSTRAINT `fk_cs_indexer_state_repository` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`) ON DELETE CASCADE)
at com.atlassian.bitbucket.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:259) ~[bitbucket-dao-impl-2.11.6.jar:na]
at com.atlassian.bitbucket.internal.backup.liquibase.DatabaseUpdater.endElement(DatabaseUpdater.java:81) ~[bitbucket-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
- Backup database for rollback purposes
- Shutdown Bitbucket Server
Delete the problematic data:
DELETE FROM cs_indexer_state WHERE repository_id NOT IN (SELECT id FROM repository);- Restart Bitbucket Server
- Recreate the backup with backup client
Last modified on Aug 8, 2017
Powered by Confluence and Scroll Viewport.