Here's my configuration for dropwizard
driverClass: com.mysql.jdbc.Driver
charSet: UTF-8
characterEncoding: UTF-8
maxWaitForConnection: 1s
validationQuery: "select case when @@read_only + @@innodb_read_only = 0 then
1 else (select table_name from information_schema.tables) end as `1`"
validationQueryTimeout: 3s
initialSize: 5
minSize: 5
maxSize: 5
checkConnectionWhileIdle: true
evictionInterval: 10s
minIdleTime: 1 minute
checkConnectionOnBorrow: true
And the underlying tomcat-pool config looks like
ConnectionPool[defaultAutoCommit=null;
defaultReadOnly=null;
defaultTransactionIsolation=-1;
defaultCatalog=null;
driverClassName=com.mysql.jdbc.Driver;
maxActive=25;
maxIdle=;
minIdle=5; i
nitialSize=5;
maxWait=1000;
testOnBorrow=true;
testOnReturn=false;
timeBetweenEvictionRunsMillis=1;
numTestsPerEvictionRun=0;
minEvictableIdleTimeMillis=6;
testWhileIdle=true;
testOnConnect=true;
password=;
url=**;
username=*;
validationQuery=select case when @@read_only + @@innodb_read_only = 0 then 1
else (select table_name from information_schema.tables) end as `1`;
validationQueryTimeout=3;
validatorClassName=null;
validationInterval=3;
accessToUnderlyingConnectionAllowed=true;
removeAbandoned=false;
removeAbandonedTimeout=60;
logAbandoned=false;
connectionProperties=null;
initSQL=null;
jdbcInterceptors=null;
jmxEnabled=true;
fairQueue=true;
useEquals=true;
abandonWhenPercentageFull=0;
maxAge=0;
useLock=false;
dataSource=null;
dataSourceJNDI=null;
suspectTimeout=0;
alternateUsernameAllowed=false;
commitOnReturn=false;
rollbackOnReturn=false;
useDisposableConnectionFacade=true;
logValidationErrors=false;
propagateInterruptState=false;
ignoreExceptionOnPreLoad=false;
On 3/22/18, 2:34 PM, "Christopher Schultz" <ch...@christopherschultz.net>
wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Casey,
On 3/22/18 3:33 PM, Casey Merrill wrote:
> I’m currently using Dropwizard + Jooq and Tomcat jdbc for the
> connection pool. I’m seeing issues when a connection dies it’s not
> evicted from the connection pool until the validator runs. While
> I can turn the rate up at which the validator runs its seems odd
> that a closed connection is returned to the pool.
>
> 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
>
> I’m able to fix this by getting a reference to the connection and
> manually setting it to discarded, but his is quite hackey.
>
> 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); } }
>
What does
