MySQL com problemas... fecha conexão da aplicação depois de um tempo de inatividade

Assuntos Gerais
#1

Olá pessoal,

Preciso de uma ajuda de quem entende de MySQL, acho que seja alguma configuração no servidor MySQL (ou também pode ser no Tomcat… não sei mesmo)
Sempre que deixo minha aplicação rodando no servidor, por 1 dia por exemplo, no dia seguinte eu recebo erro de conexão (que está na stacktrace abaixo)…
Ai tenho que dar um restart no tomcat… para que tudo volte ao normal… isso também já aconteceu enquanto eu estava desenvolvendo no NetBeans e Eclipse…
Ah, estou utilizando VRaptor 3 + Hibernate, faz um tempo que estou tentando descobrir como resolver este problema…

Fico no aguardo…

Obrigado

Att.
Leonardo Lima

Segue a stacktrace

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

br.com.caelum.vraptor.InterceptionException: an exception was raised while executing resource method
	br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:86)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ParametersInstantiatorInterceptor.intercept(ParametersInstantiatorInterceptor.java:77)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InstantiateInterceptor.intercept(InstantiateInterceptor.java:42)
	br.com.caelum.vraptor.core.InstantiatedInterceptorHandler.execute(InstantiatedInterceptorHandler.java:47)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InterceptorListPriorToExecutionExtractor.intercept(InterceptorListPriorToExecutionExtractor.java:46)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.FlashInterceptor.intercept(FlashInterceptor.java:81)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:67)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
	br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
	br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
	br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
root cause

org.hibernate.exception.JDBCConnectionException: could not execute query
	org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
	org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	org.hibernate.loader.Loader.doList(Loader.java:2297)
	org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
	org.hibernate.loader.Loader.list(Loader.java:2167)
	org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
	org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
	org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
	br.com.evto.persistence.dao.EventoDAO.listaUltimosEventos(EventoDAO.java:91)
	br.com.evto.controller.HomeController.index(HomeController.java:21)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:57)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ParametersInstantiatorInterceptor.intercept(ParametersInstantiatorInterceptor.java:77)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InstantiateInterceptor.intercept(InstantiateInterceptor.java:42)
	br.com.caelum.vraptor.core.InstantiatedInterceptorHandler.execute(InstantiatedInterceptorHandler.java:47)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InterceptorListPriorToExecutionExtractor.intercept(InterceptorListPriorToExecutionExtractor.java:46)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.FlashInterceptor.intercept(FlashInterceptor.java:81)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:67)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
	br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
	br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
	br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 35,894,523 milliseconds ago.  The last packet sent successfully to the server was 8 milliseconds ago.
	sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
	com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
	com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
	com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
	org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
	org.hibernate.loader.Loader.doQuery(Loader.java:718)
	org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
	org.hibernate.loader.Loader.doList(Loader.java:2294)
	org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
	org.hibernate.loader.Loader.list(Loader.java:2167)
	org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
	org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
	org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
	br.com.evto.persistence.dao.EventoDAO.listaUltimosEventos(EventoDAO.java:91)
	br.com.evto.controller.HomeController.index(HomeController.java:21)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:57)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ParametersInstantiatorInterceptor.intercept(ParametersInstantiatorInterceptor.java:77)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InstantiateInterceptor.intercept(InstantiateInterceptor.java:42)
	br.com.caelum.vraptor.core.InstantiatedInterceptorHandler.execute(InstantiatedInterceptorHandler.java:47)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InterceptorListPriorToExecutionExtractor.intercept(InterceptorListPriorToExecutionExtractor.java:46)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.FlashInterceptor.intercept(FlashInterceptor.java:81)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:67)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
	br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
	br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
	br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
root cause

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502)
	com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2952)
	com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
	com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
	org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
	org.hibernate.loader.Loader.doQuery(Loader.java:718)
	org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
	org.hibernate.loader.Loader.doList(Loader.java:2294)
	org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
	org.hibernate.loader.Loader.list(Loader.java:2167)
	org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
	org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
	org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
	br.com.evto.persistence.dao.EventoDAO.listaUltimosEventos(EventoDAO.java:91)
	br.com.evto.controller.HomeController.index(HomeController.java:21)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:57)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ParametersInstantiatorInterceptor.intercept(ParametersInstantiatorInterceptor.java:77)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InstantiateInterceptor.intercept(InstantiateInterceptor.java:42)
	br.com.caelum.vraptor.core.InstantiatedInterceptorHandler.execute(InstantiatedInterceptorHandler.java:47)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.InterceptorListPriorToExecutionExtractor.intercept(InterceptorListPriorToExecutionExtractor.java:46)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.FlashInterceptor.intercept(FlashInterceptor.java:81)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:67)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
	br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:65)
	br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
	br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
	br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
	br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
	com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
note The full stack trace of the root cause is available in the Apache Tomcat/6.0.24 logs.
#2

Coloca o resto do stack trace que fica no log

você usa o parâmetro auto_reconnect_for_pools do mysql? ele passa na url

edit:

errei o nome do parâmetro é

autoReconnectForPools

aqui fala sobre ele

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

procura em High Availability and Clustering

#3

Isso é perfeitamente normal. É por isso que recomendamos usar um ConnectionPool e ligar o parâmetro que o colega falou acima.

Movi também o tópico para o fórum de Persistencia.

#4

Obrigado!!

Qual pool que devo usar ? acabei de dar uma olhada no google, mtos falam do C3P0 é esse msmo?

PS:
Desculpa abusar da boa vontade… mas se for o C3P0 mesmo, não teria uma config de exemplo ai não ??

Obrigado novamente!!

Att.
Leonardo Lima

#5

Eu usava o Jakarta DBCP.

#6

Então cara…

Usei o C3P0 mesmo… achei alguns exemplos na web, até que foi fácil…
E até agora não deu mais o erro… ultima vez que dei restart na aplicação foi ontem a noite… espero que realmente funcione rsrs

Muito obrigado pela ajuda viu!!

Att.
Leonardo Lima

#7