tomee-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuel Touzery <emmanuel.touz...@lit-transit.com>
Subject Re: connection pool: poisoned connection not being removed from pool
Date Tue, 19 Jan 2021 12:47:40 GMT
Hello,

     I have to say it's not necessarily about the failover. We can also 
see this if there is something wrong with the connection to the server. 
I believe including if the SQL server is restarted.

     My understanding is that it's normal that there is an error, but I 
would expect TOMEE to recover -- to stop handling to the application a 
JDBC connection which is in fact closed and has shown itself in the past 
to be invalid. Especially since we configure a validation query.

     This is otherwise Enterprise DB 12. My understanding is that it's 
mostly a rebranded Postgresql. We are using the normal postgresql 
driver, although an older version (we didn't try a newer version, 
assuming that's not related to the issue at hand). We're using the 
postgresql jdbc driver version 42.1.4.
     For the failover, we're using EFM:
https://www.enterprisedb.com/edb-docs/d/edb-postgres-failover-manager/user-guides/user-guide/3.7/using_efm_utility.html

     There is a "virtual IP" that we connect to through JDBC, and it 
links to one or the other true IP, over 3 EDB nodes.

     We actually don't get an issue at every switchover. Even load 
testing the app (with a relatively light load though), we need ~4 
failovers to reproduce the issue. Meaning, an OK failover means that we 
get JDBC errors during the failover, but then the application recovers. 
A bad failover is when we keep getting errors long after the failover 
finished. I think it's tied to whether we had a compromised connection 
in the pool before the failover.

     Thank you for the answer!

Emmanuel

On 19/01/2021 13:26, Jonathan Gallimore wrote:
> 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 <
> emmanuel.touzery@lit-transit.com> 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
>>
>>


Mime
View raw message