Announcement Announcement Module
Collapse
No announcement yet.
JDBC Connection issues post 3.2 upgrade Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC Connection issues post 3.2 upgrade

    Hi,

    I recently updated the dependencies of my project and have started facing issues. The updates were:

    Spring 3.1.0 to Spring 3.2.2
    Hibernate 3.6 to 4.1.10 - Removed HibernateDaoSupport to use pure Hibernate API with the sessionFactory.getCurrentSession()
    Commons-DBCP to tomcat7-dbcp & tomcat7-jdbc 7.0.37

    Since then, I have been getting the exception:

    Code:
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 52,538,869 milliseconds ago.  The last packet sent successfully to the server was 52,538,871 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3871)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    	at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:601)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    	at sun.proxy.$Proxy58.executeQuery(Unknown Source)
    	at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
            ... other hibernate traces.. 
    	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1621)
    	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
    	at com.trelta.commons.utils.dao.CommonDAOImpl.getRecords(CommonDAOImpl.java:222)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:601)
    	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    	at sun.proxy.$Proxy37.getRecords(Unknown Source)
    	at com.trelta.accountmanagement.common.service.CommonService.getRecords_aroundBody20(CommonService.java:272)
    	at com.trelta.accountmanagement.common.service.CommonService$AjcClosure21.run(CommonService.java:1)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96cproceed(AbstractTransactionAspect.aj:59)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect$AbstractTransactionAspect$1.proceedWithInvocation(AbstractTransactionAspect.aj:65)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96c(AbstractTransactionAspect.aj:63)
    	at com.trelta.accountmanagement.common.service.CommonService.getRecords(CommonService.java:268)
    	at com.trelta.accountmanagement.usermanagement.service.UserService.getUserMpg_aroundBody20(UserService.java:667)
    	at com.trelta.accountmanagement.usermanagement.service.UserService$AjcClosure21.run(UserService.java:1)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96cproceed(AbstractTransactionAspect.aj:59)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect$AbstractTransactionAspect$1.proceedWithInvocation(AbstractTransactionAspect.aj:65)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96c(AbstractTransactionAspect.aj:63)
    	at com.trelta.accountmanagement.usermanagement.service.UserService.getUserMpg(UserService.java:651)
    	at com.trelta.accountmanagement.usermanagement.service.UserService.getInfo_aroundBody0(UserService.java:106)
    	at com.trelta.accountmanagement.usermanagement.service.UserService$AjcClosure1.run(UserService.java:1)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96cproceed(AbstractTransactionAspect.aj:59)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect$AbstractTransactionAspect$1.proceedWithInvocation(AbstractTransactionAspect.aj:65)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    	at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$around$org_springframework_transaction_aspectj_AbstractTransactionAspect$1$2a73e96c(AbstractTransactionAspect.aj:63)
    	at com.trelta.accountmanagement.usermanagement.service.UserService.getInfo(UserService.java:103)
    	at com.trelta.accountmanagement.usermanagement.controller.UserController.get(UserController.java:99)
    	at com.trelta.accountmanagement.usermanagement.controller.UserController$$FastClassByCGLIB$$4dabf246.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:627)
    	at com.trelta.accountmanagement.usermanagement.controller.UserController$$EnhancerByCGLIB$$888e8408.get(<generated>)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:601)
    	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    	... 56 more
    Caused by: java.net.SocketException: Connection timed out
    This happens when I leave the server running idle overnight. In the morning, when I make the first hit, when the first query is executed, the above exception occurs. Googling seems to show that there may be some connections left open, which may be causing the problem. But since spring handles my transactions (I'm using the @Transactional annotation), I thought I should raise this question here.

    Currently my DataSource is configured

    Code:
    <bean id="myDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    		<property name="driverClassName" value="${jdbc.driverClassName}" />
    		<property name="url" value="${jdbc.url}" />
    		<property name="username" value="${jdbc.username}" />
    		<property name="password" value="${jdbc.password}" />
    		<property name="initialSize" value="${jdbc.initialSize}" />
    		<property name="maxActive" value="${jdbc.maxActive}" />
    		<property name="defaultAutoCommit" value="false" />
    </bean>
    A possible work around suggested on the net would be to add the following 2 properties to the above configuration:
    Code:
    <property name="removeAbandoned" value="${jdbc.abandon}" />
    <property name="removeAbandonedTimeout" value="${jdbc.abandonTimeout}" />
    But these again seem to suggest a leaked connection.

    Another solution was to set testOnBorrow and validationQuery properties, but the spring documentation says that this might be inefficient and it's unclear how it will solve the problem.

    I am using Tomcat7. Restarting tomcat fixes the problem temporarily, but resurfaces the next morning.

    So what do I do to fix the exception? Why does it happen?

    Thanks
    Varun

  • #2
    A connection pool opens connections and keeps them open. MySQL is known for issues with this as the MySQL database kills open and idle connections. So it has not to do with leaked connections but the fact that you use a connection pool and mysql killing of those connections on the server side.

    Comment


    • #3
      Originally posted by Marten Deinum View Post
      A connection pool opens connections and keeps them open. MySQL is known for issues with this as the MySQL database kills open and idle connections. So it has not to do with leaked connections but the fact that you use a connection pool and mysql killing of those connections on the server side.
      Hi Marten,

      This problem started post the upgrade. I didn't face these issues earlier. The database hasn't changed, so MySQL would've troubled me in the same way earlier too, but it did not.

      What do you suggest I should do to get around the problem? In my earlier post, I said I had found 2 possible solutions. One was the "abandon" method and the other was the "testOnBorrow" method. Which should I use or is there some other solution to this?

      I look to this forum for the solution since tomcat-dbcp was also developed at Springsource, so you guys know best.

      Comment


      • #4
        You changed more then just spring. But it is a known issue with MySQL (ran into it myself also). We used the autoreconnect option, but at times you still might run into this issue.

        Which to use depends on your usecase, testing before using the connection could be benefitial this might reinitialize the connection or kill it when it is broken. Use a simple query to test then there shouldn't be too much of a delay...

        Comment


        • #5
          Thanks Marten,

          As per my findings, removeAbandon and removeAbandonTimeout did not work. I'll try the testOnBorrow and validationQuery options next and lets see what happens. I'll keep things posted here for others benefit.

          Comment


          • #6
            The following article from Daniel Mikusa of SpringSource gives exact explanations of what to do to fix this problem

            http://www.tomcatexpert.com/blog/201...ool-production

            Comment

            Working...
            X