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
