Re: lingering mysql connections
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 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 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 : > > > >> auth="Container" > > type="javax.sql.DataSource" > > driverClassName="com.mysql.jdbc.Driver" > > > > url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=truecharacterEncoding=utf8useSSL=false" > > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" > > username="$USER$" > > password="$PASSWORD$" > > maxActive="2" > > maxIdle="1" > > minIdle="1" > > initialSize="1" > > maxWait="3" > > removeAbandoned="true" > > removeAbandonedTimeout="60" > > logAbandoned="true" > > validationQuery="/* ping */" > > testOnBorrow="true" > > testWhileIdle="true" > > timeBetweenEvictionRunsMillis="3" > > 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
Re: lingering mysql connections
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 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 : > >auth="Container" > type="javax.sql.DataSource" > driverClassName="com.mysql.jdbc.Driver" > > url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=truecharacterEncoding=utf8useSSL=false" > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" > username="$USER$" > password="$PASSWORD$" > maxActive="2" > maxIdle="1" > minIdle="1" > initialSize="1" > maxWait="3" > removeAbandoned="true" > removeAbandonedTimeout="60" > logAbandoned="true" > validationQuery="/* ping */" > testOnBorrow="true" > testWhileIdle="true" > timeBetweenEvictionRunsMillis="3" > 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
Re: lingering mysql connections
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 님이 작성: > 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 : > >auth="Container" > type="javax.sql.DataSource" > driverClassName="com.mysql.jdbc.Driver" > > url="jdbc:mysql://localhost:3306/$DBNAME$?useUnicode=truecharacterEncoding=utf8useSSL=false" > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" > username="$USER$" > password="$PASSWORD$" > maxActive="2" > maxIdle="1" > minIdle="1" > initialSize="1" > maxWait="3" > removeAbandoned="true" > removeAbandonedTimeout="60" > logAbandoned="true" > validationQuery="/* ping */" > testOnBorrow="true" > testWhileIdle="true" > timeBetweenEvictionRunsMillis="3" > 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
