Too Many Connections!
Hi,
I have a JSF application that is using hibernate to persist data to a MySQL database, but after doing a few small operations in the application I get the error:
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
A google search quickly showed me that this is a pretty common error when working with database connections, but I'm yet to find a real solution.
The application is running on Tomcat 5.5.x using Hibernate 3.x to persist to a MySQL 5.x database.
For one, I thought that I might have a connection leak somewhere in the application, but the session.close() is called every time I finish an operation. Are there any particular methods I might be able to call to prevent this?
I see that there is a way to increase the maximum connections to the mysql databse. Does anyone know how this is done? (I'm running under OS X) I'm hoping that this might solve the problem, though I wouldn't be sure how much to increase to.
I am not using any connection pooling, which from what I understand might be a big help in this situation by recycling the connections. What are the steps to take to implement connection pooling in a JSF application? Is connection pooling more or less necessary in a proper web app or is it simply recommended in heavy use situations?
Also, I am not using a helper class to open connections, could this be an issue at all? What are the advantages to using such a class? The reason I haven't yet is just to keep things simple, but if there's nothing to lose I feel I should just go for it. Are there any examples of such a class on the internet I should use? so far, a typical save operation of mine looks like this:
SessionFactory sessionFactory =new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
session.saveOrUpdate(estInitObj);
tx.commit();
session.close();
I'm quite puzzled on this issue.
Any help is greatly appreciated.
[2204 byte] By [
mon.goosea] at [2007-11-27 10:49:56]
# 1
Hello,
Probably you has not secured the closing of all connections. Be sure that after each connection (open), must be closed. f.g closing the resultset or connection in finally block.
# 2
I have looked at every instance of opening a connection with
sessionFactory.openSession()
and later on I follow with a session.close()
Still, the problem is there.
# 3
Just because the session.close() comes after the openSession() in your code, it doesn't follow that it is always called. Unless you have it in a finally() block. I bet you don't, and that exceptions are thrown that cause it to not be called.
# 4
Here is my hibernate.cfg.xml file tag for datasource connection.
mySql would look similiar (you'll have to look it up). Note connectionpool has to be creater than 0 or 1 (typically 3 to 5 is enougth).
You definately need a connection pool. DriverManager or other non conection pool should never be used (its just not done anymore!). That is probably your problem. For Oracle, I went to the oracle home page and got the JDBC (pooled) driver ojdbc14.jar, put it in my project's lib folder, added it to the classpath. Put your code to open session in a try/catch/finally block, close the session in the finally statement. Dont put datasource tag in tomcat/config/server.xml or context.xml file since its specified in hibernate.cfg.xml file.
<session-factory>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@xxxxxxxxxxxxx</property>
<property name="connection.username">xxxxxxx</property>
<property name="connection.password">xxxxxx</property>
<property name="connection.pool_size">3</property>
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="show_sql">f=true</property>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Person.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/House.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Pet.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Species.hbm.xml"/>
</session-factory>
By the way, you may have to restart your database to clear those connections, just restarting your application is proably not good enough.
Also, what do you mean by 'using a utility to open a connection? you should not have any connection code such as connection.getConnection() or connection.close() anywhere in your code. Hibernate does all that automatically and you shouldn't mix hibernate with getConnection()/close(). It can be done, but its takes advanced knowledge. Let hibernate handle ALL database read/write tasks.
# 5
Here is my hibernate.cfg.xml file tag for datasource connection.
mySql would look similiar (you'll have to look it up). Note connectionpool has to be creater than 0 or 1 (typically 3 to 5 is enougth).
You definately need a connection pool. DriverManager or other non conection pool should never be used (its just not done anymore!). That is probably your problem. For Oracle, I went to the oracle home page and got the JDBC (pooled) driver ojdbc14.jar, put it in my project's lib folder, added it to the classpath. Put your code to open session in a try/catch/finally block, close the session in the finally statement. Dont put datasource tag in tomcat/config/server.xml or context.xml file since its specified in hibernate.cfg.xml file.
<session-factory>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@xxxxxxxxxxxxx</property>
<property name="connection.username">xxxxxxx</property>
<property name="connection.password">xxxxxx</property>
<property name="connection.pool_size">3</property>
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="show_sql">f=true</property>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Person.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/House.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Pet.hbm.xml"/>
<mapping resource="mil/jfcom/jwfc/springhibernatedemo/service/conf/hibernate/Species.hbm.xml"/>
</session-factory>
By the way, you may have to restart your database to clear those connections, just restarting your application is proably not good enough.
Also, what do you mean by 'using a utility to open a connection? you should not have any connection code such as connection.getConnection() or connection.close() anywhere in your code. Hibernate does all that automatically and you shouldn't mix hibernate with getConnection()/close(). It can be done, but its takes advanced knowledge. Let hibernate handle ALL database read/write tasks.
Thanks for the help.
I managed to fix my initial problem of the too many connections error by adding the line sessionFactory.close(); before the session.close() every time. This did the trick and I haven't seen the too many connections error since. I still need to get the connection pool running properly though, and not opening hibernate sessions in a try, catch, finally block seems very unelegant to me, so I wish to change my code to do that too.
I must be doing something wrong however, since my try catch finally block isn't functioning as it should. the session is out of scope for the finally block and so can't close it. Here's an example databse call:
try {
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
String hqlString = "FROM EstimateInit as e where e.projId like '" + projObj.getId() + "'";
Query query = session.createQuery(hqlString);
Iterator iter = query.iterate();
if (iter.hasNext() == true) {
estInitExists = true;
} else {
estInitExists = false;
}
} catch (Exception he) {
System.out.println("Hibernate has thrown an exception: " + he);
} finally {
sessionFactory.close();// Out of scope
session.close();// Out of scope
}
Again though, I'm doing everything inside the class that's managing my objects. Most people seem to have configured a seperate class to open and close sessions?
Also, I'm not sure about how to correctly configure my hibernate.cfg.xml file. George, your suggestion was helpful, but I have a few questions.
This is what my hibernate configuration file looks like so far:
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/estimating01</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<mapping resource="beans/EstimateInit.hbm.xml"/>
<mapping resource="beans/Project.hbm.xml"/>
</session-factory>
Since I already have hibernate.connection.driver_class, do I need to also have connection.driver_class as in your example? I assume these are the same…
Why is there a naming discrepency between our two files?
I only added the extra lines, and now my file looks like this:
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/estimating01</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.pool_size">5</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="show_sql">f=true</property>
<mapping resource="beans/EstimateInit.hbm.xml"/>
<mapping resource="beans/Project.hbm.xml"/>
</session-factory>
Is this all there is to it and hibernate manages the rest or have I just gotten over the smallest hurdle? This seems too painless to have allowed connection pooling. Also, the application does not run any faster, and over all it feels a bit sluggish so I doubt I've done anything to it. There is something that I read about using c3p0, can anyone elaborate?
Thank you all for your time!
# 6
I believe I see your 'slow application' problem in your code.
You are calling this line each and every time in your try block to read/write data to the database:
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
I believe that line is very slow. Put the following class (below) in your project that instansiates the sessionFactory once, then use its getSessionFactory for your code (the class is from 'Hibernate in Action' book).
I strongly suggest reading a book such as 'Hibernate in Action' since I dont believe hibernate is something programmers can pick up by reading some on-line tutorials and code examples. Even with the book, I had to refactor my persistent layer over and over and over again to figure out what the book left out.
I cant help with your configuration since I dont use mySql.
Also this link may be of help:
http://forum.java.sun.com/thread.jspa?threadID=5196191&messageID=9774366#9774366
package springhibernatedemo.util.hibernate;
import org.hibernate.*;
import org.hibernate.cfg.*;
/** a singleton to obtain a sessionFactory for hibernate queries
* @throws ExceptionInInitializerError
*/
public class HibernateUtil {
private static final SessionFactory sessionFactory;
static {
try {
// Create the SessionFactory from hibernate.cfg.xml
sessionFactory = new Configuration().configure().buildSessionFactory();
} catch (Throwable ex) {
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed:::" + ex);
throw new ExceptionInInitializerError(ex);
}
}
/* *********************************************************************** */
public static SessionFactory getSessionFactory() {
return sessionFactory;
}
/* *********************************************************************** */
}
