Hibernate version: 2.1.7

Full stack trace of any exception that occurs:

net.sf.hibernate.JDBCException: SQLException occurred
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3607)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
at de.fourthproject.controlling.businessobjects.StandortBO.getAllStandorte(StandortBO.java:72)
at de.fourthproject.controlling.businessobjects.StandortBO.createStandortList(StandortBO.java:41)
at org.apache.jsp.MitarbeiterAdministration.Mitarbeiter_jsp._jspService(Mitarbeiter_jsp.java:68)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
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:534)
Caused by: java.sql.SQLException: No operations allowed after connection closed.
at com.mysql.jdbc.Connection.checkClosed(Connection.java:2647)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1300)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1281)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:249)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:223)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:704)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:185)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:121)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3604)
... 34 more


Name and version of the database you are using: MySQL 5.0.0a


Hi,
my problem is, that MySQL closes a open Connection after 8h of inactivity and hibernate does not recognizes, that this Connection ist already closed, when he tries to open a Session. I know that because if I create an output with session.isConnected and session.isOpen(), I'M always getting 'true'. So I've tried to write an validateSession-method by trying to execute an Query. If a exception is caused, this method reutrns false. But how can I tell hibernate, that it should establish a new Connection? Using session.reconnect() doesn't work and I think it's not a good solution to write a own class or method to establish a new Connection and delievering ito session.reconnect(Connection).
Thanks for your help!

FPC

do you know what connexion pool is? please read about c3p0 or proxool and when you see "not for production use" somewhere in the doc or in the logs, that must mean something .... ;)

http://www.hibernate.org/214.html

What do you mean on this? I understand DBCP is not recommended by you. If I understand well you are now saying that c3p0 and proxool are not for production use. That is true, after all both are before 1.0. What is "connexion" pool? Google doesn't show anything about it.

dshk,
i'm saying that i think FPC is using hibernate default connexion pool and should switch to c3p0 or proxool.

FPC, what you need is autoReconnect. Here's my own connection URL:
db.url=jdbc:mysql://localhost:3306/${name.short}?autoReconnect=true

Reference:
http://dev.mysql.com/doc/connector/j/en/cj-driver-classname.html

Hope that helps !
François

and there is an "autoReconnectForPools" connection parameter too, but I have no idea what it does or what is the differenve between the two.

More information about mysqlconnector, although this still doesn't help to know what should we do...

From other posts in this forum it seems that using c3p0 doesn't solve the "MySql disconnects after 8 hours" problem either, see:
http://forum.hibernate.org/viewtopic.php?t=937693
Conclusion of this topic:
(1) Don't use pools at all
(2) use pools but pass the connection yourself to Hibernate. Unfortunately I have read somewhere in the Hibernate doc or in Hibernate in Action that this has very negative consequences and not recommended. But in another post I read from a Hibernate developer, that doing so is perfectly safe, it is only less convenient...

This MySql/J documentation section says that you should use autoReconnect:
http://dev.mysql.com/doc/connector/j/en/cj-troubleshooting.html#id2955852

On the other hand in this response of Mark Matthews (MySql/J developer) says that autoReconnect and autoReconnectForPools will be deprecated, because thay cannot be correctly implemented:
http://bugs.mysql.com/bug.php?id=5020

So I cannot be more confused... :)

I think it is very trivial to understand and it doe's not confuse. Pool is a workaround for heavy weight connections. Try to test and tune your application without workaraunds first. If it doe's not help and you know the reason then try a workaroud. It works for me, probably it can work for you too. It must be faster to test than to find solution in forum.

baliukas, sorry if it is trivial, but do you know how to configure Hibernate to NOT use any pool (including its builtin pool) in an _unmanaged_ environment?

You can implement connection provider yourself, but it is possible to configure pool this way too,
"maxConnections=0" means no pooling on some implementations, it helps to test and to tune stuff without changes in application.

At the moment I can only say, that my cfg.xml-File already includes autoReconnect=true but it doesn't work. I had an similiar problem with different other pooling frameworks (protomatter, the jakarta pooling project, etc.). All had the same problem: any framework i've tested didn't validate the connection before given it back. My solution was to write a own connetion-pooling, it works fine but it is not Thread-safe. So I need just the guaranty that the connection-framwork recognizes that the Connection can't be used and opens an new one. I've also already tried to use a retry-logic, but I need to delete the old Connection and build up a new one.

Hi.

c3p0 can handle a database's automatic expiration of Connections, in several ways:

1) You can set a maxIdleTime parameter so that old, idle Connections are automatically purged by the pool;

2) You can configure Connection testing, so that Connections are periodically tested by the pool (either prior to Connection checkout, or else frequently while idle), and purged if expired;

Please see the section of c3p0's docs regarding Connection testing.

Please note also that c3p0's default Connection test is slow -- I strongly recommend your setting the "preferredTestQuery" or "automaticTestTable" parameters. For MySQL, I have heard that setting preferredTestQuery to "SELECT 1" is a good approach. (Thanks to Mark Matthews -- see http://lists.mysql.com/java/8119 .)

Please also see c3p0's documentation of configuring c3p0 parameters from hibernate. (5 parameters MUST be configured in your hibernate config; other parameters should be defined in a file called c3p0.properties.)

(The latest c3p0 release -- 0.8.5 (final) -- includes expanded documentation of these issues.)

smiles,
Steve (c3p0 guy)