The child_position column in CONTENT table of confluence DB is not up to date with the hierarchy

[Prateek_Sharma]

We have figured out below link to check if the confancestor table is corrupted. We queried the table and found that ancestor table is actually corrupted. We followed the other steps in the below link. 

https://confluence.atlassian.com/doc/rebuilding-the-ancestor-table-153948.html

The Ancestor table was rebuilt followed by content indexing. Below are logs excerpts from rebuild activity.

2018-08-19 10:50:14,519 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] apply Stored ancestors for child pages... 507000/508873

2018-08-19 10:50:15,785 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] apply Stored ancestors for child pages... 508000/508873

2018-08-19 10:50:16,820 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] apply Stored ancestors for child pages... 508873/508873

2018-08-19 10:50:16,821 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Ancestors persisted to database in 638900 ms

2018-08-19 10:50:16,822 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Complete!

2018-08-19 10:50:16,823 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Statistics: Pages with parents processed = 508046

2018-08-19 10:50:16,825 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Statistics: Ancestors inserted in database = 1728917

2018-08-19 10:50:16,826 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Statistics: Maximum ancestor depth = 14

2018-08-19 10:50:16,827 INFO [http-nio-8080-exec-10] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Statistics: Mean ancestors per page with parent = 3

The same SQL query was run again and now returned with few rows. Do I again need to rebuild the ancestor table  or is it a different issue ?

Please help

Thanks.

[Diego · Accepted solution]

Hello Prateek, good to hear that you were able to find our documentation to help with you case!
If you run the query:

SELECT A.ANCESTORID, A.DESCENDENTID, COUNT(A.DESCENDENTID)
FROM CONFANCESTORS A, CONTENT B
WHERE B.CONTENTID = A.DESCENDENTID
GROUP BY A.ANCESTORID, A.DESCENDENTID HAVING COUNT(A.DESCENDENTID) != 1
ORDER BY 3 DESC

And it returns any results, the ANCESTORS table needs to be rebuilt.

We always advise customers to schedule a downtime for Confluence when performing this. The better course of action is to have no users at all accessing Confluence.

Reference

Rebuilding the Ancestor Table

Hope this clarifies this process!