問題

Adding SSH key to Project and Repository Access key will fail with following appears in the atlassian-bitbucket.log

2019-02-14 08:20:30,283 ERROR [http-nio-7990-exec-5] @9W2ADRx500x81x0 xxxx xxx.xxx.xxx.xxx "POST /mvc/error500 HTTP/1.1" c.a.s.i.web.ErrorPageController There was an unhandled exception loading [/plugins/servlet/ssh/projects/TEST/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.

原因

Sequence out of sync with the current data

ソリューション

1. Shutdown Bitbucket Server

2. Run the following SQL to find the max ID number:

   SELECT
       MAX(public."AO_FB71B4_SSH_PUBLIC_KEY"."ENTITY_ID")
   FROM
       public."AO_FB71B4_SSH_PUBLIC_KEY";

3. From the above result, run the following SQL to fix it:

   ALTER SEQUENCE public."AO_FB71B4_SSH_PUBLIC_KEY_ENTITY_ID_seq" RESTART WITH <above result + 1>;

   Replace <above result + 1> with the result from Step 3 plus 1

   データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

4. Restart Bitbucket Server