-
Bug
-
Resolution: Fixed
-
Highest
-
6.8.0
-
11
-
Severity 2 - Major
-
311
-
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
- Backup Bitbucket with the backup client
- 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";