Clever Geek Handbook

Tomcat JDBC Pool: Too Many Connections

I am using Tomcat JDBC pooling MySQL DB connection pooling in my web application. The JDBC pool is declared as a resource in the context.xml of the application (not globally). I am developing this web application in Eclipse IDE. So when I make changes to the code, eclipse forces the Tomcat server to reload the context.

But the problem is that when the context reloads, tomcat creates a JDBC pool with new connections without releasing the old pool. And after a few code changes, tomcat eventually ran out of maximum connection limit with MySQL server and tomcat starts showing "Too many connections" error when reloading context.

My context.xml file is like:

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource
        name="jdbc/myDB"
        auth="Container"
        type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        username="root"
        password="admin"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/myDB?allowMultiQueries=true"
        defaultAutoCommit="false"
        initialSize="20"
        maxActive="50"
        maxIdle="30"
        minIdle="15"
        maxWait="5000"
        testOnBorrow="true"
        testWhileIdle="true"
        validationQuery="SELECT 1"
        timeBetweenEvictionRunsMillis="35000"
        minEvictableIdleTimeMillis="55000"
        removeAbandoned="true"
        removeAbandonedTimeout="3600"
        logAbandoned="true"
        validationInterval="35000"
    />
</Context>

My web.xml is like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>MyApp</display-name>

  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>

  <resource-ref>
      <description>MyApp DB Connection</description>
      <res-ref-name>jdbc/myDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

  <context-param>
    <param-name>data-source-lookup-name</param-name>
    <param-value>java:comp/env/jdbc/myDB</param-value>
  </context-param>

  <listener>
    <listener-class>com.project.listeners.AppListener</listener-class>
  </listener>
</web-app>

App Listener is like:

package com.project.listeners;

import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Enumeration;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.log4j.Logger;

import com.mysql.jdbc.AbandonedConnectionCleanupThread;

public class AppListener implements ServletContextListener {
    private static Logger logger = Logger.getLogger(AppListener.class);

    @Override
    public void contextInitialized(ServletContextEvent event) {
        logger.info("Application context initialing");

        ServletContext context = event.getServletContext();

        DBObjectNames.dataSourceLookupName = context.getInitParameter("data-source-lookup-name");
        if(DBObjectNames.dataSourceLookupName == null){
            logger.fatal("data-source-lookup-name init parameter not supplied, using default: java:comp/env/jdbc/myDB");
            DBObjectNames.dataSourceLookupName = "java:comp/env/jdbc/myDB";
        }
        DBObjectNames.dataSourceLookupName = DBObjectNames.dataSourceLookupName.trim();
        logger.info("data-source-lookup-name set to: " + DBObjectNames.dataSourceLookupName);

        logger.info("Application context initialed");
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        logger.info("Application context destroying");

        try {
            logger.info("Shutting down AbandonedConnectionCleanupThread");
            AbandonedConnectionCleanupThread.shutdown();
            logger.info("Shut down AbandonedConnectionCleanupThread");
        } catch (Throwable t) {
            logger.fatal("Error while shutting down AbandonedConnectionCleanupThread", t);
        }

        Enumeration<Driver> drivers = DriverManager.getDrivers();
        while (drivers.hasMoreElements()) {
            Driver driver = drivers.nextElement();
            try {
                logger.info("Deregistering driver: " + driver);
                DriverManager.deregisterDriver(driver);
                logger.info("Deregistered driver: " + driver);
            } catch (Throwable t) {
                logger.fatal("Error while deregistering driver: " + driver, t);
            }
        }

        logger.info("Application context destroyed");
    }
}

And I get exceptions like:

Oct 30, 2014 4:16:54 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/myapp] is completed
Oct 30, 2014 4:17:04 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/myapp] has started
2014-10-30 16:17:04,497 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context destroying
2014-10-30 16:17:04,498 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Shutting down AbandonedConnectionCleanupThread
2014-10-30 16:17:04,498 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Shut down AbandonedConnectionCleanupThread
2014-10-30 16:17:04,498 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context destroyed
Oct 30, 2014 4:17:04 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
2014-10-30 16:17:04,660 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context initialing
2014-10-30 16:17:04,661 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - data-source-lookup-name set to: java:comp/env/jdbc/myDB
2014-10-30 16:17:04,662 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context initialed
Oct 30, 2014 4:17:04 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/myapp] is completed
Oct 30, 2014 4:17:14 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/myapp] has started
2014-10-30 16:17:14,665 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context destroying
2014-10-30 16:17:14,666 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Shutting down AbandonedConnectionCleanupThread
2014-10-30 16:17:14,666 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Shut down AbandonedConnectionCleanupThread
2014-10-30 16:17:14,666 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context destroyed
Oct 30, 2014 4:17:14 PM org.apache.tomcat.jdbc.pool.ConnectionPool init
SEVERE: Unable to create initial connections of pool.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1037)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
    at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:307)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:553)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:241)
    at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:829)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:166)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1084)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5120)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3821)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
    at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5576)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
    at java.lang.Thread.run(Unknown Source)

Oct 30, 2014 4:17:14 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
WARNING: Connection has been abandoned PooledConnection[com.mysql.jdbc.JDBC4Connection@1090e8d6]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1052)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:704)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:553)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:241)
    at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:829)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:166)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1084)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5120)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3821)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
    at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5576)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
    at java.lang.Thread.run(Unknown Source)

Oct 30, 2014 4:17:14 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
WARNING: Connection has been abandoned PooledConnection[com.mysql.jdbc.JDBC4Connection@c572aa4]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1052)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:704)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:553)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:241)
    at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:829)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:166)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1084)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5120)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3821)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
    at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5576)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
    at java.lang.Thread.run(Unknown Source)

Oct 30, 2014 4:17:14 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
WARNING: Connection has been abandoned PooledConnection[com.mysql.jdbc.JDBC4Connection@72e8fcdd]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1052)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:704)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:553)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:241)
    at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:829)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:166)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1084)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5120)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3821)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
    at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5576)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
    at java.lang.Thread.run(Unknown Source)

Oct 30, 2014 4:17:14 PM org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1037)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
    at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:307)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:553)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:241)
    at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:829)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:166)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1084)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5120)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3821)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
    at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5576)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
    at java.lang.Thread.run(Unknown Source)

Oct 30, 2014 4:17:14 PM org.apache.catalina.core.NamingContextListener addResource
WARNING: Failed to register in JMX: javax.naming.NamingException: Data source rejected establishment of connection,  message from server: "Too many connections"
Oct 30, 2014 4:17:14 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
2014-10-30 16:17:14,788 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context initialing
2014-10-30 16:17:14,789 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - data-source-lookup-name set to: java:comp/env/jdbc/myDB
2014-10-30 16:17:14,791 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO  com.project.listeners.AppListener  - Application context initialed
Oct 30, 2014 4:17:14 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/myapp] is completed

Additional Information:

This happens without testing the application, i.e. I am not running a web app to open connections, still just writing code, so you usually don't need to open a connection and maintain it without closing it.

Using Apache Tomcat: Version 8.0. and the release of Eclipse Luna.

Please help me with this.

EDIT

Every time the context is reloaded, I see more than 15 open DB connections in MySQL Workbench before using the maximum connection limit of 151 connections to the MySQL server.

+4


source to share


4 answers


I think you need to add closeMethod = "close" to your resource in context.xml. Then Tomcat should close your DB connections on context reload.

Modified context.xml:



<?xml version="1.0" encoding="UTF-8"?>
 <Context>
    <Resource
        name="jdbc/myDB"
        auth="Container"
        type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        username="root"
        password="admin"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/myDB?allowMultiQueries=true"
        defaultAutoCommit="false"
        initialSize="20"
        maxActive="50"
        maxIdle="30"
        minIdle="15"
        maxWait="5000"
        testOnBorrow="true"
        testWhileIdle="true"
        validationQuery="SELECT 1"
        timeBetweenEvictionRunsMillis="35000"
        minEvictableIdleTimeMillis="55000"
        removeAbandoned="true"
        removeAbandonedTimeout="3600"
        logAbandoned="true"
        validationInterval="35000"
        closeMethod="close"
    />
</Context>
+10


source


Delete factory = "org.apache.tomcat.jdbc.pool.DataSourceFactory"

Last update to apache-tomcat-8.0.28 added "factory = ..." to DataSource config, then oracle database connections went crazy. After that, the communication sessions went to the noraml status. I tried to figure out the reason, but I couldn't. This comment might be helpful to someone else.

Following is the data source configuration from Apache TomEE http://tomee.apache.org/datasource-config.html . This should be a good reference for anyone looking for a solution.



<Resource id="myDataSource" type="javax.sql.DataSource">
    accessToUnderlyingConnectionAllowed = false
    alternateUsernameAllowed = false
    connectionProperties = 
    defaultAutoCommit = true
    defaultReadOnly = 
    definition = 
    ignoreDefaultValues = false
    initialSize = 0
    jdbcDriver = org.hsqldb.jdbcDriver
    jdbcUrl = jdbc:hsqldb:mem:hsqldb
    jtaManaged = true
    maxActive = 20
    maxIdle = 20
    maxOpenPreparedStatements = 0
    maxWaitTime = -1 millisecond
    minEvictableIdleTime = 30 minutes
    minIdle = 0
    numTestsPerEvictionRun = 3
    password = 
    passwordCipher = PlainText
    poolPreparedStatements = false
    serviceId = 
    testOnBorrow = true
    testOnReturn = false
    testWhileIdle = false
    timeBetweenEvictionRuns = -1 millisecond
    userName = sa
    validationQuery = 
</Resource>
Run codeHide result


+5


source


If we have this configured using the hibernate properties file, I think this would be sufficient:

hibernate.transaction.auto_close_session
+1


source


I have a similar problem, but my environment is slightly different.

I have a springboot application and MariaDB 10.34.

I have defined all properties in application- *. Properties .

I commented (you can also delete) all settings start with spring.datasource.tomcat

and it works.

I think someone on my team signed up without checking the code. :);)

Link: https://github.com/HomoEfficio/dev-tips/blob/master/%EA%B0%84%ED%97%90%EC%A0%81%EC%9D%B8%20Too%20many%20connections % 20% 97% EU% 90% EB% 9F% AC.md

(If you don't know Korean, you can use https://translate.google.com/

: D: D)

0


source






More articles:


All Articles