Sounds like you're using TomEE 8 and Postgres (can you specify the version of Postgres and the JDBC driver?)
Can we get a bit more detail on the "failover" element - is this a failover to a secondary database node? Are the database nodes you connect to specified in the JDBC URI, or do you have a loadbalancer/proxy that is shifting the connections across? I'd like to reproduce and find out what's going on. Jon On Tue, Jan 19, 2021 at 10:05 AM Emmanuel Touzery < [email protected]> wrote: > Hello, > > we are seeing with our TOMEE setup that a connection that was > (potentially uncleanly) closed from the SQL server remains in the pool > for longer periods (instead of being closed+reopened), causing long-term > issues with the application, which we can only fix through a restart. > > A scenario where we see this clearly are failovers: in that case > the currently open connections will become invalid, which the > application should realise quickly enough through the validation > queries, therefore quickly cleaning up the pool (trying to establish a > new connection would work, except in the first few hundreds of > milliseconds of the event). But that's not what we see. We also see > situation where something happens to the SQL server (for instance it's > restarted), or the connection from TOMEE to the SQL server is disrupted, > but the application never recovers until TOMEE is restarted. > > Here is our resource, as defined in the tomee.xml: > > <Resource id="jdbc/generic" type="javax.sql.DataSource"> > jdbcDriver = org.postgresql.Driver > jdbcUrl = jdbc:postgresql://x.x.x.x/dbname > userName = xxxxxx > password = xxxxxx > maxActive = 140 > maxIdle = 20 > validationQuery = select version(); > testWhileIdle = true > testOnBorrow = true > testOnReturn = false > timeBetweenEvictionRuns = 10000 millisecond > removeAbandonedTimeout = 4400 > removeAbandoned = true > maxWaitTime = 30000 millisecond > </Resource> > > We are using hibernate. This is tomee 8.0.1, hibernate 5.4.27 (we > upgraded from 5.2.12 recently, had the issue with that version too). > > Is there anything else we should do to make sure that we properly > recover when something happens to some connections from the pool? > > Thank you! > > Emmanuel > > PS: Some relevant sections of the stacktraces we're seeing for longer > period of times after the event: > > Caused by: javax.persistence.PersistenceException: > org.hibernate.exception.JDBCConnectionException: could not prepare > statement > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) > > > at > org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1602) > > > at org.hibernate.query.Query.getResultList(Query.java:165) > > > Caused by: org.postgresql.util.PSQLException: This connection > has been closed. > at > org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:766) > at > org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1582) > at > org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:372) > at > jdk.internal.reflect.GeneratedMethodAccessor91.invoke(Unknown Source) > at > java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > > at > java.base/java.lang.reflect.Method.invoke(Method.java:566) > at > org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) > > > at > org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) > > > at > org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75) > > > at > org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) > > > at > org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) > > > at com.sun.proxy.$Proxy257.prepareStatement(Unknown > Source) > at > jdk.internal.reflect.GeneratedMethodAccessor91.invoke(Unknown Source) > at > java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > > at > java.base/java.lang.reflect.Method.invoke(Method.java:566) > at > org.apache.openejb.resource.jdbc.managed.local.ManagedConnection.invokeUnderTransaction(ManagedConnection.java:277) > > > at > org.apache.openejb.resource.jdbc.managed.local.ManagedConnection.invoke(ManagedConnection.java:132) > > > at com.sun.proxy.$Proxy256.prepareStatement(Unknown > Source) > at > org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149) > > > at > org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176) > > > ... 88 more > >
