Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-21870

"maximum number of expressions in a list is 1000" error when user is in more than 1000 groups visits dashboard

XMLWordPrintable

      In Oracle, if a user belongs to more than 1000 groups and visits the dashboard, the following error occurs:

      -- url: /confluence/pages/viewpage.action | page: 69468465 | userName: user | referer: Confluence URL | action: viewpage
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

Caused by: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
	at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
	at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
	at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
	at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
	at net.sf.hibernate.loader.Loader.list(Loader.java:1048)
	at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
	at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1552)
	at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
	at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
	at com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpaceDao$7.doInHibernate(HibernateSpaceDao.java:455)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
	at com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpaceDao.countSpaces(HibernateSpaceDao.java:447)
	at com.atlassian.confluence.spaces.DefaultSpaceManager$1.getAvailableSize(DefaultSpaceManager.java:799)
	at com.atlassian.confluence.core.DefaultListBuilder.getAvailableSize(DefaultListBuilder.java:51)
	at com.atlassian.confluence.plugins.macros.dashboard.DashboardMacroSupport.getLabelsForPermittedSpaces(DashboardMacroSupport.java:216)
	at com.atlassian.confluence.plugins.macros.dashboard.DashboardMacroSupport.getViewableTeamLabels(DashboardMacroSupport.java:201)

      The problem is that com.atlassian.confluence.spaces.persistence.dao.hibernate.HibernateSpacesQueryBuilder specifies the full list of groups the user is a member of in the "in" clause. It's unexpected for a user to be in more than 1000 groups, but it can happen, particularly if Confluence is delegating user management to a large LDAP server.

            [CONFSERVER-21870] "maximum number of expressions in a list is 1000" error when user is in more than 1000 groups visits dashboard

            Minh Tran added a comment -

            A fix for this issue is now available for Confluence Server customers.
            Upgrade now or check out the Release Notes to see what other issues are resolved.

            Minh Tran added a comment - A fix for this issue is now available for Confluence Server customers. Upgrade now or check out the Release Notes to see what other issues are resolved.

            Stephen Gramm added a comment -

            We see a similar problem with Confluence 5.10.8 Data Center.  If a user attempts to perform an XML export of several wiki pages from a very large wiki space the export fails and leaves this message in the application log:  Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

            We are glad to see Atlassian is beginning to look into this issue and hope for a quick resolution to this problem without having to upgrade Confluence to the next version (6.2.x).

            Stephen Gramm added a comment - We see a similar problem with Confluence 5.10.8 Data Center.  If a user attempts to perform an XML export of several wiki pages from a very large wiki space the export fails and leaves this message in the application log:  Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 We are glad to see Atlassian is beginning to look into this issue and hope for a quick resolution to this problem without having to upgrade Confluence to the next version (6.2.x).

            Qualcomm Support added a comment -

            Let us know when this issue will be fixed on Oracle Database as the users are unable to access confluence pages (6.0.3) if they are part of more than 1000 groups.

            Qualcomm Support added a comment - Let us know when this issue will be fixed on Oracle Database as the users are unable to access confluence pages (6.0.3) if they are part of more than 1000 groups.

            Jahirul Hassan added a comment - - edited

            I am having the issue in confluence 5.1.5 while importing space. Due to the error content's images "Attachments" are missing. Thanks

            action: restore-local-file
            com.atlassian.confluence.pages.ancestors.AncestorRebuildException: Error cleaning out the CONFANCESTORS table.
            at com.atlassian.confluence.pages.ancestors.HibernatePageAncestorManager.buildImpl(HibernatePageAncestorManager.java:82)

            Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

            Jahirul Hassan added a comment - - edited I am having the issue in confluence 5.1.5 while importing space. Due to the error content's images "Attachments" are missing. Thanks action: restore-local-file com.atlassian.confluence.pages.ancestors.AncestorRebuildException: Error cleaning out the CONFANCESTORS table. at com.atlassian.confluence.pages.ancestors.HibernatePageAncestorManager.buildImpl(HibernatePageAncestorManager.java:82) Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

            Shreeker Durgamcheruvu Ratnam added a comment -

            Hi, is there any update on this please.

            Shreeker Durgamcheruvu Ratnam added a comment - Hi, is there any update on this please.

            hemanth acharya added a comment -

            I am not quite sure I understand this, but in our case, a user who belongs to a group of more than 1000 people in it, it throws this error. I think the subject is little confusion. Can we have some update on this, because we cannot use "confluence questions" if this is not fixed. Thanks

            hemanth acharya added a comment - I am not quite sure I understand this, but in our case, a user who belongs to a group of more than 1000 people in it, it throws this error. I think the subject is little confusion. Can we have some update on this, because we cannot use "confluence questions" if this is not fixed. Thanks

            hemanth acharya added a comment -

            We are using confluence 5.5.2... The issue exists here as well. Is there any workaround for this? Thanks

            hemanth acharya added a comment - We are using confluence 5.5.2... The issue exists here as well. Is there any workaround for this? Thanks

            Victor Veinotte added a comment -

            We just upgraded to 3.5.5 and now some users experience this intermittently with less than 1000 LDAP groups as described by Kristof above.

            Victor Veinotte added a comment - We just upgraded to 3.5.5 and now some users experience this intermittently with less than 1000 LDAP groups as described by Kristof above.

            Kristof Vandermeersch added a comment - - edited

            We (Confluence 3.5.6) experience this error with users that are in less than 1000 LDAP groups:
            Users that are in 370 groups experience this error. Yes, 370 is more than the recommendation of 20 groups, but most companies with 500+ users have more than 20 groups per user...

            Is this issue solved in Confluence 4.0? Has anyone experience with this?

            Kristof Vandermeersch added a comment - - edited We (Confluence 3.5.6) experience this error with users that are in less than 1000 LDAP groups: Users that are in 370 groups experience this error. Yes, 370 is more than the recommendation of 20 groups, but most companies with 500+ users have more than 20 groups per user... Is this issue solved in Confluence 4.0? Has anyone experience with this?

            Christopher Mercer added a comment -

            This is a critical improvement for our environment.

            Christopher Mercer added a comment - This is a critical improvement for our environment.

              vhu@atlassian.com Vivian Hu (Inactive)
              asdosreis AlysonA
              Affected customers:
              23 This affects my team
              Watchers:
              33 Start watching this issue

                Created:
                Updated:
                Resolved: