I resolved the same using this link


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&amp;characterEncoding=utf8&amp;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

Reply via email to