tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Casey Merrill <CMerr...@apptio.com>
Subject Re: Connection Pool only evicts closed connections when validator runs?
Date Thu, 22 Mar 2018 21:46:35 GMT
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=10000; 
numTestsPerEvictionRun=0;
 minEvictableIdleTimeMillis=60000;
 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=30000; 
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" <chris@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 your tomcat-pool configuration look like?
    
    - -chris
    -----BEGIN PGP SIGNATURE-----
    Comment: GPGTools - http://gpgtools.org
    Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
    
    iQJRBAEBCAA7FiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlq0IUEdHGNocmlzQGNo
    cmlzdG9waGVyc2NodWx0ei5uZXQACgkQHPApP6U8pFhcuw/+OKuhKA4Wm5NjZFbN
    ln5bYs39tDIXa+5/KkduS0/dm815LSbj7NPbRVPgk+oS5TxHmusjnnZ8Otud70B8
    u30GzTRCTO+Cke2+qDFqe4hv/mBHhX+tQA2lFQgQfEb1dqhdT7eQEtcperIyxu7K
    TnW+CelagSqPFHnl/KE1PRDtsG/MYD4XPNQ9XtQkSKFQd1MstOp6YfVg1uDVi6IH
    mTaKOAU0ufZEMCtWfl3uH5hD54mDnq261G43ij8+ce33Xh7WZyq5hj5ZKhLWeuMT
    PxxYaf21UI2hlvoL9whjznjNwju8FNRrBBDO3zO/I5zxpsTBTZtOKGA0a5V7n3zW
    sYM7BwOx2wHGleGMTf6IncJIWjK/PRIjxwV7BFVkfLmtrF/9VBlHiux6U8/wVVhv
    pl7oQ90C3F5op8SZQ2aUGT1wrYEdAB95xoyWSaasvTSnns/fSJFskxkz6WC7XZtS
    RWj5HkrNmGnOyyBTFJCflmhbvi6AEGirTjCqmPrqBzWC/8/PTL/IfI5qluzsux6a
    5purySIj9LU4wdp8dQNibw0h3ZUJtqYfbKI8rpc2crsTu+jHk8WvF1ppfwW1gsT9
    vO0XW3SIc1k9vCAzQBhAQEuDYlcTSF6fKZFGbBPVFuSWCC+pC7Upp/6EWiY5vziN
    he7o1AicRZBwR6gRttdzaz7V2+4=
    =NAfD
    -----END PGP SIGNATURE-----
    
    ---------------------------------------------------------------------
    To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
    For additional commands, e-mail: users-help@tomcat.apache.org
    
    


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org

Mime
View raw message