Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-51494

When a Site has more than 1000 users and Organizations as members, an Oracle SQLGrammarException error is thrown

    Details

      Description

      When a Site has more than 1000 Organizations as members, an Oracle SQLGrammarException error is thrown

      Steps
      1. Start the portal using an Oracle database
      2. Generate 1050 Organizations
      3. Add all Organizations as a member of a site
      4. Go to Control Panel > Sites

      Errors

      23:49:21,975 ERROR [http-bio-8080-exec-1][IncludeTag:129] Current URL /group/control_panel/manage?p_p_auth=Obwq2UmB&p_p_id=174&p_p_lifecycle=0&p_p_state=maximized&p_p_mode=view&doAsGroupId=10748&refererPlid=10757&controlPanelCategory=current_site.users&_174_struts_action=%2Fsite_memberships_admin%2Fedit_site_assignments&_174_tabs2=current&_174_redirect=&_174_showBackURL=true&_174_tabs1=users generates exception: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
      java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000_ [Sanitized]
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
              at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
              at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
              at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
              at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
              at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
              at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
              at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
              at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
              at org.hibernate.loader.Loader.doQuery(Loader.java:802)
              at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
              at org.hibernate.loader.Loader.doList(Loader.java:2542)
              at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
      23:57:22,830 ERROR [http-bio-8080-exec-7][JDBCExceptionReporter:82] ORA-01795: maximum number of expressions in a list is 1000_ [Sanitized]
      

      Result
      SQLSyntaxErrorException is thrown.
      Expected
      No SQL exceptions are thrown.

      Notes:
      This happens because Oracle does not allow more than 1000 parameters to be bound to an IN clause.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              lu.liu Lu Liu
              Reporter:
              preston.crary Preston Crary
              Participants of an Issue:
              Recent user:
              Esther Sanz
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                6 years, 37 weeks ago

                  Packages

                  Version Package
                  6.1.X EE
                  6.2.3 CE GA4
                  6.2.X EE
                  7.0.0 M3