java.net.SocketException Broken Pipe Exception
For the last week, I’ve been chasing down a randomly occuring broken pipe exception in the banking app we’ve written. It was like looking for a needle in a haystack.
Database connections were being tied up to the length of wait_timeout on the MySQL server. We’re using Hibernate for our persistence layer and C3P0 for our connection pool. During initial pilot testing, we only saw issues after a long period of time. My assumption was that the pool was not doing its job and MySQL was killing the connections. However, after ramping up the testing to a larger scale, the exception began occuring much more often.
Here is the dump I was seeing:
10:01:57,812 WARN NewPooledConnection:356 – [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
10:01:57,813 WARN NewPooledConnection:357 – [c3p0] Another error has occurred [ com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
MESSAGE: Broken pipe
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java(Compiled Code))
at java.net.SocketOutputStream.write(SocketOutputStream.java(Compiled Code))
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java(Inlined Compiled Code))
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java(Inlined Compiled Code))
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java(Compiled Code))
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java(Inlined Compiled Code))
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java(Compiled Code))
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java(Compiled Code))
at com.mysql.jdbc.Connection.execSQL(Connection.java(Compiled Code))
at javax.servlet.http.HttpServlet.service(HttpServlet.java(Compiled Code))
** END NESTED EXCEPTION **
Initially I thought the problem may lie somewhere in our code, but since this was the first time I used C3P0 for my connection pool I decided to Google the problem first. Many of the postings I found related to failures on the part of the connection pool. Based on this post, I ripped out C3P0 and implemented DBCP. That did not fix my problem, though. Some posts say it is a problem with the driver (I do not believe that it is), others blame the c3p0 connection pool (I do not).
In fact, our problem was sitting in our own code and was implemented by someone who was not seasoned in transactions. When you demarcate transactions, you have to explicity commit or rollback every time. Some newbies believe that queries do not have to be committed. They do. If not, the connection is left assigned in case you wanted to manipulate the resultset or do additional reads (there are other reasons, but these are the easiest to wrap your mind around).
So in our case, a transaction was begun and a query was executed. Unfortunately, the transaction was not committed in every circumstance, leaving dangling connections that later got killed by MySQL. C3P0 would later attempt to reattach to the now defunct dangling connection and kaboom – java.net.SocketException Broken PIpe would appear. Finding the spot in the code (it was in some complicated if/else logic) and correcting it solved our problem and we have not had a problem with Hibernate or C3P0 since!
Update: 7 August 2006
In the 3 days since I’ve posted this, it has already garnered a lot of page views. I didn’t realize it would be such a hit. For most of you, it should just be a matter of setting up periodic connection testing. Here are some more pointers to information if you are getting this error:
C3P0: Configuring Connection Testing
Hibernate specific properties for C3P0
More Hibernate specific configuration info
Updated 15 February 2007 – fixed link to “Hibernate Specific Properties for C3P0″