hibernate-c3p0 problem : Communications link failure

vikas_khengare · **Posted:** Mon Aug 03, 2009 9:49 am

Hi Friends,

I am using Hibernate 3, c3p0-0.9.1.2 & mysql 5.0.45. I am getting classic hibernate-c3p0 problem, Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Broken pipe

I have already read the messages from
https://www.hibernate.org/214.html
http://forums.mysql.com/read.php?39,16257,146731#msg-146731 but still facing the same problem.

I have put below specified c3p0 properties in hibernate.cfg.xml file but still I am getting same problem after some days.
Following is my hibernate.cfg.xml file properties

Code:

        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/myDBName</property>
        <property name="connection.username">userName</property>
        <property name="connection.password">password</property>

   <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
   <property name="hibernate.c3p0.acquire_increment">5</property>
   <property name="hibernate.c3p0.idle_test_period">100</property>
   <property name="hibernate.c3p0.max_size">20</property>
   <property name="hibernate.c3p0.min_size">5</property>
   <property name="hibernate.c3p0.max_statements">0</property>

        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <property name="current_session_context_class">thread</property>
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

In above file, I have doubts,
i)Do I need to attache c3p0 properties with "hibernate."?
ii)Is it mandatory to put "connection.provider_class" & "preferredTestQuery" properties in above file?

Here is stack trace which I am getting in log file

Code:

06:45:57,337 WARN  [com.mchange.v2.c3p0.impl.NewPooledConnection] [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
06:45:57,337 WARN  [com.mchange.v2.c3p0.impl.NewPooledConnection] [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** 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:65)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1554)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2213)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at com.aikon.db.IdeaThemeDAO.list(IdeaThemeDAO.java:26)
        at com.aikon.api.servlet.FetchThemes.doPost(FetchThemes.java:42)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
        at java.lang.Thread.run(Thread.java:595)

** END NESTED EXCEPTION **

Last packet sent to the server was 0 ms ago.

Please, Can you help to find out what is problem? Is there any problem with my hibernate.cfg.xml file? or something else that I am missing?

Thank you.

regards,
Vikas Khengare

akshaye · **Joined:** Fri Feb 27, 2009 10:30 am **Posts:** 1

Vikas,

I am also trying to find out a solution to the same problem, however there are some things you might wanna try...may be you tried them already.

1. Make sure the value here <property name="hibernate.c3p0.idle_test_period">100</property> which is 100 is less than the wait_timeout set for mysql (by default it is 8 hrs)

2. I recently read there are issues when make spring+hibernate+c3po all work together,look at this post
http://forums.mysql.com/read.php?39,139 ... msg-160175

3.Also try the property names without hibernate , eg: <property name="c3p0.idle_test_period">100</property>
sounds stupid but read somewhere.

Let me know if it makes any difference.

Thanks!

vikas_khengare · **Posted:** Thu Aug 06, 2009 2:41 am

Hi akshaye,

Thanks for your reply.

1. "hibernate.c3p0.idle_test_period" property value is less than the default wait_timeout of mysql
2. I read that post but I am not using spring+hibernate+c3p0 ... I am just using plain Hibernate+c3p0
3. I have already tried this properties with our prefix "hibernate."

Now I have put c3p0.properties in classpath and it is firing query "SELECT 1;" after every 100sec. That means it is reflecting the changes from properties file; you can check mysql query log.

Code:

c3p0.acquireIncrement=1
c3p0.idleConnectionTestPeriod=100
c3p0.initialPoolSize=2
c3p0.maxIdleTime=10
c3p0.maxPoolSize=25
c3p0.maxStatements=0
c3p0.minPoolSize=5
c3p0.testConnectionOnCheckout=true
c3p0.preferredTestQuery=SELECT 1;

But still it is not solving the problem because it is firing SELECT 1; query only for 5 connections not all connections from pool.
Might be I am not handling hibernate session correctly. For insert,update & delete, I am starting and committing transactions but for selection/search purpose we are starting transaction but not committing it.
Is that causing the problem to not closing database connection and that connections only sitting idle in pool?

Please let me know your comments on this.

thedug · **Joined:** Sat Sep 12, 2009 10:20 am **Posts:** 44

Did you resolve this?

Did you try adding:

c3p0.idleConnectionTestPeriod

BonzerChien · **Joined:** Thu Aug 26, 2010 10:05 am **Posts:** 1

Here is my solution:
http://shengchien.blogspot.com/2009/10/ ... mysql.html

Hope it helps you!