Bitbucket Server Data Center restore fails - ORA-02291: integrity constraint
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
This article only applies to Bitbucket versions lower than 5.
Starting from version 5.0.0, the logic has been reviewed and the constraints causing these behaviours have been removed.
When looking to perform a migration to a new database and facing this issue, follow these steps:
- upgrade to a version higher than 5 (Release notes)
- migrate to the new database using the options in the user interface
Problem
While trying to restore a Bitbucket Server Data Center from a backup using Bitbucket Server Backup client the restore fails due to some integrity constraints.
The same occurs when attempting to migrate to a new database via the user interface.
The following appears in the restore log:
2016-02-04 12:01:56,578 ERROR [main] c.a.s.i.backup.client.RestoreMain stash-20160204-024318-415.tar could not be restored. Reason: Failed to execute change: Insert Row; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (GIT_REPO2.FK_STA_COMMENT_PARENT) violated - parent key not found
com.atlassian.stash.internal.backup.liquibase.LiquibaseChangeExecutionException: Failed to execute change: Insert Row; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (GIT_REPO2.FK_STA_COMMENT_PARENT) violated - parent key not found
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:280) ~[stash-dao-impl-3.9.0.jar:na]
at com.atlassian.stash.internal.backup.liquibase.DatabaseUpdater.onChangesetContent(DatabaseUpdater.java:63) ~[stash-dao-impl-3.9.0.jar:na]
at com.atlassian.stash.internal.backup.liquibase.ChangeSetReader.endElement(ChangeSetReader.java:72) ~[stash-dao-impl-3.9.0.jar:na]
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(AbstractSAXParser.java:609) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(XMLDocumentFragmentScannerImpl.java:1783) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:606) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(XMLNSDocumentScannerImpl.java:118) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:510) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:848) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:777) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141) ~[na:1.8.0_71]
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1213) ~[na:1.8.0_71]
at com.atlassian.security.xml.RestrictedXMLReader.parse(RestrictedXMLReader.java:103) ~[atlassian-secure-xml-3.2.4.jar:na]
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseMigrationDao.parse(DefaultLiquibaseMigrationDao.java:222) ~[stash-dao-impl-3.9.0.jar:na]
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.withLock(DefaultLiquibaseDao.java:307) ~[stash-dao-impl-3.9.0.jar:na]
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseMigrationDao.restore(DefaultLiquibaseMigrationDao.java:172) ~[stash-dao-impl-3.9.0.jar:na]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.restoreFile(DefaultRestoreClient.groovy:311) ~[stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.withDataSource(DefaultRestoreClient.groovy:268) ~[stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.restoreDatabase(DefaultRestoreClient.groovy:220) ~[stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.restoreSystem(DefaultRestoreClient.groovy:254) ~[stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.restore(DefaultRestoreClient.groovy:85) ~[stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.spring.SpringMain.run(SpringMain.groovy:28) [stash-backup-core-1.9.1.jar:na]
at com.atlassian.stash.internal.backup.client.RestoreMain.main(RestoreMain.groovy:55) [stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.JavaVersionCheckingShim.invokeClientClassMain(JavaVersionCheckingShim.java:71) [stash-restore-client.jar:1.9.1]
at com.atlassian.stash.internal.backup.client.JavaVersionCheckingShim.run(JavaVersionCheckingShim.java:85) [stash-restore-client.jar:1.9.1]
... 100 frames suppressed
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (GIT_REPO2.FK_STA_COMMENT_PARENT) violated - parent key not found
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:278) ~[stash-dao-impl-3.9.0.jar:na]
... 124 common frames omitted
Cause
The error you're seeing happens because the sta_comment table in the database has a self-referential foreign key. The parent_id and root_id columns refer back to the same table.
In the backup, we write out all rows in order. To be able to restore the table again, the restore procedure needs to ensure that all rows that have Foreign Key constraints must be restored before the rows that reference them, in other words, the root comment and parent comment need to be written to the backup before the reply is written.
To ensure that, in the restore the comment_id is used to order them, assuming that the parent_id is always smaller than the comment_id.
In a Data Center installation that assumption does not hold because of the way IDs are generated.
Each node generates batches of IDs that it will then use when new rows are created.
For example
- node1 gets ID 21-30
- node2 gets 31-40
If a new comment is created on node2, it gets id 31.
If a reply to that comment is created on node1 it gets id 21, breaking the assumption.
This happens only for Bitbucket Server Backup Client making it unreliable for performing backup and restore for Data Center instances: BSERV-8403 - Getting issue details... STATUS
Resolution
These are the available options:
- Preferred: upgrade to Bitbucket 5.0 before performing the restore / migration
- Use Bitbucket Server DIY backup to perform both the backup and the restore for Data Center instances.