Rebuilding Ancestor Table fails with duplicate key value violation in Confluence Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
While rebuilding Ancestor table in Confluence, the following error is noticed in the Atlassian Confluence logs:
1
2
3
4
5
6
7
8
9
10
2020-08-05 21:25:25,308 ERROR [http-nio-8080-exec-4] [confluence.pages.ancestors.HibernatePageAncestorManager] addAncestorsToTableForDescendant Couldn't execute statement
-- referer: https://confluence/admin/permissions/pagepermsadmin.action | url: /admin/permissions/rebuild_ancestor_table.action | traceId: c9365ae3379de36f | userName: admin | action: rebuild_ancestor_table
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
.
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "idx_16387908_primary"
Detail: Key (descendentid, ancestorposition)=(729820032, 0) already exists.Cause
This error may happen due to issues with page tree movements in Confluence resulting in duplicates entries in the Ancestor table ( CONFANCESTORS ).
Solution
To resolve this issue go through the following steps.
Stop Confluence on all nodes
Take backup of the database of Confluence
Delete all contents from the
confancestorstable1TRUNCATE table confancestors;Start Confluence on only one node
Access Confluence Administration UI and browse to General configuration > Logging and Profiling
Add the following entry and set the logging level to
DEBUG1com.atlassian.confluence.pages.ancestors.HibernatePageAncestorManagerRebuild Ancestor Data
Prior to Confluence 7.20.0
Access to
<CONFLUENCE_BASE_URL>/admin/permissions/pagepermsadmin.actionand click on the Rebuild ancestor tableAfter Confluence 7.20.0
Head over to General Configuration > Scheduled Job > Repair the Ancestors Table
Follow the rebuild process through the application log --
atlassian-confluence.logThe following entry indicates the rebuild started
12020-08-07 15:19:19,613 INFO [http-nio-6740-exec-8] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild StartedThe following entry indicates when it is completed
12020-08-07 15:19:19,637 INFO [http-nio-6740-exec-8] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Complete!
Was this helpful?