Spring, MySQL and Hibernate: Database error if the system is not used within 8 hours

Question

I am working on a Spring, MySQL and Hibernate project and If the project is not used within 8 hours I get the following error. Can someone please tell me why. It looks like if I reboot the server everything works great until night time or if no one is using the system mid day for a few hours. can someone please help me out I just found this issue in a few of my projects so I must be making the same mistake in a number of projects.. please help

Here is a example of my java code am I missing something:

public void saveVisit(VisitModel visit) throws Exception {
        LOGGER.debug("[" + visit.toString() + "]");
        sessionFactory.getCurrentSession().saveOrUpdate(visit);
    }


    @SuppressWarnings("unchecked")
    public School_lookupModel getSchoolInformation(String schoolCode)
    {
        return (School_lookupModel) sessionFactory.getCurrentSession()
        .createCriteria(School_lookupModel.class)
        .add( Restrictions.like("school", schoolCode))
        .uniqueResult();
    }

Here is the console output of the errors:

2012-09-06 08:00:07,088 [http-8080-2] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01
2012-09-06 08:00:07,088 [http-8080-2] ERROR org.hibernate.util.JDBCExceptionReporter - Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2294)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
    at org.hibernate.loader.Loader.list(Loader.java:2167)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at org.uftwf.schoolvisit.dao.SchoolVisitDAOImpl.getSelectableReporters(SchoolVisitDAOImpl.java:56)
    at org.uftwf.schoolvisit.service.SchoolVisitServiceImpl.getSelectableReporters(SchoolVisitServiceImpl.java:130)
    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:616)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy36.getSelectableReporters(Unknown Source)
    at org.uftwf.schoolvisit.controller.swf.FlowActions.initializeSelectableReports(FlowActions.java:57)
    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:616)
    at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
    at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:84)
    at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
    at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
    at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:97)
    at org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)
    at org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)
    at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)
    at org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)
    at org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)
    at org.springframework.webflow.engine.ActionList.execute(ActionList.java:155)
    at org.springframework.webflow.engine.Flow.start(Flow.java:534)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:366)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:222)
    at org.springframework.webflow.executor.FlowExecutorImpl.launchExecution(FlowExecutorImpl.java:140)
    at org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:193)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:679)


** END NESTED EXCEPTION **



Last packet sent to the server was 13 ms ago.
2012-09-06 08:00:07,089 [http-8080-2] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
2012-09-06 08:00:07,089 [http-8080-2] ERROR org.hibernate.util.JDBCExceptionReporter - Already closed.
2012-09-06 08:00:07,089 [http-8080-2] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
2012-09-06 08:00:07,089 [http-8080-2] ERROR org.hibernate.util.JDBCExceptionReporter - Already closed.
Sep 6, 2012 8:00:07 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet spring threw exception
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2294)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
    at org.hibernate.loader.Loader.list(Loader.java:2167)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at org.uftwf.schoolvisit.dao.SchoolVisitDAOImpl.getSelectableReporters(SchoolVisitDAOImpl.java:56)
    at org.uftwf.schoolvisit.service.SchoolVisitServiceImpl.getSelectableReporters(SchoolVisitServiceImpl.java:130)
    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:616)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy36.getSelectableReporters(Unknown Source)
    at org.uftwf.schoolvisit.controller.swf.FlowActions.initializeSelectableReports(FlowActions.java:57)
    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:616)
    at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
    at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:84)
    at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
    at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
    at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:97)
    at org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)
    at org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)
    at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)
    at org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)
    at org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)
    at org.springframework.webflow.engine.ActionList.execute(ActionList.java:155)
    at org.springframework.webflow.engine.Flow.start(Flow.java:534)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:366)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:222)
    at org.springframework.webflow.executor.FlowExecutorImpl.launchExecution(FlowExecutorImpl.java:140)
    at org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:193)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:679)


** END NESTED EXCEPTION **



Last packet sent to the server was 13 ms ago.
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2586)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2294)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
    at org.hibernate.loader.Loader.list(Loader.java:2167)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at org.uftwf.schoolvisit.dao.SchoolVisitDAOImpl.getSelectableReporters(SchoolVisitDAOImpl.java:56)
    at org.uftwf.schoolvisit.service.SchoolVisitServiceImpl.getSelectableReporters(SchoolVisitServiceImpl.java:130)
    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:616)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy36.getSelectableReporters(Unknown Source)
    at org.uftwf.schoolvisit.controller.swf.FlowActions.initializeSelectableReports(FlowActions.java:57)
    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:616)
    at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
    at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:84)
    at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
    at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
    at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:97)
    at org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)
    at org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)
    at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)
    at org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)
    at org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)
    at org.springframework.webflow.engine.ActionList.execute(ActionList.java:155)
    at org.springframework.webflow.engine.Flow.start(Flow.java:534)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:366)
    at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:222)
    at org.springframework.webflow.executor.FlowExecutorImpl.launchExecution(FlowExecutorImpl.java:140)
    at org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:193)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:679)

Already answered http://stackoverflow.com/questions/4436997/communications-link-failure-due-to-java-io-eofexception — user1516873, Sep 06 '12 at 12:14
the one in the comment above is for tomcat config, if you want to adjust this with mysql conf, see http://stackoverflow.com/a/7906895/365237 — eis, Sep 06 '12 at 12:19

Nirmal- thInk beYond · Accepted Answer · 2012-09-06T12:19:20.483

5

Use c3p0 for pool, it is used ready for production use

Use this parameter(you can modify to optimize), that will retain your connection active

<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.idle_test_period">100</property>

EDIT You can also increase 8 hour limit by below parameter

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

edited Sep 06 '12 at 12:19

answered Sep 06 '12 at 12:12

Nirmal- thInk beYond

11,847
8
35
46

why? there are lot of connection pool implementations. why that? does it help with the problem? – eis Sep 06 '12 at 12:14
yes, by default mysql will close connection after 8 `hour,hibernate.c3p0.idle_test_period` will it keep open, and there are many keys that can improve performance, it is best with hibernate and mysql – Nirmal- thInk beYond Sep 06 '12 at 12:16
Since you can fix this with both mysql conf and tomcat conf, I don't see why user should add a different connection pool. – eis Sep 06 '12 at 12:20
Nirmal-think I installed c3p0 today into my project and I will see how it goes tomorrow.. – PartyWithJohn Sep 06 '12 at 17:57
Nirmal I dont think its going to work.. it does not look like its using the setttings :( – PartyWithJohn Sep 06 '12 at 19:53
have you used connection provider class: ` org.hibernate.connection.C3P0ConnectionProvider ` ? – Nirmal- thInk beYond Sep 07 '12 at 03:44
@nirmal, can you help here....i have this c3p0 config....but still my sql throws this error,....randomly ..... what does this config do ? idle_test_period – ronit May 23 '21 at 19:11

score 3 · Answer 2 · answered Sep 08 '12 at 03:30

This happens because mysql drops connection after 8 hours. One way to fix this is to use c3p0 as mentioned in other posts. Another way is to use mysql's autoReconnect=true property in the jdbc url. This property will attempt to reconnect when the next query is issued on the connection.

More here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

Note that autoReconnect will only handle the connection part. It will not re execute the query automatically. You will have to deal with that yourself by catching SQLException and retrying the query in your code. I recently ran into this and it worked for me.

this ssems helpful,, but can you tell me that after using c3p0 sql wont terminate the connection despite if it doesn't receive any request from client. — ronit, May 23 '21 at 18:01

score 0 · Answer 3 · answered Sep 06 '12 at 13:40

0

What happens is that your app is trying to use a closed connection. This happens because Mysql is automatically closing the connection, but your app does not know that. The main problem is you're properly closing the connections you use. In Hibernate terms, this means you're not closing the Session. Check your code to make sure you're closing every connection after you use it. If you're using the Spring-Hibernate integration, then check the Spring documentation on integration.

answered Sep 06 '12 at 13:40

Matei Florescu

1,155
11
23

Matei I added my java code can you please review it and let me know if I am not doing something right and how to fix it – PartyWithJohn Sep 06 '12 at 15:18
`sessionFactory.getCurrentSession()` gives you the session, you must make sure that the session gets properly closed. In the code you posted the session does not get closed, however this is not necessarily a problem, since it could be closed elsewhere is your app. Even more, Spring can be configured to take care of opening/closing a session (google for "open session in view spring" to find out more). – Matei Florescu Sep 07 '12 at 07:54
One last thing: if you use Spring, you might want to take a look at the HibernateTemplate: [link](http://stackoverflow.com/questions/4067775/spring-hibernate-template-when-to-use-and-why), for example. – Matei Florescu Sep 07 '12 at 07:58

Spring, MySQL and Hibernate: Database error if the system is not used within 8 hours

3 Answers3

Linked