ERROR: duplicate key value violates unique constraint "AO_FB71B4_SSH_PUBLIC_KEY_pkey"

[Alin Ghita]

Hello,

After I migrated Bitbucket from Windows server to Linux server and from SQL Server to Postgresql I can't update/add any SSH keys to Bitbucket projects or repositories. 

Of course no builds works in Bamboo either because of this. 

The error is : 

2019-02-14 08:20:30,283 ERROR [http-nio-7990-exec-5] @9W2ADRx500x81x0 nqipj1 193.226.171.226,10.0.1.2 "POST /mvc/error500 HTTP/1.1" c.a.s.i.web.ErrorPageController There was an unhandled exception loading [/plugins/servlet/ssh/projects/LS/keys/add]
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:10.4 (Ubuntu 10.4-0ubuntu0.18.04)
- minor version:4
- major version:10
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1211

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_FB71B4_SSH_PUBLIC_KEY_pkey"
Detail: Key ("ENTITY_ID")=(22) already exists.
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:102)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:271)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at com.atlassian.bitbucket.internal.key.ssh.dao.AoSshKeyDao.create(AoSshKeyDao.java:66)

The applications are now on HTTPS behind a proxy, everything works well, I have Application links working between Jira, Confluence, Bitbucket and Bamboo. 

I can see the repositories from Bamboo to Bitbucket, but I receive the following error when I try to add a new repo in Bamboo : 

Could not check if existing access key is valid: An error occurred while processing the request. Check the server logs for more information.

So, it seems everything is related to the index : AO_FB71B4_SSH_PUBLIC_KEY_pkey in Bitbucket database. 

bitbucketdb=# select * from "AO_FB71B4_SSH_PUBLIC_KEY_pkey";
ERROR: "AO_FB71B4_SSH_PUBLIC_KEY_pkey" is an index
LINE 1: select * from "AO_FB71B4_SSH_PUBLIC_KEY_pkey";

The migration was made with the bitbucket tool : https://confluence.atlassian.com/bitbucketserver/using-the-bitbucket-server-backup-client-776640064.html#UsingtheBitbucketServerBackupClient-runrestore

Anyone have any clue what can I do? 

Regards,

Alin

[Ethan Trewhitt]

We ran into this issue at my company and it stems from an apparent fault with the Bitbucket backup scripts. They seem to fail to properly set the sequence values on the autoincrementing fields in the database, which eventually leads to failures when Bitbucket attempts to insert into these fields.

The fix for us was to follow the process on this page, running the script at the top of the page (which itself writes another script, which you then run):

https://wiki.postgresql.org/wiki/Fixing_Sequences

This fixes the sequence values for the whole database, setting each of them to the current maximum value in the DB. You ought to back up the database beforehand and it might not hurt to shut down Bitbucket first to make sure there are no race conditions.

[Stephen Sifers]

Hello Alin,

Thank you for providing great detail into the error you’re having. This type of issue is database related and will require sensitive information being shared to resolve the issue. With this said, we would suggest opening a support case to have this resolved.

Please navigate to Contact Support and fill out all of the required information. Support will contact you shortly after creating your case. Could you also please let us know your case number here so we may monitor it to ensure your issue is resolved.

Regards,
Stephen Sifers