Bug 589268

Summary:	server log ERROR SQLGrammarException when subscribing new resource to a repository
Product:	[Other] RHQ Project	Reporter:	Simeon Pinder <spinder>
Component:	Content	Assignee:	Simeon Pinder <spinder>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Corey Welton <cwelton>
Severity:	medium	Docs Contact:
Priority:	low
Version:	unspecified	CC:	skondkar
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:	2.4	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2010-08-12 16:47:18 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	586123

Description Simeon Pinder 2010-05-05 17:29:00 UTC

Description of problem:
When trying to subscribe a new resource to an existing repo, sql grammar exceptions are thrown in the server log but gui proceeds.  Note there are no content sources yet associated with the repository.

############
11:19:22,693 INFO  [EnhancedSchedulerImpl] Scheduled cron job [15cb1:syncRepo] to fire next at [Thu May 06 03:00:00 GMT-05:00 2010] with the cron string [0 0 3 * * ?].
11:19:26,363 WARN  [DefaultActionRendererBase] Must set an id for the default action source
11:19:56,906 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42883
11:19:56,906 ERROR [JDBCExceptionReporter] ERROR: function pg_catalog.like_escape(bytea, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 402
11:19:56,908 ERROR [PagedListDataModel] PageControlView[RepoUnsubscriptionsUIBean]: Received error[javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query], resetting to PageControl[page=0, size=15]
11:19:56,911 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42883
11:19:56,911 ERROR [JDBCExceptionReporter] ERROR: function pg_catalog.like_escape(bytea, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 402
11:19:56,912 ERROR [PagedListDataModel] Could not retrieve collection for PageControlView[RepoUnsubscriptionsUIBean]
javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:63)
	at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83)
	at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
	at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:240)
	at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:210)
	at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:84)
	at $Proxy303.findAvailableResourcesForRepo(Unknown Source)
	at org.rhq.enterprise.gui.content.RepoUnsubscriptionsUIBean$RepoUnsubscriptionsDataModel.fetchDataForPage(RepoUnsubscriptionsUIBean.java:133)
	at org.rhq.enterprise.gui.common.paging.ResourceNameDisambiguatingPagedListDataModel.fetchPage(ResourceNameDisambiguatingPagedListDataModel.java:68)
	at org.rhq.enterprise.gui.common.paging.PagedListDataModel.fetchPageGuarded(PagedListDataModel.java:353)
	at org.rhq.enterprise.gui.common.paging.PagedListDataModel.getDataPage(PagedListDataModel.java:263)
	at org.rhq.enterprise.gui.common.paging.PagedListDataModel.getPage(PagedListDataModel.java:123)
	at org.rhq.enterprise.gui.common.paging.PagedListDataModel.getRowCount(PagedListDataModel.java:110)
	at org.ajax4jsf.model.SequenceDataModel.getRowCount(SequenceDataModel.java:139)
	at org.ajax4jsf.component.UIDataAdaptorBase.getRowCount(UIDataAdaptorBase.java:248)
	at org.richfaces.component.UIDatascroller.getRowCount(UIDatascroller.java:362)
	at org.richfaces.component.UIDatascroller.setupFirstRowValue(UIDatascroller.java:452)
	at org.richfaces.component.RenderPhaseDataScrollerVisitor.afterRoot(RenderPhaseDataScrollerVisitor.java:191)
	at org.richfaces.event.RenderPhaseComponentListener.beforePhase(RenderPhaseComponentListener.java:73)
	at org.ajax4jsf.component.AjaxViewRoot.processPhaseListeners(AjaxViewRoot.java:188)
	at org.ajax4jsf.component.AjaxViewRoot.encodeBegin(AjaxViewRoot.java:510)
	at javax.faces.component.UIComponent.encodeAll(UIComponent.java:928)
	at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
	at org.rhq.enterprise.gui.common.framework.FaceletRedirectionViewHandler.renderView(FaceletRedirectionViewHandler.java:64)
	at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
	at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
	at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
	at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:530)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.rhq.enterprise.gui.legacy.WebUserTrackingFilter.doFilter(WebUserTrackingFilter.java:50)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.rhq.enterprise.gui.legacy.AuthenticationFilter.doFilter(AuthenticationFilter.java:129)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
	at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:38)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
	at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
	at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
	at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
	at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.debug.hot.HotDeployFilter.doFilter(HotDeployFilter.java:51)
	at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
	at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.rhq.helpers.rtfilter.filter.RtFilter.doFilter(RtFilter.java:124)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
	at java.lang.Thread.run(Thread.java:619)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
	at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:99)
	at org.rhq.enterprise.server.resource.ResourceManagerBean.findAvailableResourcesForRepo(ResourceManagerBean.java:1509)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
	at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
	at org.rhq.enterprise.server.common.TransactionInterruptInterceptor.addCheckedActionToTransactionManager(TransactionInterruptInterceptor.java:77)
	at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:118)
	at org.rhq.enterprise.server.authz.RequiredPermissionsInterceptor.checkRequiredPermissions(RequiredPermissionsInterceptor.java:156)
	at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:118)
	at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
	at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
	at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
	... 92 more
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:2216)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
	at org.hibernate.loader.Loader.list(Loader.java:2099)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
	at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:80)
	... 116 more
Caused by: org.postgresql.util.PSQLException: ERROR: function pg_catalog.like_escape(bytea, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 402
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
	at sun.reflect.GeneratedMethodAccessor92.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:455)
	at $Proxy49.executeQuery(Unknown Source)
	at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)
	at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
	at org.hibernate.loader.Loader.doQuery(Loader.java:674)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
	at org.hibernate.loader.Loader.doList(Loader.java:2213)
	... 124 more


############

Version-Release number of selected component (if applicable):

Jon build 127 from http://hudson-qe.rhq.rdu.redhat.com:8080/job/jon/
should map to master build 280.

How reproducible:

Every time.

Steps to Reproduce:
1. Create new content source
2. Create new repo but do not associate it to any existing content sources.
3. With the new repo selected, hit the 'Subscribe' button from the "Resources Subscribed To This Repository" region and check the server log.
  
Actual results:
Exceptions thrown in the log.

Expected results:
No Exceptions thrown in the log.

Additional info:

Comment 1 Simeon Pinder 2010-05-05 17:35:20 UTC

Additionally, when actually trying to add a resource, if you search with an empty field then i) no results are returned and ii) the same exception is echoed out in the server log with each search.

You are however able to subscribe the resource appropriately.

Comment 2 Simeon Pinder 2010-05-11 19:02:24 UTC

Ick. This was caused by some wierd hibernate behavior.  Simply changing the order of the query and not the definition means the wierd sql errors went away and default searches now work properly.

Related but not a duplicate of 588568.

This has been fixed in successful build of master >= 292
with commit hash: b75aca89d96e1421a26003dca674b290a849365f

Comment 3 Sunil Kondkar 2010-05-18 10:46:51 UTC

Verified on Jon build# 152 (Revision: 10620)

No exception is observed in server log after hitting the 'Subscribe' button from the "Resources Subscribed To This Repository" region.

Also observed that after doing a search for a resource, no exception is thrown in server log.

Comment 4 Corey Welton 2010-08-12 16:47:18 UTC

Mass-closure of verified bugs against JON.