pingConnection blocks indefinitely even with poolTimeToWait at default of 20s

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

pingConnection blocks indefinitely even with poolTimeToWait at default of 20s

Douglas Heriot
Hi,

I'm a user of Apache Guacamole (web based RDP, VNC, SSH client). We're running into an issue where MyBatis blocks indefinitely when returning a Postgres connection from the connection pool.

Here's the scenario:
* Application starts, Postgres connection works, everything is good
* Nobody uses application for a few hours, connections still exist in pool
* A user tries to use the application again. MyBatis sees the existing connections in the pool, sees that it hasn't been used for a while so decides to ping the connection with a SELECT 1.
* The call appears to block - in the log we see the line "Testing connection", but no "GOOD" or "BAD" response.
* This is possibly due to a network issue, or a behaviour of AWS RDS - netstat shows the TCP connection is still ESTABLISHED, but data piles up in the Send-Q and is not acknowledged by the Postgres server.


You can see the bug report with the Guacamole project here, where it was determined this is not a bug in Guacamole, but possibly with the MyBatis library:

In this application there is no defaultStatementTimeout set, so I guess the SELECT ping query is allowed to run indefinitely.

In the MyBatis configuration, there is a setting "poolTimeToWait" that seems to be documented as a timeout for returning a connection from a pool. However, it is only applied in the case of creating a new connection, and is not applied in this case of testing an existing (bad) connection from the pool.

Is this to be considered a bug in MyBatis that poolTimeToWait is not being considered here? I would expect that it should be set as the timeout on executing the poolPingQuery.

Or, should I look at setting defaultStatementTimeout in the application and having a timeout apply to all statements?

I'm also wondering - are timeouts even implemented at all for the Postgres backend? I haven't been able to find where that code is.


Thanks,
Douglas

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/4403bde3-c48d-4142-8bdb-705a827df804%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: pingConnection blocks indefinitely even with poolTimeToWait at default of 20s

Iwao AVE!
Hello Douglas,

It could happen if the DB server goes down or missing without properly terminated client's connection.
Last time I checked, the only reliable solution in this situation was to set socket timeout.
Other timeout settings (e.g. query timeout) may not work because the connection looks alive [1].

To configure socket timeout, pgjdbc, like many other drivers, has `socketTimeout` connection property [2].
Alternatively, MyBatis' `PooledDataSource` has `defaultNetworkTimeout` option since version 3.5.2 [3] and pgjdbc seems to support the underlying `java.sql.Connection#setNetworkTimeout(int)` method.

[1] https://github.com/pgjdbc/pgjdbc/issues/750
[2] https://jdbc.postgresql.org/documentation/head/connect.html
[3] https://mybatis.org/mybatis-3/configuration.html#environments

Hope this helps,
Iwao

On Thu, Mar 19, 2020 at 9:19 PM Douglas Heriot <[hidden email]> wrote:
Hi,

I'm a user of Apache Guacamole (web based RDP, VNC, SSH client). We're running into an issue where MyBatis blocks indefinitely when returning a Postgres connection from the connection pool.

Here's the scenario:
* Application starts, Postgres connection works, everything is good
* Nobody uses application for a few hours, connections still exist in pool
* A user tries to use the application again. MyBatis sees the existing connections in the pool, sees that it hasn't been used for a while so decides to ping the connection with a SELECT 1.
* The call appears to block - in the log we see the line "Testing connection", but no "GOOD" or "BAD" response.
* This is possibly due to a network issue, or a behaviour of AWS RDS - netstat shows the TCP connection is still ESTABLISHED, but data piles up in the Send-Q and is not acknowledged by the Postgres server.


You can see the bug report with the Guacamole project here, where it was determined this is not a bug in Guacamole, but possibly with the MyBatis library:

In this application there is no defaultStatementTimeout set, so I guess the SELECT ping query is allowed to run indefinitely.

In the MyBatis configuration, there is a setting "poolTimeToWait" that seems to be documented as a timeout for returning a connection from a pool. However, it is only applied in the case of creating a new connection, and is not applied in this case of testing an existing (bad) connection from the pool.

Is this to be considered a bug in MyBatis that poolTimeToWait is not being considered here? I would expect that it should be set as the timeout on executing the poolPingQuery.

Or, should I look at setting defaultStatementTimeout in the application and having a timeout apply to all statements?

I'm also wondering - are timeouts even implemented at all for the Postgres backend? I haven't been able to find where that code is.


Thanks,
Douglas

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/4403bde3-c48d-4142-8bdb-705a827df804%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2TyDY9kQ8ytcVhdNqzYvwX4KCHSfV6SMyMZaUaA2UZnuw%40mail.gmail.com.