Hello,

I have extensively read about recovering connections that are closed within a pool connected to mysql. However I have use 2 pools proxool and cp30 to no avail when recovering. Please forgive me if the question is lame or if the pools do not do what I would like. I am taking the Hibernate training on April 19 in NYC ... please help me out until I get more training. I realize this is not a hibernate issue per se, but I may have c3p0 configured incorrectly within hibernate. Important to note that I would also be happy to just have c3p0 open a new connection on each request - cannot seem to get it to do that either.


Hibernate version: 2.1

Mysql Version: 4.0.20

Mysql Connector: 3.1.7

Here is how I am testing:

I startup my application and make a few calls that initialize hibernate (using HibernateUtil.java).

I then kill the database and start the database.

I wait about 5 minutes and hit the same urls - and I get a socket connection reset exception. I would like to have c3p0 check to make sure this connection is safe to use prior to Hibernate using it, seems like this can be done per the docs. Here are my configurations:

hibernate.properties:

hibernate.c3p0.max_size 5
hibernate.c3p0.min_size 0
hibernate.c3p0.timeout 120
hibernate.c3p0.max_statements 100
hibernate.c3p0.idle_test_period 0
hibernate.c3p0.acquire_increment 2

hibernate.dialect net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql://localhost:3306/test?autoReconnect=true

hibernate.connection.username homer
hibernate.connection.password simpson

hibernate.cfg.xml:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="show_sql">true</property>
<mapping resource="com/test/dao/Subscriber.hbm.xml"/>
<mapping resource="com/test/dao/Link.hbm.xml"/>

</session-factory>

</hibernate-configuration>

c3p0.properties:

c3p0.propertyCycle=300

c3p0.testConnectionOnCheckout=true
c3p0.automaticTestTable=c3p0config
c3p0.testConnectionOnCheckin=true
c3p0.idelConnectionTestPeriod=60
c3p0.maxIdleTime=120



And finally the full stack trace:

Hibernate: SELECT l.LINK_ID as LINK_ID0_, l.DATE as DATE0_, l.LINK_TEXT as LINK_TEXT0_, l.LINK_ADDRESS as LINK_ADD4_0_ FROM links l WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= DATE ORDER BY DATE DESC
com.mchange.v2.c3p0.impl.NewPooledConnection@a35b19 invalidated by Exception: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:168)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:104)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:172)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1839)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:71)
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.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3867)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.mastalk.links.ReadOfTheDayAction.execute(ReadOfTheDayAction.java:50)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:552)


** END NESTED EXCEPTION **


at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2500)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:71)
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.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3867)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.mastalk.links.ReadOfTheDayAction.execute(ReadOfTheDayAction.java:50)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:552)
CONNECTION ERROR OCCURRED!

I am also trying to keep mysql the connection drops by mysql to not have a negative impact. I have relentlessly tried every option I can find and can only deduce that I am not configuring the files correctly. Here is the stack when a connection by mysql gets dropped (I realize that this exception is all over the forum but figured it could not hurt).

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

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:71)
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.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3867)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.mastalk.links.ReadOfTheDayAction.execute(ReadOfTheDayAction.java:50)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:324)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:395)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:673)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:615)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:786)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)


** END NESTED EXCEPTION **

Hi,

I hope I did not cost anyone too much time in reading my issue...as it is MY FAULT :-)

The issues I had were the result of a particular scenario in my code where a session was not being closed. I assume as a result the connection wan not being set back to the pool and as a result Cp30 could not do its good config stuff like checking a connection on check-in.

I am wondering if there would be a possible Hibernate piece of functionality that would limit the amount of time a session could take before it is closed - probably a default associated?

Thank you.

as i can see from the logs, you're using struts, so you're in a webapp.
How can the session remain opened so long, are storing it in httpsession? are you using threadlocal pattern + storing in hhtp session?
you may do something (very) dangerous in your app.

You must :
- close (session per http request pattern)
- or disconnect (session per application transaction pattern)

Hope your app isn't critical ;)

Let's read hibernate in action to get more details.

starrin,

re your c3p0 configuration, there are some problems. But one of them is my fault -- there is something missing from c3p0's docs!

as you noticed, there are several parameters that MUST be set in your hibernate config. in hibernate 2.1, one of these is...



...so you need to set this in your hibernate config rather than in c3p0.properties!

(hibernate3 seems to have removed "hibernate.c3p0.validate" from the hibernate-managed params... that's why it's not mentioned in c3p0 docs.)

also, in your c3p0.properties, you have...

c3p0.idelConnectionTestPeriod

this property name is misspelled (it should be c3p0.idleConnectionTestPeriod), but it also needs to be set in hibernate, as...



when you have c3p0-related problems, a good thing to do is check the config dump c3p0 spits into your logs on pool initialization, and make sure that the configuration that you think you are setting is actually getting through to the pool.

although it sounds like you had other issues as well, proper configuration of Connection testing should help with database failover issues.

smiles,
Steve (c3p0 guy)

AWESOME!

This is why I love this project. All of you are so helpful.

Anthony -- you are correct -- this was my problem, I was not closing the session in one of my actions. This is now fixed -- cannot believe it slipped by me prior to posting.

swaldman -- Thank you for your help -- this is the first time I have used c3p0 -- I love it. The connection pool is working as expected and I did notice the init paroameters coming out when I initialize Hibernate -- so I started checking them on each startup to make sure they were as I wanted them.

I am looking forward to my formal Hibernate training in April :-)

Thank you.