Database migration fails due to duplicate key value
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
Summary
When using the built-in database migration tool the migration fails with a duplicate key value constraint message.
Environment
- Any Bitbucket version
- Postgres Database
Diagnosis
Check the logs for similar stack traces:
2020-09-11 18:12:39,296 ERROR [threadpool:thread-15] username @i@PZQFBCx921x19253x0 1s371we 0.0.0.0 "POST /admin/db/edit HTTP/1.1" c.a.s.i.b.l.DefaultLiquibaseDao Unable to insert into table plugin_setting data [namespace=bitbucket.global.settings, key_name=wittified.announcer.changeToken, key_value=efbe1694-1a52-4f37-bac5-b2ecfc8b5490-1601394003489, id=763] with statement insert into public.plugin_setting (namespace, key_name, key_value, id) values (?, ?, ?, ?)
2020-09-11 18:12:40,697 ERROR [threadpool:thread-15] username @i@PZQFBCx921x19253x0 1s371we 0.0.0.0 "POST /admin/db/edit HTTP/1.1" c.a.s.i.m.m.BaseMigrationTask Reverting database configuration after a failed migration attempt
com.atlassian.stash.internal.backup.liquibase.LiquibaseChangeExecutionException: Failed to execute change: Inserts data into an existing table; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uq_plug_setting_ns_key"
Detail: Key (key_name, namespace)=(wittified.announcer.changeToken, bitbucket.global.settings) already exists.
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:306)
at com.atlassian.stash.internal.backup.liquibase.DatabaseUpdater.onChangesetContent(DatabaseUpdater.java:63)
at com.atlassian.stash.internal.backup.liquibase.processors.CompositeChangeSetProcessor.onChangesetContent(CompositeChangeSetProcessor.java:34)
at com.atlassian.stash.internal.backup.liquibase.ChangeSetReader.endElement(ChangeSetReader.java:63)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanEndElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at com.atlassian.security.xml.RestrictedXMLReader.parse(RestrictedXMLReader.java:103)
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseMigrationDao.parse(DefaultLiquibaseMigrationDao.java:232)
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseMigrationDao.lambda$restore$0(DefaultLiquibaseMigrationDao.java:189)
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.withLock(DefaultLiquibaseDao.java:333)
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseMigrationDao.restore(DefaultLiquibaseMigrationDao.java:185)
at com.atlassian.stash.internal.maintenance.restore.DatabaseRestoreStep.run(DatabaseRestoreStep.java:103)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask$Step.run(CompositeMaintenanceTask.java:133)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask.run(CompositeMaintenanceTask.java:69)
at com.atlassian.stash.internal.maintenance.restore.RestorePhase.run(RestorePhase.java:27)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask$Step.run(CompositeMaintenanceTask.java:133)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask.run(CompositeMaintenanceTask.java:69)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask$Step.run(CompositeMaintenanceTask.java:133)
at com.atlassian.stash.internal.maintenance.CompositeMaintenanceTask.run(CompositeMaintenanceTask.java:69)
at com.atlassian.stash.internal.maintenance.MaintenanceModePhase.run(MaintenanceModePhase.java:27)
at com.atlassian.stash.internal.maintenance.migration.BaseMigrationTask.run(BaseMigrationTask.java:68)
at com.atlassian.stash.internal.maintenance.migration.DatabaseMigrationTask.run(DatabaseMigrationTask.java:49)
at com.atlassian.stash.internal.maintenance.DefaultMaintenanceTaskMonitor.run(DefaultMaintenanceTaskMonitor.java:212)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at com.atlassian.stash.internal.concurrent.DefaultTransferableStateManager$StateTransferringRunnable.run(DefaultTransferableStateManager.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.lang.Thread.run(Thread.java:748)
... 1 frame trimmed
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uq_plug_setting_ns_key"
Detail: Key (key_name, namespace)=(wittified.announcer.changeToken, bitbucket.global.settings) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:299)
... 39 common frames omittedAs noted in the stack trace, the message states that it is unable to insert into the plugin_setting table due to a duplicate key value violating the unique constraint of uq_plug_setting_ns_key. Exactly what it is attempting to insert will vary on a case-by-case basis.
Cause
The exact cause is unknown and varies per instance. Some common behavior that may cause this issue include:
- Past failed attempts to upgrade, where an incomplete/partial rollback was performed.
- Improperly restoring the database in which the necessary keys were stripped on the table thus allowing duplicates to exist.
In either case: when migrating into a new target database, the constraints are properly applied and reject the insert of this duplicate record into the table.
Solution
The solution requires database modification to remove the duplicate entry.
- Shut down Bitbucket and backup the application database and file system, ensuring their backups are in sync.
Remove the duplicate database entry. For example, using the data in the stack trace above, you could run the following with the consultation of your DBA:
# List the reported duplicate: select * from plugin_setting where key_value = 'efbe1694-1a52-4f37-bac5-b2ecfc8b5490-1601394003489'; # List all by the reported key_name to visually verify duplicate key name entries: select * from plugin_setting where key_name = 'wittified.announcer.changeToken'; # Remove the duplicate: delete from plugin_setting where key_value = 'efbe1694-1a52-4f37-bac5-b2ecfc8b5490-1601394003489';- Start Bitbucket
If you have a staging/test platform that used production data in its creation, this issue might exist on these platforms as well. In this case, you can apply the same diagnostic process and resolution listed here first before applying the same to your production environment.
Last modified on Dec 18, 2020
Powered by Confluence and Scroll Viewport.