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.
Communications link failure due to underlying exception:
MESSAGE: Broken pipe
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
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.