Restoring backup fail with 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 with backup client into another Bitbucket Server instance fail with the following error:
1
2
3
4
5
6
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:
1
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.
Solution
Resolution
Backup database for rollback purposes
Shutdown Bitbucket Server
Delete the problematic data:
1DELETE FROM cs_indexer_state WHERE repository_id NOT IN (SELECT id FROM repository);Restart Bitbucket Server
Recreate the backup with backup client
Was this helpful?