mysql - Hibernate c3p0 keep alive doesn't seem to work, getting 'Broken Pipe' exception -

i'm using hibernate in combination c3p0 in web application (no spring). overnight, database connections seem time out (or @ least 1 of them) , broken pipe exception next day.
below configuration i'm using project.

    <hibernate-configuration>     <session-factory>         <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/**********?autoreconnect=true</property>         <property name="hibernate.connection.driver_class">com.mysql.jdbc.driver</property>         <property name="hibernate.dialect">org.hibernate.dialect.mysqlinnodbdialect</property>         <property name="hibernate.connection.username">********</property>         <property name="hibernate.connection.password">********</property>         <property name="hibernate.show_sql">false</property>         <property name="hibernate.globally_quoted_identifiers">true</property>         <property name="hibernate.current_session_context_class">thread</property>         <property name="hibernate.transaction.factory_class">org.hibernate.transaction.jdbctransactionfactory</property>         <property name="hibernate.connection.autocommit">false</property>         <property name="hibernate.connection.provider_class">org.hibernate.connection.c3p0connectionprovider</property>          <property name="hibernate.c3p0.min_size">5</property>         <property name="hibernate.c3p0.max_size">25</property>         <property name="hibernate.c3p0.timeout">300</property>         <property name="hibernate.c3p0.max_statements">50</property>         <property name="hibernate.c3p0.idle_test_period">60</property>         <property name="hibernate.c3p0.acquire_increment">1</property>         <property name="hibernate.c3p0.acquireretryattempts">5</property>         <property name="hibernate.c3p0.acquireretrydelay">250</property>         <property name="hibernate.c3p0.preferredtestquery">select 1;</property>         <property name="hibernate.c3p0.validate">true</property>         <property name="hibernate.c3p0.testconnectiononcheckout">true</property>         <property name="hibernate.c3p0.testconnectiononcheckin">true</property>     </session-factory> </hibernate-configuration> 

the exception i'm getting looks following:

      last packet received server 56,974,967 milliseconds ago.     last packet sent server 56,974,967 milliseconds ago.     longer server configured value of 'wait_timeout'. should consider either     expiring and/or testing connection validity before use in application, increasing     server configured values client timeouts, or using connector/j connection     property 'autoreconnect=true' avoid problem.     org.hibernate.exception.jdbcconnectionexception: last packet received     server 56,974,967 milliseconds ago.  last packet sent     server 56,974,967 milliseconds ago. longer server configured value     of 'wait_timeout'. should consider either expiring and/or testing connection     validity before use in application, increasing server configured values     client timeouts, or using connector/j connection property 'autoreconnect=true'     avoid problem.  

the problem here is, using property autoreconnect=true, , testing connection validity. (see c3p0 configuration.) i'm sure c3p0 configuration loaded, since following shows in stdout logs:

      apr 08, 2014 10:57:13 com.mchange.v2.c3p0.impl.abstractpoolbackeddatasource getpoolmanager     info: initializing c3p0 pool... com.mchange.v2.c3p0.poolbackeddatasource@f79fb235 [      connectionpooldatasource -> com.mchange.v2.c3p0.wrapperconnectionpooldatasource@4e20d034 [      acquireincrement -> 1, acquireretryattempts -> 5, acquireretrydelay -> 250, autocommitonclose ->      false, automatictesttable -> null, breakafteracquirefailure -> false, checkouttimeout -> 0,      connectioncustomizerclassname -> null, connectiontesterclassname ->      com.mchange.v2.c3p0.impl.defaultconnectiontester, debugunreturnedconnectionstacktraces -> false,      factoryclasslocation -> null, forceignoreunresolvedtransactions -> false, identitytoken ->      ph9ube91hyyxjw1mgbhpo|709689fb, idleconnectiontestperiod -> 60, initialpoolsize -> 3,      maxadministrativetasktime -> 0, maxconnectionage -> 0, maxidletime -> 300,      maxidletimeexcessconnections -> 0, maxpoolsize -> 25, maxstatements -> 50, maxstatementsperconnection      -> 0, minpoolsize -> 5, nesteddatasource -> com.mchange.v2.c3p0.drivermanagerdatasource@a1ca3a3 [      description -> null, driverclass -> null, factoryclasslocation -> null, identitytoken ->      ph9ube91hyyxjw1mgbhpo|24bbab7, jdbcurl -> jdbc:mysql://localhost:3306/********?autoreconnect=true,      properties -> {user=******, password=******, autocommit=false} ], preferredtestquery -> select 1;,      propertycycle -> 0, testconnectiononcheckin -> true, testconnectiononcheckout -> true,      unreturnedconnectiontimeout -> 0, usestraditionalreflectiveproxies -> false; useroverrides: {} ],      datasourcename -> null, factoryclasslocation -> null, identitytoken -> ph9ube91hyyxjw1mgbhpo|fedb05d,      numhelperthreads -> 3 ]  

the broken pipe part of exception this:

      caused by: com.mysql.jdbc.exceptions.jdbc4.communicationsexception: last packet received server 56,974,967 milliseconds ago.  last packet sent server 56,974,967 milliseconds ago. longer server configured value of 'wait_timeout'. should consider either expiring and/or testing connection validity before use in application, increasing server configured values client timeouts, or using connector/j connection property 'autoreconnect=true' avoid problem.         @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method)         @ sun.reflect.nativeconstructoraccessorimpl.newinstance(nativeconstructoraccessorimpl.java:57)         @ sun.reflect.delegatingconstructoraccessorimpl.newinstance(delegatingconstructoraccessorimpl.java:45)         @ java.lang.reflect.constructor.newinstance(constructor.java:525)         @ com.mysql.jdbc.util.handlenewinstance(util.java:411)         @ com.mysql.jdbc.sqlerror.createcommunicationsexception(sqlerror.java:1116)         @ com.mysql.jdbc.mysqlio.send(mysqlio.java:3851)         @ com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:2471)         @ com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2651)         @ com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2683)         @ com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:2144)         @ com.mysql.jdbc.preparedstatement.executequery(preparedstatement.java:2310)         @ com.mchange.v2.c3p0.impl.newproxypreparedstatement.executequery(newproxypreparedstatement.java:76)         @ sun.reflect.generatedmethodaccessor35.invoke(unknown source)         @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)         @ java.lang.reflect.method.invoke(method.java:601)         @ org.hibernate.engine.jdbc.internal.proxy.abstractstatementproxyhandler.continueinvocation(abstractstatementproxyhandler.java:122)         ... 23 more     caused by: java.net.socketexception: broken pipe         @ java.net.socketoutputstream.socketwrite0(native method)         @ java.net.socketoutputstream.socketwrite(socketoutputstream.java:109)         @ java.net.socketoutputstream.write(socketoutputstream.java:153)         @ java.io.bufferedoutputstream.flushbuffer(bufferedoutputstream.java:82)         @ java.io.bufferedoutputstream.flush(bufferedoutputstream.java:140)         @ com.mysql.jdbc.mysqlio.send(mysqlio.java:3832)         ... 33 more  

i've tried following, based on found on internet:

• adding autoreconnect=true connection url (please note connection still broken after trying use it, no reconnection seems take place)
• adding testconnectiononcheckout , testconnectiononcheckin c3p0 config
• adding idle_test_period , preferredtestquery c3p0 config

and nothing seems work. has got idea on might causing problem?

so, scattershot ideas:

there's error in preferredtestquery. should "select 1" rather "select 1;". i'd think if problem, you'd see exceptions (and checkouts fail), it's worth trying fix that.

[what version of c3p0 using? if want live dangerously , using recent mysql driver, try using latest prerelease, c3p0-0.9.5-pre8, , don't set preferredtestquery @ all. latest version supports jdbc4 connection.isvalid() tests.]

another possibility application holds connections open (checked out) long time. wouldn't quite connection leak, because application tries execute statement against connection. if application this, you'd see seeing, c3p0 won't test connections while in client use they'd able idle out. (i have no idea why autoreconnect doesn't help, except maybe in mid-transaction , autoreconnect doesn't work that?) if want test scenario, can use unreturnedconnectiontimeout (optionally debugunreturnedconnectionstacktraces) forcibly close too-long-checked-out exception , try diagnose problem.

if want problem go away (and less concerned understanding it), might try setting c3p0's maxconnectionage and/or maxidletime. if set these large values, these settings won't meaningfully impact performance. (maybe 7200 secs, i.e. 2 hours, though more ~1800 secs fine.) these settings (especially maxconnectionage) pretty foolproof, unless troublesome connections are, per above, held open , checked out of pool long time. [the c3p0 setting close() checked-out connection out under client unreturnedconnectiontimeout.]

good luck!