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.

The relevant code is here: https://github.com/mybatis/mybatis-3/blob/cb11469b3118c5d1d0501c489dcfe196d7c8568d/src/main/java/org/apache/ibatis/datasource/pooled/PooledDataSource.java#L555

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:

https://issues.apache.org/jira/browse/GUACAMOLE-919?focusedCommentId=17062493&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17062493

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.

https://mybatis.org/mybatis-3/configuration.html

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

[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

--
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 mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/4403bde3-c48d-4142-8bdb-705a827df804%40googlegroups.com.