You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Chris Cheshire <ya...@gmail.com> on 2018/12/13 20:50:47 UTC
lingering mysql connections
Tomcat 9.0.12, Debian, MySQL Server 5.7.23, Connector/J 5.1.46
I am trying to fix a lingering database connection problem. When I
reload a context via the tomcat manager, connections to the
datasources are not being released in mysql. They are still on the 30
second activity cycle from the eviction thread. I can see this via
'show processlist' in the mysql client - the 'time' column resets at
30, and each connection has unique process ids that I can track per
reload.
I have tomcat home and base split (multiple instances of tomcat across
different users), with the connector/j jar in tomcat_base/lib.
In my webapp's META-INF/context.xml I have 3 different datasources,
config, data, sched. All have configuration like :
<Resource name="jdbc/config"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=true&characterEncoding=utf8&useSSL=false"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
username="$USER$"
password="$PASSWORD$"
maxActive="2"
maxIdle="1"
minIdle="1"
initialSize="1"
maxWait="30000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
validationQuery="/* ping */"
testOnBorrow="true"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="30000"
defaultAutoCommit="false"
defaultIsolation="READ_COMMITTED" />
Connections are obtained via
Connection dbConn = ((DataSource)new
InitialContext().lookup(resourceName)).getConnection()
Connections are all closed via
dbConn.close()
(Simplified greatly, there's convenience methods with exception
handling in there)
In contextDestroyed() of a ServletContextListener I am calling
AbandonedConnectionCleanupThread.checkedShutdown();
I have an initialization servlet that must be manually called before
the webapp is fully online - it is used to load encrypted
configuration from the conf datasource. It does not touch the data
datasource, only conf and sched by virtue of starting the quartz
scheduler which is configured to use this datasource.
My observation are :
* It doesn't matter what order I declare the datasources, they are
always getting opened in the order sched, conf, data (judging by
increased thread/process ids in mysql).
* When I start tomcat, I get 3 open connections in mysql, 1 to each of
the databases referenced by the datasources. If I immediately reload
via the manager, all 3 connections are destroyed and 3 new ones are
opened.
* Once I call the initialization servlet, and subsequently reload the
web app via the manager, previous connections to conf and sched are
still open in mysql, as well as new ones
* If I access any part of the web app that uses the data datasource,
those connections now also linger.
* Once I stop tomcat (and the JVM) all lingering connections are
closed in mysql.
* If I put the connector/j jar in my WEB-INF/lib instead of
tomcat_base/lib, I get the following warning on reload/shutdown
13-Dec-2018 20:19:53.968 WARNING [ajp-nio-8019-exec-3]
org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc
The web application [ct] registered the JDBC driver
[com.mysql.jdbc.Driver] but failed to unregister it when the web
application was stopped. To prevent a memory leak, the JDBC Driver has
been forcibly unregistered.
* There are no warnings or errors in catalina.out about abandoned
connections during runtime, reload or shutdown of the tomcat instance.
I have every connection being closed after use. (I have seen the
warnings when I have made a code mistake however, so the thread is
doing its job).
* If I remove the abandoned connection and eviction thread
configuration entirely, the connection activity timer resets on a 5
second cycle in mysql. If I explicitly change
timeBetweenEvictionRunsMillis to -1, the activity timer in mysql never
resets (which implies that the default is not -1 as the documentation
suggests)
So it seems that once a datasource is accessed, connections to it
perpetually linger in mysql until the JVM is shutdown.
I have had this issue for a long time, through tomcat 7, 8.5 and 9,
and multiple versions of mysql server and the connector., and also
using the commons pool. On my sandboxes I tend to reload a lot as I
deploy exploded, rather than deploy an entire WAR each time.
Googling led to multiple old bug reports filed w/ MySQL about the
cleanup thread, but nothing that would point to me doing anything
different than what I am doing. Are these lingering connections just a
fact of doing business with MySQL, or is there something I need to
configure/change in code to clean this up?
Thanks
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: lingering mysql connections
Posted by HeeGu Lee <el...@gmail.com>.
Hi, chris.
You can use apache commons-dbcp2-2.5.0.
This problem is fixed in this version.
And ServletContextListener is not necessary!
This is make you happy. :-)
Have a nice day!!!
2018년 12월 14일 (금) 오전 5:51, Chris Cheshire <ya...@gmail.com>님이 작성:
> Tomcat 9.0.12, Debian, MySQL Server 5.7.23, Connector/J 5.1.46
>
> I am trying to fix a lingering database connection problem. When I
> reload a context via the tomcat manager, connections to the
> datasources are not being released in mysql. They are still on the 30
> second activity cycle from the eviction thread. I can see this via
> 'show processlist' in the mysql client - the 'time' column resets at
> 30, and each connection has unique process ids that I can track per
> reload.
>
> I have tomcat home and base split (multiple instances of tomcat across
> different users), with the connector/j jar in tomcat_base/lib.
>
> In my webapp's META-INF/context.xml I have 3 different datasources,
> config, data, sched. All have configuration like :
>
> <Resource name="jdbc/config"
> auth="Container"
> type="javax.sql.DataSource"
> driverClassName="com.mysql.jdbc.Driver"
>
> url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=true&characterEncoding=utf8&useSSL=false"
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> username="$USER$"
> password="$PASSWORD$"
> maxActive="2"
> maxIdle="1"
> minIdle="1"
> initialSize="1"
> maxWait="30000"
> removeAbandoned="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"
> validationQuery="/* ping */"
> testOnBorrow="true"
> testWhileIdle="true"
> timeBetweenEvictionRunsMillis="30000"
> defaultAutoCommit="false"
> defaultIsolation="READ_COMMITTED" />
>
> Connections are obtained via
>
> Connection dbConn = ((DataSource)new
> InitialContext().lookup(resourceName)).getConnection()
>
> Connections are all closed via
>
> dbConn.close()
>
> (Simplified greatly, there's convenience methods with exception
> handling in there)
>
>
>
> In contextDestroyed() of a ServletContextListener I am calling
>
> AbandonedConnectionCleanupThread.checkedShutdown();
>
> I have an initialization servlet that must be manually called before
> the webapp is fully online - it is used to load encrypted
> configuration from the conf datasource. It does not touch the data
> datasource, only conf and sched by virtue of starting the quartz
> scheduler which is configured to use this datasource.
>
> My observation are :
> * It doesn't matter what order I declare the datasources, they are
> always getting opened in the order sched, conf, data (judging by
> increased thread/process ids in mysql).
> * When I start tomcat, I get 3 open connections in mysql, 1 to each of
> the databases referenced by the datasources. If I immediately reload
> via the manager, all 3 connections are destroyed and 3 new ones are
> opened.
> * Once I call the initialization servlet, and subsequently reload the
> web app via the manager, previous connections to conf and sched are
> still open in mysql, as well as new ones
> * If I access any part of the web app that uses the data datasource,
> those connections now also linger.
> * Once I stop tomcat (and the JVM) all lingering connections are
> closed in mysql.
> * If I put the connector/j jar in my WEB-INF/lib instead of
> tomcat_base/lib, I get the following warning on reload/shutdown
>
> 13-Dec-2018 20:19:53.968 WARNING [ajp-nio-8019-exec-3]
> org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc
> The web application [ct] registered the JDBC driver
> [com.mysql.jdbc.Driver] but failed to unregister it when the web
> application was stopped. To prevent a memory leak, the JDBC Driver has
> been forcibly unregistered.
>
> * There are no warnings or errors in catalina.out about abandoned
> connections during runtime, reload or shutdown of the tomcat instance.
> I have every connection being closed after use. (I have seen the
> warnings when I have made a code mistake however, so the thread is
> doing its job).
> * If I remove the abandoned connection and eviction thread
> configuration entirely, the connection activity timer resets on a 5
> second cycle in mysql. If I explicitly change
> timeBetweenEvictionRunsMillis to -1, the activity timer in mysql never
> resets (which implies that the default is not -1 as the documentation
> suggests)
>
> So it seems that once a datasource is accessed, connections to it
> perpetually linger in mysql until the JVM is shutdown.
>
> I have had this issue for a long time, through tomcat 7, 8.5 and 9,
> and multiple versions of mysql server and the connector., and also
> using the commons pool. On my sandboxes I tend to reload a lot as I
> deploy exploded, rather than deploy an entire WAR each time.
>
> Googling led to multiple old bug reports filed w/ MySQL about the
> cleanup thread, but nothing that would point to me doing anything
> different than what I am doing. Are these lingering connections just a
> fact of doing business with MySQL, or is there something I need to
> configure/change in code to clean this up?
>
>
> Thanks
>
> Chris
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: lingering mysql connections
Posted by Chris Cheshire <ya...@gmail.com>.
This is a bit of a lead but it doesn't entirely solve it. It's only
cleaning up connections to one of the datasources, I still have
connections for 2 of them duplicated :(
If I get a chance I'll go digging some more based on the SO rabbit
hole, but I can mitigate the problem by restarting tomcat (since it's
really only a sandbox issue where I do a lot of redeploys)
Cheers
Chris
On Fri, Dec 14, 2018 at 3:00 AM Greg Huber <gr...@gmail.com> wrote:
>
> I resolved the same using this link
>
> https://stackoverflow.com/questions/11872316/tomcat-guice-jdbc-memory-leak
>
> I created the ContextFinalizer to cleanup on shut down.
>
> Also, I had loads of strange sql issues which were resolved by switching to
> maria db.
>
> Cheers Greg
>
> On Thu, 13 Dec 2018 at 20:51, Chris Cheshire <ya...@gmail.com> wrote:
>
> > Tomcat 9.0.12, Debian, MySQL Server 5.7.23, Connector/J 5.1.46
> >
> > I am trying to fix a lingering database connection problem. When I
> > reload a context via the tomcat manager, connections to the
> > datasources are not being released in mysql. They are still on the 30
> > second activity cycle from the eviction thread. I can see this via
> > 'show processlist' in the mysql client - the 'time' column resets at
> > 30, and each connection has unique process ids that I can track per
> > reload.
> >
> > I have tomcat home and base split (multiple instances of tomcat across
> > different users), with the connector/j jar in tomcat_base/lib.
> >
> > In my webapp's META-INF/context.xml I have 3 different datasources,
> > config, data, sched. All have configuration like :
> >
> > <Resource name="jdbc/config"
> > auth="Container"
> > type="javax.sql.DataSource"
> > driverClassName="com.mysql.jdbc.Driver"
> >
> > url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=true&characterEncoding=utf8&useSSL=false"
> > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> > username="$USER$"
> > password="$PASSWORD$"
> > maxActive="2"
> > maxIdle="1"
> > minIdle="1"
> > initialSize="1"
> > maxWait="30000"
> > removeAbandoned="true"
> > removeAbandonedTimeout="60"
> > logAbandoned="true"
> > validationQuery="/* ping */"
> > testOnBorrow="true"
> > testWhileIdle="true"
> > timeBetweenEvictionRunsMillis="30000"
> > defaultAutoCommit="false"
> > defaultIsolation="READ_COMMITTED" />
> >
> > Connections are obtained via
> >
> > Connection dbConn = ((DataSource)new
> > InitialContext().lookup(resourceName)).getConnection()
> >
> > Connections are all closed via
> >
> > dbConn.close()
> >
> > (Simplified greatly, there's convenience methods with exception
> > handling in there)
> >
> >
> >
> > In contextDestroyed() of a ServletContextListener I am calling
> >
> > AbandonedConnectionCleanupThread.checkedShutdown();
> >
> > I have an initialization servlet that must be manually called before
> > the webapp is fully online - it is used to load encrypted
> > configuration from the conf datasource. It does not touch the data
> > datasource, only conf and sched by virtue of starting the quartz
> > scheduler which is configured to use this datasource.
> >
> > My observation are :
> > * It doesn't matter what order I declare the datasources, they are
> > always getting opened in the order sched, conf, data (judging by
> > increased thread/process ids in mysql).
> > * When I start tomcat, I get 3 open connections in mysql, 1 to each of
> > the databases referenced by the datasources. If I immediately reload
> > via the manager, all 3 connections are destroyed and 3 new ones are
> > opened.
> > * Once I call the initialization servlet, and subsequently reload the
> > web app via the manager, previous connections to conf and sched are
> > still open in mysql, as well as new ones
> > * If I access any part of the web app that uses the data datasource,
> > those connections now also linger.
> > * Once I stop tomcat (and the JVM) all lingering connections are
> > closed in mysql.
> > * If I put the connector/j jar in my WEB-INF/lib instead of
> > tomcat_base/lib, I get the following warning on reload/shutdown
> >
> > 13-Dec-2018 20:19:53.968 WARNING [ajp-nio-8019-exec-3]
> > org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc
> > The web application [ct] registered the JDBC driver
> > [com.mysql.jdbc.Driver] but failed to unregister it when the web
> > application was stopped. To prevent a memory leak, the JDBC Driver has
> > been forcibly unregistered.
> >
> > * There are no warnings or errors in catalina.out about abandoned
> > connections during runtime, reload or shutdown of the tomcat instance.
> > I have every connection being closed after use. (I have seen the
> > warnings when I have made a code mistake however, so the thread is
> > doing its job).
> > * If I remove the abandoned connection and eviction thread
> > configuration entirely, the connection activity timer resets on a 5
> > second cycle in mysql. If I explicitly change
> > timeBetweenEvictionRunsMillis to -1, the activity timer in mysql never
> > resets (which implies that the default is not -1 as the documentation
> > suggests)
> >
> > So it seems that once a datasource is accessed, connections to it
> > perpetually linger in mysql until the JVM is shutdown.
> >
> > I have had this issue for a long time, through tomcat 7, 8.5 and 9,
> > and multiple versions of mysql server and the connector., and also
> > using the commons pool. On my sandboxes I tend to reload a lot as I
> > deploy exploded, rather than deploy an entire WAR each time.
> >
> > Googling led to multiple old bug reports filed w/ MySQL about the
> > cleanup thread, but nothing that would point to me doing anything
> > different than what I am doing. Are these lingering connections just a
> > fact of doing business with MySQL, or is there something I need to
> > configure/change in code to clean this up?
> >
> >
> > Thanks
> >
> > Chris
> >
> > ---------------------------------------------------------------------
> > 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: lingering mysql connections
Posted by Greg Huber <gr...@gmail.com>.
I resolved the same using this link
https://stackoverflow.com/questions/11872316/tomcat-guice-jdbc-memory-leak
I created the ContextFinalizer to cleanup on shut down.
Also, I had loads of strange sql issues which were resolved by switching to
maria db.
Cheers Greg
On Thu, 13 Dec 2018 at 20:51, Chris Cheshire <ya...@gmail.com> wrote:
> Tomcat 9.0.12, Debian, MySQL Server 5.7.23, Connector/J 5.1.46
>
> I am trying to fix a lingering database connection problem. When I
> reload a context via the tomcat manager, connections to the
> datasources are not being released in mysql. They are still on the 30
> second activity cycle from the eviction thread. I can see this via
> 'show processlist' in the mysql client - the 'time' column resets at
> 30, and each connection has unique process ids that I can track per
> reload.
>
> I have tomcat home and base split (multiple instances of tomcat across
> different users), with the connector/j jar in tomcat_base/lib.
>
> In my webapp's META-INF/context.xml I have 3 different datasources,
> config, data, sched. All have configuration like :
>
> <Resource name="jdbc/config"
> auth="Container"
> type="javax.sql.DataSource"
> driverClassName="com.mysql.jdbc.Driver"
>
> url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=true&characterEncoding=utf8&useSSL=false"
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> username="$USER$"
> password="$PASSWORD$"
> maxActive="2"
> maxIdle="1"
> minIdle="1"
> initialSize="1"
> maxWait="30000"
> removeAbandoned="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"
> validationQuery="/* ping */"
> testOnBorrow="true"
> testWhileIdle="true"
> timeBetweenEvictionRunsMillis="30000"
> defaultAutoCommit="false"
> defaultIsolation="READ_COMMITTED" />
>
> Connections are obtained via
>
> Connection dbConn = ((DataSource)new
> InitialContext().lookup(resourceName)).getConnection()
>
> Connections are all closed via
>
> dbConn.close()
>
> (Simplified greatly, there's convenience methods with exception
> handling in there)
>
>
>
> In contextDestroyed() of a ServletContextListener I am calling
>
> AbandonedConnectionCleanupThread.checkedShutdown();
>
> I have an initialization servlet that must be manually called before
> the webapp is fully online - it is used to load encrypted
> configuration from the conf datasource. It does not touch the data
> datasource, only conf and sched by virtue of starting the quartz
> scheduler which is configured to use this datasource.
>
> My observation are :
> * It doesn't matter what order I declare the datasources, they are
> always getting opened in the order sched, conf, data (judging by
> increased thread/process ids in mysql).
> * When I start tomcat, I get 3 open connections in mysql, 1 to each of
> the databases referenced by the datasources. If I immediately reload
> via the manager, all 3 connections are destroyed and 3 new ones are
> opened.
> * Once I call the initialization servlet, and subsequently reload the
> web app via the manager, previous connections to conf and sched are
> still open in mysql, as well as new ones
> * If I access any part of the web app that uses the data datasource,
> those connections now also linger.
> * Once I stop tomcat (and the JVM) all lingering connections are
> closed in mysql.
> * If I put the connector/j jar in my WEB-INF/lib instead of
> tomcat_base/lib, I get the following warning on reload/shutdown
>
> 13-Dec-2018 20:19:53.968 WARNING [ajp-nio-8019-exec-3]
> org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc
> The web application [ct] registered the JDBC driver
> [com.mysql.jdbc.Driver] but failed to unregister it when the web
> application was stopped. To prevent a memory leak, the JDBC Driver has
> been forcibly unregistered.
>
> * There are no warnings or errors in catalina.out about abandoned
> connections during runtime, reload or shutdown of the tomcat instance.
> I have every connection being closed after use. (I have seen the
> warnings when I have made a code mistake however, so the thread is
> doing its job).
> * If I remove the abandoned connection and eviction thread
> configuration entirely, the connection activity timer resets on a 5
> second cycle in mysql. If I explicitly change
> timeBetweenEvictionRunsMillis to -1, the activity timer in mysql never
> resets (which implies that the default is not -1 as the documentation
> suggests)
>
> So it seems that once a datasource is accessed, connections to it
> perpetually linger in mysql until the JVM is shutdown.
>
> I have had this issue for a long time, through tomcat 7, 8.5 and 9,
> and multiple versions of mysql server and the connector., and also
> using the commons pool. On my sandboxes I tend to reload a lot as I
> deploy exploded, rather than deploy an entire WAR each time.
>
> Googling led to multiple old bug reports filed w/ MySQL about the
> cleanup thread, but nothing that would point to me doing anything
> different than what I am doing. Are these lingering connections just a
> fact of doing business with MySQL, or is there something I need to
> configure/change in code to clean this up?
>
>
> Thanks
>
> Chris
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>