O Hibernate não consegue executar a query após 8 horas de inatividade

Desenvolvi um sistema Web usando Flex na interface no cliente e Java+Hibernate no servidor (Tomcat 6 + Mysql). Ele está apresentando um problema:
O sistema é utilizado durante o dia, porém durante a noite, ninguém acessa, então, no outro dia o Hibernate retorna o erro:

faultCode:Server.Processing faultString:‘org.hibernate.exception.JDBCConnectionException : could not execute query’ faultDetail:‘null’

Já pesquisei em outros foruns e descobri que o Mysql fecha a conexão após 8 horas sem acesso, mas o estranho é que outros sistemas desenvolvidos da mesma forma, com os mesmos parâmetros de configuração e mesmo período de utilização NÃO apresentam esse erro.

O que eu posso fazer para resolver esse problema sem ter que reiniciar o sistema todas as manhãs?

Depende do pool de conexoes que você está usando… tem pools que executam queries de keep alive em um tempo que você pode programar… assim, se a conexao não for utilizada ele roda essa query de tempos em tempos só pra garantir que ela nao expire

Mas como eu garanto isso, dê um exemplo.

Cara, te dei o caminho, se vc quer tudo mastigado nao é comigo… v no google

Procurei um pool de conexões para usar e encontrei o C3PO. Adicionei as seguintes configurações na hibernate.cfg.xml:

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.min_size">2</property>
        <property name="hibernate.c3p0.timeout">5000</property>
        <property name="hibernate.c3p0.max_statements">10</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>
        <property name="hibernate.c3p0.acquire_increment">2</property>

Agora as consultas que demoram de executar e as vezes nem executam. Essas consultas antes não demoravam nem meio segundo. O que poderia ser?

Cara, estou com problema e creio ser o mesmo , de tempos em tempos tenho que dar um restart no meu servidor para acessar a aplicação.

Teve algum sucesso com essas config no xml do hibernate?

Meu Log está desta forma:

Erro dia 7 Dec 7, 2010 4:14:06 PM

Dec 7, 2010 4:14:06 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet default threw exception
br.com.caelum.vraptor.InterceptionException: ClientAbortException:  java.net.SocketException: Broken pipe
	at br.com.caelum.vraptor.resource.DefaultResourceNotFoundHandler.couldntFind(DefaultResourceNotFoundHandler.java:43)
	at br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:69)
	at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
	at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
	at br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
	at br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
	at br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
	at br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
	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:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	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.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:769)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:698)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)
Caused by: ClientAbortException:  java.net.SocketException: Broken pipe
	at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:358)
	at org.apache.tomcat.util.buf.ByteChunk.flushBuffer(ByteChunk.java:434)
	at org.apache.tomcat.util.buf.ByteChunk.append(ByteChunk.java:349)
	at org.apache.catalina.connector.OutputBuffer.writeBytes(OutputBuffer.java:381)
	at org.apache.catalina.connector.OutputBuffer.write(OutputBuffer.java:370)
	at org.apache.catalina.connector.CoyoteOutputStream.write(CoyoteOutputStream.java:89)
	at org.apache.catalina.servlets.DefaultServlet.copyRange(DefaultServlet.java:2012)
	at org.apache.catalina.servlets.DefaultServlet.copy(DefaultServlet.java:1755)
	at org.apache.catalina.servlets.DefaultServlet.serveResource(DefaultServlet.java:844)
	at org.apache.catalina.servlets.DefaultServlet.doGet(DefaultServlet.java:332)
	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 br.com.caelum.vraptor.resource.DefaultResourceNotFoundHandler.couldntFind(DefaultResourceNotFoundHandler.java:41)
	... 24 more
Caused by: java.net.SocketException: Broken pipe
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
	at org.apache.jk.common.ChannelSocket.send(ChannelSocket.java:532)
	at org.apache.jk.common.JkInputStream.doWrite(JkInputStream.java:162)
	at org.apache.coyote.Response.doWrite(Response.java:560)
	at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:353)
	... 38 more
Dec 7, 2010 4:14:06 PM org.apache.jk.common.ChannelSocket processConnection
WARNING: processCallbacks status 2

Entao meu servidor teve alguns restarts:

Dec 7, 2010 6:56:04 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 3609 ms

e

Dec 8, 2010 4:02:09 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 5798 ms

Entao o erro abaixo nos seguintes dias até que eu reinicie o servidor.

Dec 8, 2010 2:38:29 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet default threw exception
br.com.caelum.vraptor.InterceptionException: exception raised, check root cause for details: org.hibernate.exception.JDBCConnectionException: could not execute query
	at br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:86)

E novamente dias depois…

Dec 11, 2010 12:35:12 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet default threw exception
br.com.caelum.vraptor.InterceptionException: exception raised, check root cause for details: org.hibernate.exception.JDBCConnectionException: could not execute query
	at br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:86)
	at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
	at br.com.caelum.vraptor.interceptor.ExceptionHandlerInterceptor.intercept(ExceptionHandlerInterceptor.java:69)
	at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
	at br.com.caelum.vraptor.core.LazyInterceptorHandler.execute(LazyInterceptorHandler.java:61)
	at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
...
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:769)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:698)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.JDBCConnectionException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2235)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
	at org.hibernate.loader.Loader.list(Loader.java:2124)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1597)
	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
	at br.com.henrykauan.dao.EnderecoDao.buscaEstados(EnderecoDao.java:27)
	at br.com.henrykauan.controller.PortalController.contatos(PortalController.java:39)
	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 br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:57)
	... 47 more
Caused by: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2232)
	... 59 more
Caused by: java.io.EOFException
	at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:257)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1165)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
	... 67 more

Valeu, consegui resolver o problema da minha aplicação com o C3P0.
Tudo nesse post. http://guj.com.br/posts/list/141603.java e http://blog.caelum.com.br/2009/10/19/a-java-net-socketexception-broken-pipe/

abcs

[quote=fidelis felipe]Valeu, consegui resolver o problema da minha aplicação com o C3P0.
Tudo nesse post. http://guj.com.br/posts/list/141603.java e http://blog.caelum.com.br/2009/10/19/a-java-net-socketexception-broken-pipe/

abcs
[/quote]

Opa, valeu pelo link.