Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-24722

More than 1000 topics in Forums causes Oracle failure

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.9.0
    • Fix Version/s: 2.9.0
    • Component/s: Messages Tool
    • Labels:
      None
    • 2.9 Status:
      Resolved
    • Previous Issue Keys:
      MSGCNTR-636

      Description

      We're seeing an ORA-01795, which can be caused by any query that has IN (.....) list, in production.

      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:2220)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108)
      at org.hibernate.loader.Loader.list(Loader.java:2103)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
      at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:340)
      at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1122)
      at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      at org.sakaiproject.component.app.messageforums.PermissionLevelManagerImpl$4.doInHibernate(PermissionLevelManagerImpl.java:677)
      at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
      ... 101 more
      Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

      This can be traced back to line 677 of org.sakaiproject.component.app.messageforums.PermissionLevelManagerImpl

      This query needs to check for more than 1000 taskIds, and if so, break the query into several IN () statements, for example: ... IN (x,y,z) OR IN (x1, y1, z1) OR IN (x2, y2, z2) ...

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  baholladay Bryan Holladay
                  Reporter:
                  arwhyte Anthony Whyte
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  0 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration