What can causes "Too many connection" for Hibernate 4

Question

I'm using Hibernate 4 and mysql for my current website, after I tried to keep refreshing the page many time in 2 web browser's tab, the server gone down with error log:

ERROR: Data source rejected establishment of connection,  message from server: "Too many connections"
org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:68)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
    at hibernate.dao.StockDao.getWarehouseInStock(StockDao.java:132)
    at Utils.Common.updateStockHistorical(Common.java:501)
    at Controller.StockServlet.processRequest(StockServlet.java:67)
    at Controller.StockServlet.doPost(StockServlet.java:116)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at Controller.ServletFilter.doFilter(ServletFilter.java:134)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:879)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:600)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1703)
    at java.lang.Thread.run(Thread.java:722)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.GeneratedConstructorAccessor70.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1104)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2412)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2230)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor62.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:334)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:190)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:173)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:279)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
    ... 24 more

And this is my config for hibernate:

<!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.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/wstrader2?characterEncoding=UTF-8</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>
    <property name="hibernate.current_session_context_class">jta</property>
    <mapping resource="hibernate/dto/Warehouse.hbm.xml"/>
    <mapping resource="hibernate/dto/Exchange.hbm.xml"/>
    <mapping resource="hibernate/dto/Packing.hbm.xml"/>
    <mapping resource="hibernate/dto/GradeMaster.hbm.xml"/>
    <mapping resource="hibernate/dto/MarketFobDiff.hbm.xml"/>
    <mapping resource="hibernate/dto/Arbitration.hbm.xml"/>
    <mapping resource="hibernate/dto/Quality.hbm.xml"/>
    <mapping resource="hibernate/dto/Region.hbm.xml"/>
    <mapping resource="hibernate/dto/City.hbm.xml"/>
    <mapping resource="hibernate/dto/Contract.hbm.xml"/>
    <mapping resource="hibernate/dto/HistoricalBasis.hbm.xml"/>
    <mapping resource="hibernate/dto/Country.hbm.xml"/>
    <mapping resource="hibernate/dto/Origin.hbm.xml"/>
    <mapping resource="hibernate/dto/WarehouseReceipt.hbm.xml"/>
    <mapping resource="hibernate/dto/DailyBasis.hbm.xml"/>
    <mapping resource="hibernate/dto/User.hbm.xml"/>
    <mapping resource="hibernate/dto/Movement.hbm.xml"/>
    <mapping resource="hibernate/dto/PaymentTerm.hbm.xml"/>
    <mapping resource="hibernate/dto/ContractTerm.hbm.xml"/>
    <mapping resource="hibernate/dto/CompanyMaster.hbm.xml"/>
    <mapping resource="hibernate/dto/ProcessingOrder.hbm.xml"/>
    <mapping resource="hibernate/dto/Stock.hbm.xml"/>
    <mapping resource="hibernate/dto/HistoricalStock.hbm.xml"/>
    <mapping resource="hibernate/dto/Menu.hbm.xml"/>
    <mapping resource="hibernate/dto/Authorization.hbm.xml"/>
    <mapping resource="hibernate/dto/Page.hbm.xml"/>
    <mapping resource="hibernate/dto/ContractPriceFixation.hbm.xml"/>
    <mapping resource="hibernate/dto/FuturesContract.hbm.xml"/>
    <mapping resource="hibernate/dto/FutureCloseOut.hbm.xml"/>
    <mapping resource="hibernate/dto/Currency.hbm.xml"/>
    <mapping resource="hibernate/dto/FuturesContractArchive.hbm.xml"/>
    <mapping resource="hibernate/dto/FutureCloseOutArchive.hbm.xml"/>
    <mapping resource="hibernate/dto/ScreenMaster.hbm.xml"/>
    <mapping resource="hibernate/dto/QualityMaster.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

And here is my HibernateUtil.java:

public class HibernateUtil {
    private static final SessionFactory ourSessionFactory;
    private static ServiceRegistry serviceRegistry;
    private static Configuration configuration = new Configuration().configure();

    static {
        try {
            serviceRegistry = new ServiceRegistryBuilder().applySettings(
                    configuration.getProperties()).buildServiceRegistry();
            ourSessionFactory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static Session getSession() throws HibernateException {
        return ourSessionFactory.openSession();
    }
}

Could someone find out the reason or tell me the conditions cause error "Too many connection", and org.hibernate.exception.JDBCConnectionException: Could not open connection I try to find related topics but still not found the answer for my problem

Thanks for reading this topic.

UPDATED: my sample Dao.java:

public class CityDAO {

    private Session session = null;

    public CityDAO() {
        session = HibernateUtil.getSession();
    }

    public ArrayList<City> getAllCities() {
        try {
            session.getTransaction().begin();
            Criteria crit = session.createCriteria(City.class);
            ArrayList<City> cities = (ArrayList<City>)crit.list();
            session.flush();
            session.getTransaction().commit();
            return cities;
        } catch (Exception e) {
            if (session.getTransaction().isActive()) {
                session.getTransaction().rollback();
            }
            e.printStackTrace();
        }
        return null;
    }

    public City getCityById(int id){
        try {
            session.getTransaction().begin();
            Criteria crit = session.createCriteria(City.class);
            crit.add(Restrictions.eq("id", id));
            City city = (City)crit.uniqueResult();
            session.flush();
            session.getTransaction().commit();
            return city;
        } catch (Exception e) {
            if (session.getTransaction().isActive()) {
                session.getTransaction().rollback();
            }
            e.printStackTrace();
        }
        return null;
    }
}

Answer 1

I found solution for my problem. Because my web application use servlet - the old fashioned one. So I try this:

https://community.jboss.org/wiki/OpenSessionInView

And it's work for me.

Thanks you guys for give me advices :D

Answer 2

You have one open session on every instance of your DAO class, and even if you set up connection pool, it will not much help.

You made something like many-sessions-per-application anti-pattern (a bit different of session-per-application). That anti-pattern has problems, described in Hiberhate Guide

A Session is not thread-safe. Things that work concurrently, like HTTP requests, session beans, or Swing workers, will cause race conditions if a Session instance is shared.

An exception thrown by Hibernate means you have to rollback your database transaction and close the Session immediately

Additional, because you haven't connection pool, application cannot detect situation, when connection was killed by server.

Answer 3

I assume, that the exception is thrown by MySql. Either you increase the maxconnections there or you check your source if there is a resource leak. For example, don't create connections instances each request or close the connection after every request and so on.