Hi,

I'm using hibernate 2.1.8 along with C3P0 and MySQL 4.1.10 on linux. Following is the configuration in my "hibernate.cfg.xml"

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">5000</property>
<property name="c3p0.idle_test_period">300</property>
<property name="c3p0.max_statements">20</property>

This runs fine for some time (typically, a day) and then I start getting exception similar to the one shown below

** BEGIN NESTED 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)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:66)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:124)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2616)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2547)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1512)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2385)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2306)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1877)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1722)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)

I google'd quite a bit, but couldn't find any specific solution. I'm already using "idle_test_period" and "timeout". As, "testConnectionOnTimeout" is not recommended, I'm not considering that.

I would appreciate if someone can point a resolution for the same.

Thanks,
- Nitin

nitin,

if you use the latest 0.9.0-pre release[*], testConnectionOnCheckout is fine as long as it is used in combination with a fast preferredTestQuery. For MySql, SELECT 1 is reputed to work well as a test query. See c3p0's docs for more discussion of connection testing strategies.

smiles,
Steve (c3p0 guy)

[*] these will lose the "-pre" any day now -- it's just a matter of getting some reorganizing and a bit more testing done in the face of some pressing time commitments.