You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Casey Merrill <CM...@apptio.com> on 2018/03/22 19:33:37 UTC
Connection Pool only evicts closed connections when validator runs?
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);
}
}
Re: Connection Pool only evicts closed connections when validator
runs?
Posted by Casey Merrill <CM...@apptio.com>.
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" <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 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
Re: Connection Pool only evicts closed connections when validator
runs?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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