The Broken Database Connection
We had an issue in one of the project which uses Hibernate for persistence, MySQL for database and JBoss as application server with JNDI connection pooling to the database. The problem was identified when the first iteration of the project went for the QC testing. It stuck after our test person tested the application for a day and came back next day morning and started to continue the testing in the morning. A pile of exception threw up on the screen and none of the links that required bringing data from the database worked. It all threw up exceptions. We had to restart the server to make it start working. We went into the log file and found out an exception constantly being thrown. Here are the first few lines of the exception.
com.mysql.jdbc.CommunicationsException:
Communications link failure due to underlying exception:
java.net.SocketException
MESSAGE: Broken pipe
STACKTRACE:
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
…
…
…
The problem seemed to be the application idle time of more than 8 hrs during night, when all the connections in the pool was cut off by MySQL. This value is one of the system variable settings in MySQL (wait_timeout - 28800 seconds which translates to 8 hours). Obviously changing this setting to a larger number is not the solution. But the program that handles the connections should be smart enough to detect an idle timeout connection close and re-establish the connection. The problem is not peculiar or new, there had been lot of discussions on the same. A google search on the first line of the stacktrace will result in so many discussions around this.
Apparently none of the solutions worked for us and we could not find a solution that addressed this combination Hibernate + MySQL (MySQL Java Connector) + JBoss JNDI. Apache Commons DBCP and C3P0 are couple of frameworks that help in connection pooling. These two frameworks have settings that can reestablish a connection when it is cut. But clueless we were why it didn’t work for us and we tried all sorts of combination.
Finally we decided to take care of the problem by ourselves through code. I am sure when you use Hibernate, the HibernateUtil program that is commonly available has methods to get an Hibernate Session. We had to tweak the openSession method and catch GenericJDBCException and include code to trap the broken pipe condition by checking the corresponding SQL error code and attempt a fresh connection. Here is the openSession and reconnect method from HibernateUtil.java.
Hope this helps if anyone is facing the same issue and any of the solution in the above links does not work.
Giridhar Said,
January 5, 2007 @ 6:57 pm
Hi,
I am facing the same problem as you’ve described. I’ve never used Hibernate. So how should I proceed? Do I need to use Hibernate just for this? Excuse me if this is a very basic question. I have no idea what a Hibernate is!
Thanks for your time and help,
Giridhar
Ramesh Said,
January 5, 2007 @ 7:21 pm
Hi Giridhar,
You dont need to use Hibernate to fix this.
Just take a look at the code that is there in the post. All you have to do is pick up the exceptions wherever you are dealing with the connection.
Catch the GenericJDBCException and handle it the way its there in the code to reconnect and you must be set.
Let me know how it goes.
Ramesh
Rashid Said,
January 6, 2007 @ 9:34 pm
Hi Sridhar
Wonderful fix for the bug. i have the same issue and i was so frustrated and right now i am instantiating my objects using conventional jdbc technique i have the hibernate util class which i am using right now here it is
public class HibernateUtil {
private static String CONFIG_FILE_LOCATION = “/hibernate.cfg.xml”;
private static final ThreadLocal threadLocal = new ThreadLocal();
private static Configuration configuration = new Configuration();
private static org.hibernate.SessionFactory sessionFactory;
private static String configFile = CONFIG_FILE_LOCATION;
static {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
private HibernateUtil() {
}
public static Session getSession() throws HibernateException {
Session session = (Session) threadLocal.get();
if (session == null || !session.isOpen()) {
if (sessionFactory == null) {
rebuildSessionFactory();
}
session = (sessionFactory != null) ? sessionFactory.openSession()
: null;
threadLocal.set(session);
}
try{
final Transaction transaction = session.beginTransaction();
session.createSQLQuery( “select 1″ ).list();
transaction.commit();
}catch(GenericJDBCException){
//check for sql state if 08S01 call get session again for 5 attempts
}
return session;
}
public synchronized static void rebuildSessionFactory() {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeSession() throws HibernateException {
Session session = (Session) threadLocal.get();
threadLocal.set(null);
if (session != null) {
session.close();
}
}
public static org.hibernate.SessionFactory getSessionFactory() {
return sessionFactory;
}
public static void setConfigFile(String configFile) {
HibernateUtil.configFile = configFile;
sessionFactory = null;
}
public static Configuration getConfiguration() {
return configuration;
}
Should i be catching GenericJDBCException in my openSesssion mehtod and recursively call open session again for five times. (i just added some comments not the acutal code yet) i am a bit confused since you code is a bit differnt from mine. Please let me know fi you can guide me on this fix. Also since i want to test this on my windows environment how can i set the wait_timeout in mysql to a value around 10 minutes. i tried to do it but still this error is not happening.
Thanks a bunch
Rashid
Thomas Said,
September 9, 2007 @ 6:48 pm
Hello,
Thank you Very Muchn!!!
Your post was very helpfull to me!!!
Thanks again!
Thomas