Issue Summary

Gatekeeper and UPM breaks beyond in Confluence version >= 7.11.0 for MSSQL and Oracle database

Steps to Reproduce

1. Install Confluence version 7.13.1 with MSSQL
2. Connect an LDAP/Crowd containing a group that has more than 2100 nested groups
3. Give this group a canuse permission in Global Permission page
4. Synchronise the directory
5. Restart Confluence

This should be replicable in Oracle as well since it also enforces a limit of 1000

Expected Results

No Errors

Actual Results

• We see following errors where MSSQL complains that the query has more then 2100 parameters in IN condition

  021-10-19 11:16:10,405 ERROR [perm-delta-cache-receiver] [gatekeeper.evaluator.cache.CacheUpdateReceiver] error Failed to initialize evaluator cache
  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:228)
      at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:392)
      at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:351)
      at com.atlassian.crowd.embedded.hibernate2.HibernateMembershipDao.search(HibernateMembershipDao.java:153)
      at com.atlassian.confluence.impl.user.crowd.CachedCrowdMembershipDao.search(CachedCrowdMembershipDao.java:151)
      at jdk.internal.reflect.GeneratedMethodAccessor490.invoke(Unknown Source)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.base/java.lang.reflect.Method.invoke(Unknown Source)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
      at com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
      at com.sun.proxy.$Proxy264.search(Unknown Source)
      at com.atlassian.crowd.directory.AbstractInternalDirectory.searchGroupRelationships(AbstractInternalDirectory.java:883)
  

2021-10-20 08:34:28,283 ERROR [http-nio-8090-exec-5] [[Standalone].[localhost].[/].[servlet-module-container-servlet]] log Servlet.service() for servlet [servlet-module-container-servlet] in context with path [] threw exception
io.atlassian.util.concurrent.LazyReference$InitializationException: io.atlassian.util.concurrent.LazyReference$InitializationException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at io.atlassian.util.concurrent.LazyReference.getInterruptibly(LazyReference.java:156)
    at io.atlassian.util.concurrent.LazyReference.get(LazyReference.java:116)
    at io.atlassian.util.concurrent.ResettableLazyReference.get(ResettableLazyReference.java:95)
    at com.atlassian.upm.LazyReferences.safeGet(LazyReferences.java:15)
    at com.atlassian.upm.core.pac.BaseClientContextFactory.getClientContext(BaseClientContextFactory.java:67)
    at com.atlassian.upm.core.pac.BaseClientContextFactory.getClientContext(BaseClientContextFactory.java:47)
Caused by: io.atlassian.util.concurrent.LazyReference$InitializationException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at io.atlassian.util.concurrent.LazyReference.getInterruptibly(LazyReference.java:156)
	at io.atlassian.util.concurrent.LazyReference.get(LazyReference.java:116)
	at io.atlassian.util.concurrent.ResettableLazyReference.get(ResettableLazyReference.java:95)
	at com.atlassian.upm.LazyReferences.safeGet(LazyReferences.java:15)
	at com.atlassian.upm.core.impl.ConfUserAccessor.getActiveUserCount(ConfUserAccessor.java:26)
	at com.atlassian.upm.api.util.Option$4.fold(Option.java:353)
	at com.atlassian.upm.api.util.Option.getOrElse(Option.java:71)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:260)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
	at com.microsoft.sqlserv

Workaround

• Disabling the user directory helps in showing Manage apps screen correctly but the error will appear again after a restart
• Reduce the nested groups to below 2100 in case of MSSQL or below 1000 in Oracle
• Disable Gatekeeper Plugin