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

Message Board "My Subscriptions" tab fails to load when using Oracle database

    Details

      Description

      Additional Info
      java.sql.SQLSyntaxErrorException is also thrown in the logs:

      Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression_ 

      Steps to Reproduce

      1. Setup Liferay with Oracle database
      2. Sign in as Admin
      3. Add a Message Board to a page
      4. Click on the My Subscriptions tab

      Expected Results:
      User is taken to My Subscriptions and with no error/issue

      Actual Results:
      UI shows a portlet error: "Message Boards is temporarily unavailable. "
      Log shows stack trace with error: java.sql.SQLSyntaxErrorException

      Snippet of Stack Trace (Condensed)

      021-02-09 18:55:16.993 ERROR [http-nio-8080-exec-5][JDBCExceptionReporter:234] ORA-00979: not a GROUP BY expression_ [Sanitized]
      2021-02-09 18:55:16.998 ERROR [http-nio-8080-exec-5][PortletRequestDispatcherImpl:304] Unable to dispatch request: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
      2021-02-09 18:55:17.004 ERROR [http-nio-8080-exec-5][PortletServlet:119] javax.portlet.PortletException: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
      javax.portlet.PortletException: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          at com.liferay.portlet.internal.PortletRequestDispatcherImpl.dispatch(PortletRequestDispatcherImpl.java:307)
          at com.liferay.portlet.internal.PortletRequestDispatcherImpl.include(PortletRequestDispatcherImpl.java:123)
          at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.include(MVCPortlet.java:571)
          at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.include(MVCPortlet.java:587)
          at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.doDispatch(MVCPortlet.java:461)
          ...
          at org.apache.jsp.html.portal.render_005fportlet_jsp._jspService(render_005fportlet_jsp.java:1432)
          ...
          at com.liferay.taglib.portletext.RuntimeTag.doTag(RuntimeTag.java:349)
          at com.liferay.fragment.internal.renderer.FragmentPortletRendererImpl.renderPortlet(FragmentPortletRendererImpl.java:70)
          at com.liferay.fragment.entry.processor.portlet.PortletFragmentEntryProcessor._renderWidgetHTML(PortletFragmentEntryProcessor.java:401)
          at com.liferay.fragment.entry.processor.portlet.PortletFragmentEntryProcessor.processFragmentEntryLinkHTML(PortletFragmentEntryProcessor.java:154)
          at com.liferay.fragment.internal.processor.FragmentEntryProcessorRegistryImpl.processFragmentEntryLinkHTML(FragmentEntryProcessorRegistryImpl.java:159)
          at com.liferay.fragment.internal.renderer.FragmentEntryFragmentRenderer._renderFragmentEntryLink(FragmentEntryFragmentRenderer.java:314)
          at com.liferay.fragment.internal.renderer.FragmentEntryFragmentRenderer.render(FragmentEntryFragmentRenderer.java:100)
          at com.liferay.fragment.internal.renderer.FragmentRendererControllerImpl.render(FragmentRendererControllerImpl.java:111)
          at org.apache.jsp.render_005flayout_005fstructure.render_005flayout_005fstructure_jsp._jspService(render_005flayout_005fstructure_jsp.java:725)
          ...
          at com.liferay.portal.internal.servlet.MainServlet.doGet(MainServlet.java:207)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
          at com.liferay.portal.internal.servlet.MainServlet.service(MainServlet.java:643)
          ...
      Caused by: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:440)
          at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
          at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
          at com.liferay.portal.osgi.web.servlet.jsp.compiler.internal.JspServlet.service(JspServlet.java:368)
          at com.liferay.portal.osgi.web.servlet.jsp.compiler.internal.JspServlet.service(JspServlet.java:380)
          at org.eclipse.equinox.http.servlet.internal.registration.EndpointRegistration.service(EndpointRegistration.java:153)
          at org.eclipse.equinox.http.servlet.internal.servlet.ResponseStateHandler.processRequest(ResponseStateHandler.java:62)
          at org.eclipse.equinox.http.servlet.internal.context.DispatchTargets.doDispatch(DispatchTargets.java:120)
          at org.eclipse.equinox.http.servlet.internal.servlet.RequestDispatcherAdaptor.include(RequestDispatcherAdaptor.java:48)
          at com.liferay.portlet.internal.PortletRequestDispatcherImpl.dispatch(PortletRequestDispatcherImpl.java:291)
          ... 251 more
      Caused by: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.doFindC_ByS_G_U_P(MBCategoryFinderImpl.java:579)
          at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.filterFindC_ByS_G_U_P(MBCategoryFinderImpl.java:142)
          at com.liferay.message.boards.service.impl.MBCategoryServiceImpl.getSubscribedCategories(MBCategoryServiceImpl.java:442)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:498)
          at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:50)
          at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:69)
          at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
          at com.liferay.portal.kernel.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:64)
          at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
          at com.liferay.portal.spring.aop.AopInvocationHandler.invoke(AopInvocationHandler.java:49)
          at com.sun.proxy.$Proxy2069.getSubscribedCategories(Unknown Source)
          at com.liferay.message.boards.service.MBCategoryServiceUtil.getSubscribedCategories(MBCategoryServiceUtil.java:298)
          at org.apache.jsp.message_005fboards.view_jsp._jspService(view_jsp.java:840)
          at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
          at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
          ... 261 more
      Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:39)
          at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:188)
          at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:168)
          at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:61)
          at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.doFindC_ByS_G_U_P(MBCategoryFinderImpl.java:550)
          ... 279 more
      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
          at org.hibernate.loader.Loader.doList(Loader.java:2545)
          at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
          at org.hibernate.loader.Loader.list(Loader.java:2271)
          at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
          at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
          at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
          at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
          at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:176)
          ... 282 more
      Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression_ [Sanitized]
          at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
          at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
          at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
          at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
          at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
          at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
          at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
          at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
          at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
          at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
          at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
          at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
          at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
          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)
          ... 289 more
      Caused by: Error : 979, Position : 50, Sql = SELECT MBCategory.categoryId AS categoryId884_0_, MBCategory.mvccVersion AS mvccVers2_884_0_, MBCategory.ctCollectionId AS ctCollec3_884_0_, MBCategory.uuid_ AS uuid4_884_0_, MBCategory.groupId AS groupId884_0_, MBCategory.companyId AS companyId884_0_, MBCategory.userId AS userId884_0_, MBCategory.userName AS userName884_0_, MBCategory.createDate AS createDate884_0_, MBCategory.modifiedDate AS modifie10_884_0_, MBCategory.parentCategoryId AS parentC11_884_0_, MBCategory.name AS name884_0_, MBCategory.description AS descrip13_884_0_, MBCategory.displayStyle AS display14_884_0_, MBCategory.lastPublishDate AS lastPub15_884_0_, MBCategory.status AS status884_0_, MBCategory.statusByUserId AS statusB17_884_0_, MBCategory.statusByUserName AS statusB18_884_0_, MBCategory.statusDate AS statusDate884_0_ FROM MBCategory INNER JOIN Subscription ON (Subscription.companyId = MBCategory.companyId) AND (Subscription.classNameId = :1 ) AND (Subscription.classPK = MBCategory.categoryId) LEFT JOIN MBMessage ON MBMessage.categoryId = MBCategory.categoryId AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) WHERE (MBCategory.groupId = :2 ) AND (MBCategory.parentCategoryId = 0) AND (Subscription.userId = :3 ) AND (MBCategory.status != :4 ) AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) GROUP BY MBCategory.ctCollectionId, MBCategory.categoryId ORDER BY (CASE WHEN MAX(MBMessage.modifiedDate) IS NULL THEN MBCategory.modifiedDate ELSE MAX(MBMessage.modifiedDate) END) DESC, OriginalSql = SELECT MBCategory.categoryId AS categoryId884_0_, MBCategory.mvccVersion AS mvccVers2_884_0_, MBCategory.ctCollectionId AS ctCollec3_884_0_, MBCategory.uuid_ AS uuid4_884_0_, MBCategory.groupId AS groupId884_0_, MBCategory.companyId AS companyId884_0_, MBCategory.userId AS userId884_0_, MBCategory.userName AS userName884_0_, MBCategory.createDate AS createDate884_0_, MBCategory.modifiedDate AS modifie10_884_0_, MBCategory.parentCategoryId AS parentC11_884_0_, MBCategory.name AS name884_0_, MBCategory.description AS descrip13_884_0_, MBCategory.displayStyle AS display14_884_0_, MBCategory.lastPublishDate AS lastPub15_884_0_, MBCategory.status AS status884_0_, MBCategory.statusByUserId AS statusB17_884_0_, MBCategory.statusByUserName AS statusB18_884_0_, MBCategory.statusDate AS statusDate884_0_ FROM MBCategory INNER JOIN Subscription ON (Subscription.companyId = MBCategory.companyId) AND (Subscription.classNameId = ?) AND (Subscription.classPK = MBCategory.categoryId) LEFT JOIN MBMessage ON MBMessage.categoryId = MBCategory.categoryId AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) WHERE (MBCategory.groupId = ?) AND (MBCategory.parentCategoryId = 0) AND (Subscription.userId = ?) AND (MBCategory.status != ?) AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) GROUP BY MBCategory.ctCollectionId, MBCategory.categoryId ORDER BY (CASE WHEN MAX(MBMessage.modifiedDate) IS NULL THEN MBCategory.modifiedDate ELSE MAX(MBMessage.modifiedDate) END) DESC, Error Msg = ORA-00979: not a GROUP BY expression_ [Sanitized]
          at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
          ... 310 more

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              beck.liu Beck Liu
              Reporter:
              eric.yan Eric Yan
              Participants of an Issue:
              Recent user:
              Jayda Wu
              Engineering Assignee:
              Eric Yan
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                41 weeks, 1 day ago

                  Packages

                  Version Package
                  7.3.10 DXP FP1
                  7.3.10.1 DXP SP1
                  7.3.6 CE GA7
                  7.3.X
                  7.4.13 DXP GA1
                  Master