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
