Sunday, July 17, 2011

c3p0 Connection reset error

By default c3p0 does not pings or destroys opened connections, so it will not know if database closes old connection until someone will try to use it. It leads to bad errors like:

Caused by: java.sql.SQLException: I/O Error: Connection reset
        at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(
        at net.sourceforge.jtds.jdbc.TdsCore.submitSQL(
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.setAutoCommit(
        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(
        at org.hibernate.transaction.JDBCTransaction.begin(

Fortunately, this can be easily fixed as c3p0 has all necessary facilities for pinging and maintaining connections. There are several options, you can read about them in c3p0 docs, I found easiest for me asynchronously pinging waiting connections every few minutes with trivial select. For example, this will specify ping connection every 3 minute in MS SQL:

         <Set name="idleConnectionTestPeriod">180</Set>
         <Set name="preferredTestQuery">select 1</Set>

No comments:

Post a Comment