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 <gregh3...@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 <yahoono...@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-unsubscr...@tomcat.apache.org > > For additional commands, e-mail: users-h...@tomcat.apache.org > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
