MySQL, Hibernate and Broken Pipe Exception

I was using Hibernate with Apache Tomcat and MySQL with apache-commons-dbcp for a web application I was developping recently and I used to get this annoying `java.net.SocketException: Broken pipe` after about 8 hours of usage. I tried the following quick fixes:

• sessions were closed (which acually were)
• ensuring that the mysql connection url had autoreconnect=true attribute

But none of it fixed the issue. Finally after searching for a while on internet i found out the cause and here it is.

Cause: MySQL server has a default timeout period after which it terminates the idle connections. This period is 8 hours by default. Now here’s what happens. The dbcp creates a set of connections to database when the servlet container / application server starts up. If the connections are not used for the tinmeout period, the MySQL server assumes these to be dead connections and terminates them. The dbcp, however, is unaware of the fact that the connections have been terminated. So when a connection is demended from the connection-pool, these dead connections are returned and when a call is made on these dead connections – *BOOM* – you the java.net.SocketException: Broken pipe exception. Ok – so what’s the fix ??

Fix: If only the connection pool could check if the the connection it is about to return is live or not, the porblem is fixed. This can be done in apache-common-dbcp (I know this one coz I used it, please look into documentation of the connection-pool you are using). Here’s how you do it: You add the following properties to dbcp configuration.

• validationQuery=”SELECT 1″
• testOnBorrow=”true”

And that does the trick.

How it works:

• Before returning the connection from pool to the application, dbcp runs the “SELECT 1 ” query on the connection to see it it is still live. That’s the job of validationQuery.
• As for testOnBorrow, you tell dbcp to perform the check before returning the connection to application.

For more details refer to the apache-common-dbcp configuration manual here. The apache-commons-dbcp config:

<Resource   name="jdbc/cooldatabase"
			description="Strandls.com license database"
			auth="Container"
			type="javax.sql.DataSource"
			factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
			driverClassName="com.mysql.jdbc.Driver"
			url="jdbc:mysql://localhost:3306/cooldatabase?autoReconnect=true"
			username="cooluser"
			password="coolpassword"
			initialSize="0"
			maxActive="20"
			maxIdle="10"
			minIdle="0"
			maxWait="-1"
			validationQuery="SELECT 1"
			testOnBorrow="true"
			poolPreparedStatements="true"
			removeAbandoned="true"
			removeAbandonedTimeout="60"
			logAbandoned="true"/>

The complete stacktrace:

[ERROR] 12:27 (JDBCExceptionReporter.java:logExceptions:78)Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketExceptionMESSAGE: Broken pipe

STACKTRACE:
java.net.SocketException: Broken pipeat
java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2689)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551).....

Advertisement