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