Re: [eclipselink-users] Communication failure detected when attempting t

[christopher delahunt <christopher.delahunt@xxxxxxxxxx>]

Hello Samba,

I believe the fetch-size query hint has to do with scrollable cursors 
paging size, and so is not likely to have an effect on regular queries. 

Using the setFirstResult and setMaxResults options -
It depends on how your database runs the query and returns results, but 
I'd recommend using ordering on the query to ensure results are returned 
in a consistent order. 
Some database platforms take advantage of pagination filters such as the 
Oracle platform using rownum to filter setFirstResult and 
setMaxResults.  If the platform does not
have pagination support, it will use JDBC to filter out the maxrows and 
jump to the first result in the returned data.

LIMIT is used in the MySQL platform for pagination, which is supported 
in the nightly EclipseLink (I am not sure when it was added).  So if you 
are using the MySQL database platform and
set the setFirstResult and setMaxResults values, the generated SQL will 
use LIMIT to filter results.

Best Regards,
Chris



Samba wrote:
> Hi Chris,
>    I think this has to do with query optimisation as the query is 
> indeed taking around 10 seconds  to fetch the results when run from an 
> SQL Editor.
>   
>    Actually I wanted to limit the results to a single record and that 
> was working fine with in a second but in JPA I think using te 
> eclipselink query hint
>    "eclipselink.jdbc.fetch-size"  and setting that to 1 did not help.
>  
>   Now I tried using
>  
>      query.setFirstResult(0);
>      query.setMaxResults(1);
>      query.getSingleResult();
>  
> and I got the expected result pretty quickly around 30 ms.
>  
>  
> But I have a doubt whether the above method gives me consistent 
> results in the same way as database LIMIT keyword does!
> Can you give some suggestions here?
>  
> Thanks ad Regards,
> Samba
>  
>  
>  
> On Mon, Mar 30, 2009 at 6:50 PM, christopher delahunt 
> <christopher.delahunt@xxxxxxxxxx 
> <mailto:christopher.delahunt@xxxxxxxxxx>> wrote:
>
>     Hello Samba,
>
>     The problem is that the session bean method is taking longer than
>     the time allowed for the transaction, causing the transaction to
>     timeout.  It is an EJB problem, not necessarily JPA, its just that
>     JPA is configured to use the connection associated to the
>     transaction (JTA) for reads, and so reports the exception before
>     the container does. You can either break up the transaction into
>     smaller chunks, or increase the timeout value for transactions -
>     I'm not that familiar with WebLogic settings so I can't comment on
>     how to change the timeout value.
>     Best Regards,
>     Chris
>
>     Samba wrote:
>
>         Hi I'm using eclipselink 1.0.2 from Jdeveloper 11.1.0.1 and
>         MySQL as database.
>
>            I'm running a java client to test the written functionality
>         in a Session Bean deployed to weblogic server (default in
>         jdeveloper).
>              and I'm getting the error
>          [code]
>                           /Communication failure detected when
>         attempting to perform read query outside of a transaction.
>         Attempting to retry query.
>                              [EL Warning]: 2009.03.28
>         22:02:29.140--UnitOfWork(8724084)--Exception
>         [EclipseLink-4002] (Eclipse Persistence Services - 1.0.2
>         (Build 20081024)):
>         org.eclipse.persistence.exceptions.DatabaseException
>         Internal Exception: java.sql.SQLException: Transaction
>         BEA1-00032CA83BB89E472B1E not active anymore. tx status =
>         Rolled back.
>         [Reason=weblogic.transaction.internal.TimedOutException:
>         Transaction timed out after 31 seconds
>         BEA1-00032CA83BB89E472B1E]
>         Error Code: 0
>         Call: SQLCall(SELECT latitude, longitude, zipcode, city,
>         country_code, ip_start, region_code FROM ip_group_city WHERE
>         (ip_start <= ?) ORDER BY ip_start DESC)
>         Query: ReadAllQuery(com.gamyam.product.geoip.model.IPGroupCity)
>
>
>         Here is the stacktrace:
>
>           javax.ejb.EJBException: EJB Exception: ; nested exception is:
>            Exception [EclipseLink-23009] (Eclipse Persistence Services
>         - 1.0.2 (Build 20081024)):
>         org.eclipse.persistence.exceptions.TransactionException
>         Exception Description: Error marking externally managed
>         transaction for rollback
>         Internal Exception: java.lang.IllegalStateException: Cannot
>         mark the transaction for rollback.
>         xid=BEA1-00032CA83BB89E472B1E, status=Rolled back.
>         [Reason=weblogic.transaction.internal.TimedOutException:
>         Transaction timed out after 31 seconds
>         BEA1-00032CA83BB89E472B1E]
>            at
>         weblogic.ejb.container.internal.RemoteBusinessIntfProxy.unwrapRemoteException(RemoteBusinessIntfProxy.java:105)
>            at
>         weblogic.ejb.container.internal.RemoteBusinessIntfProxy.invoke(RemoteBusinessIntfProxy.java:87)
>            at $Proxy10.fetchLocation(Unknown Source)
>            at
>         com.gamyam.product.geoip.model.GeoIPServiceClient.main(GeoIPServiceClient.java:60)
>         Caused by: Exception [EclipseLink-23009] (Eclipse Persistence
>         Services - 1.0.2 (Build 20081024)):
>         org.eclipse.persistence.exceptions.TransactionException
>         Exception Description: Error marking externally managed
>         transaction for rollback
>         Internal Exception: java.lang.IllegalStateException: Cannot
>         mark the transaction for rollback.
>         xid=BEA1-00032CA83BB89E472B1E, status=Rolled back.
>         [Reason=weblogic.transaction.internal.TimedOutException:
>         Transaction timed out after 31 seconds
>         BEA1-00032CA83BB89E472B1E]
>            at
>         org.eclipse.persistence.exceptions.TransactionException.errorMarkingTransactionForRollback(TransactionException.java:103)
>            at
>         org.eclipse.persistence.transaction.AbstractTransactionController.markTransactionForRollback(AbstractTransactionController.java:198)
>            at
>         org.eclipse.persistence.internal.jpa.transaction.JTATransactionWrapper.setRollbackOnlyInternal(JTATransactionWrapper.java:83)
>            at
>         org.eclipse.persistence.internal.jpa.EntityManagerImpl.setRollbackOnly(EntityManagerImpl.java:946)
>            at
>         org.eclipse.persistence.internal.jpa.EJBQueryImpl.setRollbackOnly(EJBQueryImpl.java:941)
>            at
>         org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:567)
>            at
>         com.gamyam.product.geoip.model.GeoIPServiceProvider.fetchLocation(GeoIPServiceProvider.java:159)
>            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
>         com.bea.core.repackaged.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
>            at
>         com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:126)
>            at
>         com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:114)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
>            at
>         com.bea.core.repackaged.springframework.jee.spi.MethodInvocationVisitorImpl.visit(MethodInvocationVisitorImpl.java:15)
>            at
>         weblogic.ejb.container.injection.EnvironmentInterceptorCallbackImpl.callback(EnvironmentInterceptorCallbackImpl.java:54)
>            at
>         com.bea.core.repackaged.springframework.jee.spi.EnvironmentInterceptor.invoke(EnvironmentInterceptor.java:30)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
>            at
>         com.bea.core.repackaged.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
>            at
>         com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:126)
>            at
>         com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:114)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
>            at
>         com.bea.core.repackaged.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
>            at $Proxy90.fetchLocation(Unknown Source)
>            at
>         com.gamyam.product.geoip.model.GeoIPService_2uw13f_GeoIPServiceImpl.fetchLocation(GeoIPService_2uw13f_GeoIPServiceImpl.java:610)
>            at
>         com.gamyam.product.geoip.model.GeoIPService_2uw13f_GeoIPServiceImpl_WLSkel.invoke(Unknown
>         Source)
>            at
>         weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:589)
>            at
>         weblogic.rmi.cluster.ClusterableServerRef.invoke(ClusterableServerRef.java:230)
>            at
>         weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:477)
>            at
>         weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
>            at weblogic.security.service.SecurityManager.runAs(Unknown
>         Source)
>            at
>         weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:473)
>            at
>         weblogic.rmi.internal.wls.WLSExecuteRequest.run(WLSExecuteRequest.java:118)
>            at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
>            at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
>         Caused by: java.lang.IllegalStateException: Cannot mark the
>         transaction for rollback. xid=BEA1-00032CA83BB89E472B1E,
>         status=Rolled back.
>         [Reason=weblogic.transaction.internal.TimedOutException:
>         Transaction timed out after 31 seconds
>         BEA1-00032CA83BB89E472B1E]
>            at
>         weblogic.transaction.internal.TransactionImpl.throwIllegalStateException(TransactionImpl.java:1829)
>            at
>         weblogic.transaction.internal.TransactionImpl.setRollbackOnly(TransactionImpl.java:549)
>            at
>         weblogic.transaction.internal.TransactionManagerImpl.setRollbackOnly(TransactionManagerImpl.java:316)
>            at
>         weblogic.transaction.internal.TransactionManagerImpl.setRollbackOnly(TransactionManagerImpl.java:309)
>            at
>         org.eclipse.persistence.transaction.JTATransactionController.markTransactionForRollback_impl(JTATransactionController.java:145)
>            at
>         org.eclipse.persistence.transaction.AbstractTransactionController.markTransactionForRollback(AbstractTransactionController.java:196)
>            ... 37 more
>         Process exited with exit code 0.
>
>         Here is my fetchLocation() method:
>            public IPGroupCity fetchLocation(String ip){
>                //String sql="SELECT * FROM `ip_group_city` where
>         `ip_start` <= ((INET_ATON(?1))/256) order by ip_start desc
>         limit 1";
>                Query query = em.createNamedQuery("findLocationForIP");
>                int ipNumber = GeoIPUtils.convertIPStringToNumber(ip);
>                query.setParameter("ipNumber",ipNumber);
>                query.setHint("eclipselink.jdbc.fetch-size",1);
>                IPGroupCity location= (IPGroupCity)
>         query.getSingleResult();
>                return location;
>            }/
>
>         [/code]
>
>         Can you help me find out why eclipselink could not fetch the
>         result of my query?
>         and what I need to to do to fix this...
>
>         Thanks and Regards,
>         Samba
>
>         ------------------------------------------------------------------------
>
>         _______________________________________________
>         eclipselink-users mailing list
>         eclipselink-users@xxxxxxxxxxx
>         <mailto:eclipselink-users@xxxxxxxxxxx>
>         https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>          
>
>     _______________________________________________
>     eclipselink-users mailing list
>     eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>     https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>