Uploaded image for project: 'Bitbucket Server'
  1. Bitbucket Server
  2. BSERV-12075

When restoring with the Backup client to PostgreSQL 10+ the AO table sequences are not updated

    XMLWordPrintable

    Details

      Description

      Issue Summary

      When restoring with the backup client to PostgreSQL 10+ the AO table sequences are not in-sync. This also occurs with PostgreSQL 11+. This has been observed for Backup clients 3.3.4 - 3.6.0 (others untested).

      Steps to Reproduce

      1. Backup Bitbucket with the backup client
      2. Restore Bitbucket with the backup client to PostgreSQL DB 10+

      Expected Results

      The sequences for AO tables are in-sync with the entries in the AO table

      Actual Results

      The sequences have reset for the AO table

      The below exception is thrown in the atlassian-bitbucket.log file when Bitbucket attempts to add a new entry to the AO table:

      ... 355 frames trimmed
       Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_FB71B4_SSH_PUBLIC_KEY_pkey"
       Detail: Key ("ENTITY_ID")=(14) 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 net.java.ao.db.PostgreSQLDatabaseProvider.executeInsertReturningKey(PostgreSQLDatabaseProvider.java:336)
       at net.java.ao.DatabaseProvider.insertReturningKey(DatabaseProvider.java:1882)
       at net.java.ao.db.PostgreSQLDatabaseProvider.insertReturningKey(PostgreSQLDatabaseProvider.java:298)
       at net.java.ao.EntityManager.create(EntityManager.java:367)
       at net.java.ao.EntityManager.create(EntityManager.java:400)
       at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:100)
       ... 84 common frames omitted
       ...skipping...
       at com.atlassian.stash.internal.sp

      Workaround

      Currently there is no known workaround for this behavior. Sequences can be manually fixed by following Fixing Sequences - PostgreSQL wiki

      You can check if the AO table is out of sync by using the following commands and comparing the output. Example for the AO_FB71B4_SSH_PUBLIC_KEY table:

      --- Dump table contents
      SELECT * FROM "AO_FB71B4_SSH_PUBLIC_KEY" ORDER BY "ENTITY_ID" DESC;
      --- Check sequence count
      SELECT * FROM "AO_FB71B4_SSH_PUBLIC_KEY_ENTITY_ID_seq;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              ephillips@atlassian.com Edward
              Votes:
              6 Vote for this issue
              Watchers:
              11 Start watching this issue

                Dates

                Created:
                Updated:

                  Backbone Issue Sync

                  • BbS JAC Mirror <> Bitbucket Server
                    Synced with:
                    BBSJAC-493
                    Issue sync status:
                    UP TO DATE
                    Last received:
                    Last sent: