Problemas com pool de conexões no TomEE

[prog.tiago]

Boa noite, amigos!

Estou desenvolvendo uma aplicação de testes no TomEE (http://tomee.apache.org/). Conhecem?

Pois bem, seguindo a documentação oficial, criei um datasource no arquivo /conf/tomee.xml

A DataSource can be declared via xml in the /conf/tomee.xml file or in a WEB-INF/resources.xml file using a declaration like the following. All properties in the element body are optional.

O meu .xml ficou assim:

[code]<?xml version="1.0" encoding="UTF-8"?>

<Resource id="qvpDataSource"   type="javax.sql.DataSource">
	jdbcDriver = com.microsoft.sqlserver.jdbc.SQLServerDriver
	jdbcUrl = jdbc:sqlserver://192.168.56.102:1433;databaseName=sistemaqvp
	password = senha
	userName = usuario
	jtaManaged = true
	maxActive = 20
	maxIdle = 20
	maxOpenPreparedStatements = 0
	maxWaitTime = -1 millisecond
	minEvictableIdleTime = 30 minutes
	minIdle = 0
	poolPreparedStatements = true
</Resource>

[/code]

Dentro do meu projeto, o meu persistence ficou assim:

<?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"> <persistence-unit name="QvpPersistence" transaction-type="JTA"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>qvpDataSource</jta-data-source> <class></class> <properties> <property name="hibernate.show_sql" value="true"/> <property name="hibernate.format_sql" value="true"/> <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" /> </properties> </persistence-unit> </persistence>

Estou conseguindo me conectar pela aplicação. Tá tudo funcionando. Percebi um problema quando deixei a aplicação rodando por algumas horas (durante a noite) e quando acordei percebi que a aplicação não conseguia mais se conectar na base de dados. A seguinte exception foi lançada:

[quote]javax.transaction.xa.XAException: Count not turn off auto commit for a XA transaction
at org.apache.openejb.resource.jdbc.managed.local.LocalXAResource.start(LocalXAResource.java:67)
at org.apache.geronimo.transaction.manager.TransactionImpl.enlistResource(TransactionImpl.java:209)
at org.apache.openejb.resource.jdbc.managed.local.ManagedConnection.invoke(ManagedConnection.java:112)
at com.sun.proxy.$Proxy104.prepareStatement(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:257)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:201)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102)
at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:186)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4126)
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:503)
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:468)
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:213)
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:275)
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:151)
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1106)
at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:176)
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2587)
at org.hibernate.internal.SessionImpl.get(SessionImpl.java:991)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1110)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1068)
at org.apache.openejb.persistence.JtaEntityManager.find(JtaEntityManager.java:180)
at br.com.qvp.model.dao.UsuarioDao.getAlgumUsuario(UsuarioDao.java:16)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.monitoring.StatsInterceptor.record(StatsInterceptor.java:181)
at org.apache.openejb.monitoring.StatsInterceptor.invoke(StatsInterceptor.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.core.interceptor.InterceptorStack.invoke(InterceptorStack.java:85)
at org.apache.openejb.core.stateless.StatelessContainer._invoke(StatelessContainer.java:227)
at org.apache.openejb.core.stateless.StatelessContainer.invoke(StatelessContainer.java:194)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler.synchronizedBusinessMethod(EjbObjectProxyHandler.java:308)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler.businessMethod(EjbObjectProxyHandler.java:303)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke(EjbObjectProxyHandler.java:92)
at org.apache.openejb.core.ivm.BaseEjbProxyHandler.invoke(BaseEjbProxyHandler.java:322)
at br.com.qvp.model.dao.UsuarioDao$$LocalBeanProxy.getAlgumUsuario(br/com/qvp/model/dao/UsuarioDao.java)
at br.com.qvp.model.service.UsuarioServiceImpl.getAlgumUsuario(UsuarioServiceImpl.java:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.monitoring.StatsInterceptor.record(StatsInterceptor.java:181)
at org.apache.openejb.monitoring.StatsInterceptor.invoke(StatsInterceptor.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.core.interceptor.InterceptorStack.invoke(InterceptorStack.java:85)
at org.apache.openejb.core.stateless.StatelessContainer._invoke(StatelessContainer.java:227)
at org.apache.openejb.core.stateless.StatelessContainer.invoke(StatelessContainer.java:194)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler.synchronizedBusinessMethod(EjbObjectProxyHandler.java:308)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler.businessMethod(EjbObjectProxyHandler.java:303)
at org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke(EjbObjectProxyHandler.java:92)
at org.apache.openejb.core.ivm.BaseEjbProxyHandler.invoke(BaseEjbProxyHandler.java:322)
at com.sun.proxy.$Proxy103.getAlgumUsuario(Unknown Source)
at br.com.qvp.view.managedbean.UsuarioMB.buscar(UsuarioMB.java:17)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:273)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.tomee.catalina.OpenEJBValve.invoke(OpenEJBValve.java:44)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1668)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1655)
at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1805)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:3581)
at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:3482)
at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3062)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6120)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6106)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:1757)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:1762)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:1902)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
at com.sun.proxy.$Proxy54.setAutoCommit(Unknown Source)
at org.apache.openejb.resource.jdbc.managed.local.LocalXAResource.start(LocalXAResource.java:65)
… 108 more

Ago 15, 2015 3:56:36 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: null
Ago 15, 2015 3:56:36 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: A conexão está fechada.[/quote]

Concluo que todas as conexões foram fechadas por tempo de inatividade pelo próprio banco. Mas não é função do pool de conexões criar novas quando necessário? Por que não funcionou?

Estou correto nessa ideia? Nunca configurei um pool de conexões, talvez eu esteja falando besteira.

Agradeço se puderem me ajudar. Um abraço!

[Hebert_Coelho]

Creio que está faltando uma query de validação aí.

O modelo que eu uso é:

<Resource factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" name="jdbc/GatewayDS" auth="Container" type="javax.sql.DataSource" initialSize="1" maxActive="20" maxIdle="10" minIdle="5" maxWait="5000" username="usuario" password="senha" driverClassName="net.sourceforge.jtds.jdbc.Driver" validationQuery="SELECT 'OK'" testWhileIdle="true" testOnBorrow="true" numTestsPerEvictionRun="5" timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000" url="URL_DO_BANCO"/>

Caso a consulta SELECT ‘OK’ dê uma mensagem de erro, deve ser por causa da sintaxe do seu banco. Já vi algumas queries com “select 1” e coisas do tipo.