Jooq connection pooling with Tomcat not evicting closed connections?

[caseyder...@gmail.com]

I'm seeing what appears to be closed connections being reused for a short time period.  It looks like the only way these connections are removed from the connection pool is when the validator runs.  Is there a configurable way to change this? As of now I've created a class that extends DefaultExecuteListener and manually discards the connections on specific errros.

Example error: (repeats with different queries many times before the connection is killed)

....user`.`login` WHERE `session`.`id` = ?]; No operations allowed after connection closed.

....

at org.jooq_3.9.1.MYSQL.debug(Unknown Source) ... Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. ... Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 11,618 milliseconds ago. The last packet sent successfully to the server was 5,005 milliseconds ago. ... 1 common frames omitted Caused by: java.net.SocketTimeoutException: Read timed out ... 28 common frames omitted

Here's my ExcuteListener that I'm using to detect one of these errors and discard the connection.

ConnectionListener extends DefaultExecuteListener {

...


    public ConnectionListener() {}

    @Override
    public void exception(ExecuteContext ctx) {
        try {
            if (ctx != null && ctx.exception() != null) {
                //Proceed to check if we recieved a DataAccessException
                if (ctx.exception() instanceof DataAccessException) {
                    DataAccessException exception = (DataAccessException) ctx.exception();

                    //If the error is network related discard the connection
                    if (isNetworkError(exception)) {
                        //The underlying ProxyConnection which we need to call  setDiscarded is a few levels deep
                        Connection conn = ctx.connection();
                        DefaultConnection dConn = (DefaultConnection) conn;
                        SettingsEnabledConnection sConn = (SettingsEnabledConnection) dConn;
                        ProviderEnabledConnection pConn = (ProviderEnabledConnection) sConn.getDelegate();

                        //Get the Proxy connection handler
                        InvocationHandler handler = Proxy.getInvocationHandler(pConn.getDelegate());

                        //Get the Proxy connection
                        ProxyConnection proxyConnection = (ProxyConnection)
                                ((DisposableConnectionFacade) handler).getNext();

                        //Discard the connection
                        proxyConnection.getConnection().setDiscarded(true);
                    }
                }
            }
        } catch (Exception e) {
            logger.error("ConnectionListener caught unexpected error", e);
        }
    }

[Casey Merrill]

I'm aware that checkConnectionOnBorrow and upping the validation interval to something shorter than the default 30 seconds will reduce the errors but I'd like to drop the connection on the first failure.

[Lukas Eder]

Hi Casey,

The important thing to understand here is that jOOQ doesn't pool any of your connections. It externalises this responsibility through the ConnectionProvider SPI. Either, you have implemented your own ConnectionProvider, or you have implicitly chosen one of the two defaults:

- DefaultConnectionProvider (e.g. when you call DSL.using(connection))

- DataSourceConnectionProvider (e.g. when you call DSL.using(datasource))

In the first case, jOOQ will never call Connection.close() as that will be your responsibility.

In the second case, jOOQ will always call Connection.close() as the general DataSource contract is that connections have to be closed after usage for the DataSource to implement whatever connection pool semantics.

I suspect your problem lies in the configuration of your connection pool / data source.

Hope this helps,

Lukas

2018-03-21 19:37 GMT+01:00 Casey Merrill <caseyder...@gmail.com>:

I'm aware that checkConnectionOnBorrow and upping the validation interval to something shorter than the default 30 seconds will reduce the errors but I'd like to drop the connection on the first failure.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.