Problem with Hibernate + c3p0

sven · **Posted:** Wed Aug 31, 2005 7:12 pm

I have a problem using Hibernate with c3p0 connection pool. See configuration and exceptions below.

Well, moreover I wondered what happens, if hibernate.c3p0.max_size is greater than mysql concurrent users? Can this lead to exceptions as well?

This is related to a common problem mentioned on dev.mysql.com:

Quote:

1.5.1.4:
I have a servlet/application that works fine for a day, and then stops working overnight

http://dev.mysql.com/doc/connector/j/en/cj-faq.html

Hibernate version: 3.1 beta 1

Database: MySQL 4.1.14

JDBC Driver: MySQL Connector/J 3.1.10

Connection Pool: c3p0 v. 0.9.0 (delivered with Hibernate 3.1)

hibernate.properties:
hibernate.connection.url jdbc:mysql://localhost/mydb?autoReconnect=true

hibernate.c3p0.max_size 75
hibernate.c3p0.min_size 3
hibernate.c3p0.timeout 3600
hibernate.c3p0.max_statements 0
hibernate.c3p0.idle_test_period 1800
hibernate.c3p0.acquire_increment 2
hibernate.c3p0.validate false

c3p0.properties:
c3p0.initialPoolSize=3
c3p0.autoCommitOnClose=false
c3p0.forceIgnoreUnresolvedTransactions=false
c3p0.idleConnectionTestPeriod=1800
c3p0.testConnectionOnCheckin=true
c3p0.testConnectionOnCheckout=true
c3p0.automaticTestTable=c3p0_test_table
com.mchange.v2.log.MLog=com.mchange.v2.log.log4j.Log4jMLog

Exception:

Code:

javax.servlet.ServletException: could not execute query
   org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:523)
   org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
   org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

org.hibernate.exception.GenericJDBCException: could not execute query
   org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   org.hibernate.loader.Loader.doList(Loader.java:1962)
   org.hibernate.loader.Loader.list(Loader.java:1943)
   org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:405)
   org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:293)
   org.hibernate.impl.SessionImpl.list(SessionImpl.java:862)
   org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   de.w3s.webapp.struts.actions.admin.SelectSurveyStatesAction.execute(SelectSurveyStatesAction.java:83)
   org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

Code:

WARN  01.09.2005 00:51:14,665 com.mchange.v2.c3p0.impl.NewPooledConnection (NewPooledConnection:handleThrowable:356) - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
WARN  01.09.2005 00:51:14,666 com.mchange.v2.c3p0.impl.NewPooledConnection (NewPooledConnection:handleThrowable:357) - [c3p0] Another error has occurred [ com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 


Last packet sent to the server was 2 ms ago.
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2707)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   ... 27 more
WARN  01.09.2005 00:51:14,665 com.mchange.v2.c3p0.impl.NewPooledConnection (NewPooledConnection:handleThrowable:356) - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
WARN  01.09.2005 00:51:14,666 com.mchange.v2.c3p0.impl.NewPooledConnection (NewPooledConnection:handleThrowable:357) - [c3p0] Another error has occurred [ com.mysql.jdbc.CommunicationsException: 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:2689)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:405)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:293)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:862)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at de.w3s.webapp.struts.actions.admin.SelectSurveyStatesAction.execute(SelectSurveyStatesAction.java:83)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   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:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
   at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **

Thanks for your help!

Best regards
Sven

christian · **Posted:** Thu Sep 01, 2005 3:22 am

Quote:

Well, moreover I wondered what happens, if hibernate.c3p0.max_size is greater than mysql concurrent users? Can this lead to exceptions as well?

Of course.

swaldman · **Joined:** Tue Jan 06, 2004 8:58 pm **Posts:** 145

Sven,

The c3p0 stuff you're seeing is a warning you that you are using a Connection that has already generated Exceptions, that c3p0 considers broken. c3p0 is also reporting to you the most recent Exception from the Connection.

Whenever a JDBC operation provokes an Exception, c3p0 performs a test and makes a judgement about whether or not the Exception is a "normal" Exception (a commit fails due to concurrent modification, a query is malformed or against nonexistent tables), or whether the Exception indicates that the Connection object is broken (communication failures, for example). If c3p0 decides the Connection is broken, the PooledConnection object that contains it signals a ConnectionError, which prevents the Connection from ever being returned to the pool. When c3p0 logs "A PooledConnection that has already signalled a Connection error is still in use!", it is kind of saying "I told you so."

In the past, c3p0 would forcibly close Connections that it decides are broken. This (rightly) upset users, who do all kinds of wacky things with Connections, which may provoke all kinds of Exceptions and temporarily broken states that users expect and know how to recover from. c3p0 now presumes you know what you're doing, and lets you keep working with an Exception that seems to be broken, close()ing (instead of returning it to the pool) only when the user invokes close(). But if more errors occur, c3p0 doesn't bother signalling a ConnectionError, since that's been done already. It just says I told you so, why are you still using this crappy Connection?

To resolve your problem, you really want to understand why the Connection you are working with is breaking in the first place. Look for the very first Exception you see, especially at the JDBC Connection level, rather than a higher-level hibernate Exception. (What is the root cause of the org.hibernate.exception.GenericJDBCException?) Fix this, and c3p0 won't have to tell you I told you so.

Good luck!

Steve (c3p0 guy)

p.s. i've been traveling, and c3p0 support here, on sourceforge, and elsewhere has been less and slower than i like it to be. sorry about that! hopefully i'll catch my breath and resolve some of the issues people have noted soon.

sven · **Posted:** Sun Sep 04, 2005 7:35 pm

Hello Steve,

thanks a lot for your time and your explanation!
I reproduced my problem and here is my stacktrace. The org.hibernate.exception.GenericJDBCException is caused by com.mysql.jdbc.CommunicationsException.

Do you have an idea what could be the problem?
Is there any way to improve my c3p0 configuration? I don't need ultra-high performance in the first place, but stability. Any suggestions are really welcome!

c3p0.properties

Code:

c3p0.initialPoolSize=3
c3p0.autoCommitOnClose=false
c3p0.forceIgnoreUnresolvedTransactions=false
c3p0.idleConnectionTestPeriod=1800
c3p0.testConnectionOnCheckin=true
c3p0.testConnectionOnCheckout=true
c3p0.automaticTestTable=c3p0_test_table
c3p0.connectionTesterClassName=com.mysql.jdbc.integration.c3p0.MysqlConnectionTester
com.mchange.v2.log.MLog=com.mchange.v2.log.log4j.Log4jMLog

hibernate.properties

Code:

hibernate.c3p0.max_size 20
hibernate.c3p0.min_size 3
hibernate.c3p0.timeout 3600
hibernate.c3p0.max_statements 0
hibernate.c3p0.idle_test_period 1800
hibernate.c3p0.acquire_increment 2
hibernate.c3p0.validate false

stacktrace

Code:

WARN  04.09.2005 23:24:30,840 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:71) - SQL Error: 0, SQLState: 08S01
ERROR 04.09.2005 23:24:30,845 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:72) - 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:2689)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:405)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:293)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:862)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at de.w3s.webapp.struts.actions.admin.SelectSurveyStatesAction.execute(SelectSurveyStatesAction.java:83)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   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:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
   at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 2 ms ago.
WARN  04.09.2005 23:24:30,870 org.apache.struts.action.RequestProcessor (RequestProcessor:processException:516) - Unhandled Exception thrown: class org.hibernate.exception.GenericJDBCException
ERROR 04.09.2005 23:24:30,874 org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/w3s].[action] (StandardWrapperValve:invoke:253) - Servlet.service() for servlet action threw exception
org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1962)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:405)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:293)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:862)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at de.w3s.webapp.struts.actions.admin.SelectSurveyStatesAction.execute(SelectSurveyStatesAction.java:83)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   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:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
   at java.lang.Thread.run(Thread.java:595)
Caused by: com.mysql.jdbc.CommunicationsException: 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:2689)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:405)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:293)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:862)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at de.w3s.webapp.struts.actions.admin.SelectSurveyStatesAction.execute(SelectSurveyStatesAction.java:83)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   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:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
   at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 2 ms ago.
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2707)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   ... 27 more

Thanks again, I really appreciate your help!

Best regards
Sven

swaldman · **Joined:** Tue Jan 06, 2004 8:58 pm **Posts:** 145

Sven,

I can't help you with why your Connections to mysql seem to be dying. At the c3p0 level, your configuration looks pretty good. I don't know how com.mysql.jdbc.integration.c3p0.MysqlConnectionTester is implemented (your post is the first I've heard of it), but presuming it is carefully done, when your Connection is checked out from c3p0, they are tested and functional, there are no communications problems. So Connections are dying while they're checked out.

(This does seem to be a problem at the Connection level. As you seem not to be caching statements in your config, it can't related to the statement cache.)

Here is a bit of advice: 1) try to minimize the time that you keep your Connections open. If a Connection is checked out and held open for a long time, there's nothing c3p0 can do to help. But if you frequently close and reopen Connections, it'll still be pretty fast, because functioning Connections are pooled, but c3p0 will (with your cautious config) test and discard bad Connections on checkout, minimizing the likelihood of your app seeing Exceptions; 2) when your app does see an unexpected Exception, make sure that your response to that Exception includes close()ing the bad Connection and checking out another one. The message you found in your logs at the start of this threads suggests that you may sometimes be making attempts on Connections that have already appeared to fail.

Good luck, and continued apologies to you and to all for being a bit behind on c3p0 support.

smiles,
Steve (c3p0 guy)

p.s. as well as config file information, please do include c3p0's INFO level version and config dumps in c3p0-related support requests.

sven · **Posted:** Wed Sep 07, 2005 8:57 pm

Hi Steve,

thanks again for your reply!

I just searched for c3p0 in the MySQL Connector/J changelog and I found the following for version 3.1.8:

Quote:

Added support for the c3p0 connection pool's (http://c3p0.sf.net/)
validation/connection checker interface which uses the lightweight
'COM_PING' call to the server if available. To use it, configure your
c3p0 connection pool's 'connectionTesterClassName' property to use
'com.mysql.jdbc.integration.c3p0.MysqlConnectionTester'.

See: http://dev.mysql.com/doc/connector/j/en ... gelog.html

A little strange to say, since using this parameter (I haven't used it when getting the exceptions) I haven't got a single exception until now. But I'll have to wait some more time...

To your advice:
1) My application is a web application based on Struts. I'm using the pattern "Open Session in View" which works really fine. There are no parts which keep connections open for a long time. A (Hibernate) session is opened within an action (Struts Action) which is executed, the corresponding JSP is shown and finally the session is closed. I don't think I can optimize here...

2) That is understandable and sounds good, but how would I implement it in my application code? It will probably be part of any of my Action classes, right? Is there a way of doing this properly?

Just to give you an impression of what it looks like, here's an extract of one of my Action classes:

Code:

public class SubjectListAction extends Action {

    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        // get a Hibernate session and begin a transaction
        Session session = HibernateUtil.getSession();
        HibernateUtil.beginTransaction();

        // create a Hibernate query to select the results from the database
        StringBuilder queryString = new StringBuilder(50).
                append("SELECT s ").
                append("FROM Subject AS s ").
                append("ORDER BY s.name ASC");
        Query query = session.createQuery(queryString.toString());
        List elements = query.list();

        // do something...
      
        // commit the transaction
        HibernateUtil.commitTransaction();
        session.flush();

        // put Subject objects into bean
        SubjectBean subjectBean = (SubjectBean)form;
        subjectBean.setSubjects(subjects);

        // forward to the success page
        return mapping.findForward("success");
    }
}

I appreciate any further suggestions.

Best regards
Sven

swaldman · **Joined:** Tue Jan 06, 2004 8:58 pm **Posts:** 145

Sven,

I'm hesitant to offer this advice, because while I may be a gnarled JDBC veteran, I am a hibernate novice. But looking at your code, my intuition is that your Action should have a finally block that calls Session.close().

If you are using a HibernateUtil as described in hibernate's getting started docs, you have a ThreadLocal session that, if not closed, may retain a Connection indefinitely with a variety of bad effects, including eventual Connection pool exhaustion and indefinite persistance of broken Connections.

Again, my apologies if I'm missing something obvious here. I can speak authoritatively on c3p0/jdbc issues, but not on architecture at the hibernate/struts/spring levels of abstraction.

Good luck!
Steve (c3p0 guy)

sven · **Posted:** Thu Sep 08, 2005 4:00 pm

Hi Steve,

I think you're wrong in this case. I'm using the pattern Open Session in View which handles the closing of the session within a filter, defined in the web.xml.

Code:

<filter>
   <filter-name>HibernateFilter</filter-name>
   <filter-class>de.w3s.webapp.servlets.HibernateFilter</filter-class>
</filter>

Using this pattern the sessions are kept open while rendering the JSP. Afterwards their are closed by the filter.

Though, thanks again for your help!

Best regards
Sven

swaldman · **Joined:** Tue Jan 06, 2004 8:58 pm **Posts:** 145

Sven,

Yup. I try to be wrong as often as possible; that's why I didn't follow your link, which explains the architecture you are using quite well... As long as the Session clean-up is reliably executed in a finally block of the filter, and as long as all accesses to your action are made in such a way that the filter applies, you should be okay.

(Note that there are hazards here: in the servlet 2.3 spec filters don't apply to forwards and includes, whereas forwarding or including to a resource mapped to your action would still start up a session that might never terminate; in servlet 2.4 you can control which of normal requests, forwards, includes, and errorpage-forwards are mapped through a filter, and would want to ensure the filter applies to all possible invocations. But if nested resources are filtered, if you're not careful you may end up closing your session prematurely. Maybe you'd want a thread-local counter in the filter to only close() on the when a nest-count goes to zero, so you can map to everything for a reliable close(), but enforce a once-and-only-once close() in the outermost application of the filter. Anyway, I'm just waving my hands around to make up for my previous misguided post.)

Sorry 'bout that, and good luck!

Steve (c3p0 guy)

p.s. I'm interested if the alternative Connection tester really does reliably fix your issue. Do let me know...

sven · **Posted:** Fri Sep 09, 2005 3:47 am

Hi Steve,

thanks for your suggestions!
I think I've found my error and unfortunately it's quite stupid. :)
There are very very few Action classes which do not have a forward and return null instead of an ActionForward meaning to remain on the same page. This is of course intended behaviour (e.g. downloading something etc.).
BUT the consequence of not forwarding to any other JSP is that the filter is not called and the session is not closed. Now, I' closing the sessions within these certain Action classes and I'll wait, if the exception appears again.

The com.mysql.jdbc.integration.c3p0.MysqlConnectionTester seems to be implemented reasonably though.
Just have a look at the source code:
http://dev.mysql.com/downloads/connector/j/3.1.html

I'd like to thank you again for your time and I'll let you know, if any further exceptions appear.

Best regards
Sven

posygary · **Joined:** Mon Dec 31, 2012 5:31 pm **Posts:** 2

Hi all,
My error appears randomly and I have to fix it up.
I post it in http://stackoverflow.com/questions/14121226/a-pooledconnection-that-has-already-signalled-a-connection-error-is-still-in-use
But I can't resolve the problem.
Can somebody help me?