com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

Question

My simple web-app sometimes crashes when navigating around the site, the error message I've gotten from the logs are found below. I have absolutely no idea of what is causing this and would be very thankful for any tips that'll lead me in the right direction.

HTTP Status 500 - org.hibernate.exception.JDBCConnectionException: Cannot open connection

type Exception report

message org.hibernate.exception.JDBCConnectionException: Cannot open connection

description The server encountered an internal error that prevented it from fulfilling this request.

exception
org.apache.jasper.JasperException: org.hibernate.exception.JDBCConnectionException: Cannot open connection
    org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:549)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:470)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause

org.hibernate.exception.JDBCConnectionException: Cannot open connection
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
    org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
    org.hibernate.loader.Loader.doQuery(Loader.java:696)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    org.hibernate.loader.Loader.doList(Loader.java:2228)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    org.hibernate.loader.Loader.list(Loader.java:2120)
    org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
    org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
    com.html.XmlSetting.getListOfhtml_connect(XmlSetting.java:55)
    com.html.XmlSetting.removeXml(XmlSetting.java:121)
    com.html.MarginSetting.removeXml(MarginSetting.java:291)
    org.apache.jsp.check_jsp._jspService(check_jsp.java:183)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
    sun.reflect.GeneratedConstructorAccessor153.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.Util.getInstance(Util.java:381)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:985)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:894)
    com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3808)
    com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1256)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2032)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:729)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.GeneratedConstructorAccessor145.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
    com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:283)
    java.sql.DriverManager.getConnection(DriverManager.java:620)
    java.sql.DriverManager.getConnection(DriverManager.java:169)
    org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
    org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
    org.hibernate.loader.Loader.doQuery(Loader.java:696)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    org.hibernate.loader.Loader.doList(Loader.java:2228)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    org.hibernate.loader.Loader.list(Loader.java:2120)
    org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
    org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
    com.html.XmlSetting.getListOfhtml_connect(XmlSetting.java:55)
    com.html.XmlSetting.removeXml(XmlSetting.java:121)
    com.html.MarginSetting.removeXml(MarginSetting.java:291)
    org.apache.jsp.check_jsp._jspService(check_jsp.java:183)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

I m using Apache Tomcat 7

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>

        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/htmlcleaner</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.connection.username">root</property>

        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

        <property name="connection.pool_size">1000</property>

       <!--  <property name="show_sql">true</property> -->
        <mapping class="com.model.html_connect"/>

    </session-factory>
</hibernate-configuration>

Answer 1

Its clearly seen hibernate is not able to create or open connection with database there could be many reason some them are

1. Your not closing session in your program after using session
2. Connection pool is small and too many threads are accessing the pool.

Kindly paste your configuration and exception point program

Add

<property name="hibernate.connection.release_mode">on_close</property>

property and call session.close(); at the end of your method

Answer 2

It looks like you are leaking connections big time. Are you closing your hibernate sessions when you are done with them?

You could also try and fiddle about with hibernate.connection.release_mode , by default, this is set to auto and it will release the connection when you close the session. However, you also have the options here to use after_transaction or after_statement (its rather self explanatory when it will release the connections for each of the two :P).

With that said though, you do need to find out where you leaks are :)

Answer 3

The possible reason for this is that your web application is possibly leaking connections or the connection pool size is very small due to which all the open connections are currently being used and the new connection cannot be obtained.

I am not sure about Tomcat but in JBoss, we generally refer to datasource file and you can set the connection pool size as

   <max-pool-size>80</max-pool-size>

You can even log into the administrative console of Tomcat and check how many connections are actually in use whenever you get this exception. The administrative console will give you the maximum connection objects ever created and a variety of other information related to connection pool.