-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Low
-
Affects Version/s: 8.5.4, 8.5.9, 7.19.22, 8.9.3
-
Component/s: Search - Indexing
-
5
-
Severity 2 - Major
-
5
Issue Summary
Confluence integrated with Oracle Database will throw the following error:
ORA-01795: maximum number of expressions in a list is 1000
This is reproducible on Data Center: Yes
Steps to Reproduce
Scenario 1 - when the page tree depth exceeds 1000.
- Create a nested page tree depth exceeding 1000 pages, e.g.
- Page 1 » Page 2 » Page 3 .... » Page 1000 » Page 1001
Scenario 2 - when the number of ancestor pages exceeds 1000.
Expected Results
No exceptions should appear in the logs.
Actual Results
The below exceptions are thrown:
atlassian-confluence.log
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:195) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:374) at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:320) at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateContentPermissionSetDao.getInheritedContentPermissionSets(HibernateContentPermissionSetDao.java:85) at com.atlassian.confluence.core.DefaultInheritedContentPermissionManager.getInheritedContentPermissionSets(DefaultInheritedContentPermissionManager.java:44) at com.atlassian.confluence.core.DefaultInheritedContentPermissionManager.getInheritedContentPermissionSetsIncludeEdit(DefaultInheritedContentPermissionManager.java:37) at com.atlassian.confluence.impl.security.CachingInheritedContentPermissionManager.lambda$getInheritedContentPermissionSets$2(CachingInheritedContentPermissionManager.java:96) at com.atlassian.confluence.impl.security.ContentPermissionSetCache.lambda$getOrLoad$0(ContentPermissionSetCache.java:41) at com.atlassian.confluence.cache.CacheOperations.lambda$get$0(CacheOperations.java:172) at java.base/java.util.HashMap.computeIfAbsent(HashMap.java:1220) at com.atlassian.confluence.cache.CacheOperations.get(CacheOperations.java:172) at com.atlassian.confluence.cache.DeferredOperationsCache.getOrLoad(DeferredOperationsCache.java:92) at com.atlassian.confluence.cache.DeferredOperationsCache.get(DeferredOperationsCache.java:56) at com.atlassian.confluence.cache.TransactionalCacheFactory$TransactionalCache.get(TransactionalCacheFactory.java:346) at com.atlassian.confluence.impl.cache.tx.TransactionAwareCache$1.get(TransactionAwareCache.java:43) at com.atlassian.confluence.impl.security.ContentPermissionSetCache.getOrLoad(ContentPermissionSetCache.java:39) at com.atlassian.confluence.impl.security.CachingInheritedContentPermissionManager.getInheritedContentPermissionSets(CachingInheritedContentPermissionManager.java:96) at com.atlassian.confluence.impl.security.CachingInheritedContentPermissionManager.getInheritedContentPermissionSets(CachingInheritedContentPermissionManager.java:82) at com.atlassian.confluence.impl.search.v2.DefaultContentPermissionCalculator.getInheritedPermissions(DefaultContentPermissionCalculator.java:150) at com.atlassian.confluence.impl.search.v2.DefaultContentPermissionCalculator.calculate(DefaultContentPermissionCalculator.java:59) at com.atlassian.confluence.impl.search.v2.DefaultContentPermissionCalculator.calculate(DefaultContentPermissionCalculator.java:32) ... Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) at org.hibernate.loader.Loader.doQuery(Loader.java:956) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) at org.hibernate.loader.Loader.doList(Loader.java:2868) at org.hibernate.loader.Loader.doList(Loader.java:2850) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) at org.hibernate.loader.Loader.list(Loader.java:2677) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1459) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateContentPermissionSetDao.lambda$getInheritedContentPermissionSets$2(HibernateContentPermissionSetDao.java:114) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:367) ... 85 more Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 ... aused by: Error : 1795, Position : 326, Sql = select contentper0_.ID as id1_21_, contentper0_.CONT_PERM_TYPE as cont_perm_type2_21_, contentper0_.CONTENT_ID as content_id3_21_, contentper0_.CREATIONDATE as creationdate4_21_, contentper0_.LASTMODDATE as lastmoddate5_21_ from CONTENT_PERM_SET contentper0_ where contentper0_.CONT_PERM_TYPE=:1 and (contentper0_.CONTENT_ID in (:2 , :3 .... :1000 , :1001 , :1002 )).....
atlassian-confluence-index.log
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:195) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:374) at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:320) at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateContentPermissionSetDao.getInheritedContentPermissionSets(HibernateContentPermissionSetDao.java:123) at com.atlassian.confluence.impl.search.v2.extractor.ContentPermissionsBulkExtractor.extractAll(ContentPermissionsBulkExtractor.java:63) at com.atlassian.confluence.internal.index.lucene.BulkFieldPrefetcher.lambda$prefetch$1(BulkFieldPrefetcher.java:75) at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197) at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) at com.atlassian.confluence.internal.index.lucene.BulkFieldPrefetcher.prefetch(BulkFieldPrefetcher.java:73) at com.atlassian.confluence.internal.index.lucene.BulkFieldPrefetcher.prefetch(BulkFieldPrefetcher.java:60) at com.atlassian.confluence.internal.index.lucene.BulkFieldPrefetcher.createPrefetchedDocumentBuilder(BulkFieldPrefetcher.java:48) ... Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet ... at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateContentPermissionSetDao.getContentPermissionSets(HibernateContentPermissionSetDao.java:168) at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateContentPermissionSetDao.lambda$getInheritedContentPermissionSets$6(HibernateContentPermissionSetDao.java:136) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:367) ... 31 more Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 ... Caused by: Error : 1795, Position : 895, Sql = select contentper0_.ID as id1_21_0_, contentper1_.ID as id1_20_1_, contentper0_.CONT_PERM_TYPE as cont_perm_type2_21_0_, contentper0_.CONTENT_ID as content_id3_21_0_, contentper0_.CREATIONDATE as creationdate4_21_0_, contentper0_.LASTMODDATE as lastmoddate5_21_0_, contentper1_.CP_TYPE as cp_type2_20_1_, contentper1_.USERNAME as username3_20_1_, contentper1_.GROUPNAME as groupname4_20_1_, contentper1_.CPS_ID as cps_id5_20_1_, contentper1_.CREATOR as creator6_20_1_, contentper1_.CREATIONDATE as creationdate7_20_1_, contentper1_.LASTMODIFIER as lastmodifier8_20_1_, contentper1_.LASTMODDATE as lastmoddate9_20_1_, contentper1_.CPS_ID as cps_id5_20_0__, contentper1_.ID as id1_20_0__ from CONTENT_PERM_SET contentper0_, CONTENT_PERM contentper1_, CONTENT contentent2_ where contentper0_.ID=contentper1_.CPS_ID(+) and contentper0_.CONTENT_ID=contentent2_.CONTENTID and (contentper0_.CONTENT_ID in (:1 , :2 ... :19595 , :19596 , :19597 , :19598 )),
Diagnosis
Run this query to identify all pages with a page tree depth >= 1000.
Find all pages with page depth >= 1000
select CONCAT('<confluence-base-url>/pages/viewpage.action?pageId=', descendentid) as pageURL, count(*) as pagedepth from confancestors group by descendentid having count(*) >= 1000 order by pagedepth desc
Example SQL output
PAGEURL PAGEDEPTH --------------------------------------------------------- --------- <confluence-base-url>/pages/viewpage.action?pageId=918506 1002 <confluence-base-url>/pages/viewpage.action?pageId=918505 1001 <confluence-base-url>/pages/viewpage.action?pageId=918504 1000
Workaround
For Scenario 1 - when the page tree depth exceeds 1000.
In Space Tools, move/re-order the pages so the maximum page tree depth does not exceed 1000 sub-pages in depth.
For Scenario 2 - when the number of ancestor pages exceeds 1000.
Check whether the following JVM flags are present with high values:
-Dconfluence.reindex.attachments.to.pop (Default value is 20) -Dconfluence.reindex.documents.to.pop (Default value is 10)
If the above JVM flags are present, try lowering the custom assigned value or deleting the JVM flag to revert to default values.