-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 
Author Message
 Post subject: MySQL closes Connection, but Hibernate thinks it's open
PostPosted: Wed Feb 09, 2005 6:19 am 
Regular
Regular

Joined: Thu Jan 27, 2005 8:58 am
Posts: 80
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 09, 2005 1:18 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
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

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 09, 2005 3:35 pm 
Beginner
Beginner

Joined: Tue Feb 08, 2005 7:01 am
Posts: 21
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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 09, 2005 3:45 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
dshk,
i'm saying that i think FPC is using hibernate default connexion pool and should switch to c3p0 or proxool.

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 09, 2005 10:44 pm 
Newbie

Joined: Sun Dec 28, 2003 2:07 pm
Posts: 15
Location: Quebec, Canada
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 3:29 am 
Beginner
Beginner

Joined: Tue Feb 08, 2005 7:01 am
Posts: 21
and there is an "autoReconnectForPools" connection parameter too, but I have no idea what it does or what is the differenve between the two.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 4:03 am 
Beginner
Beginner

Joined: Tue Feb 08, 2005 7:01 am
Posts: 21
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... :)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 4:21 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 4:38 am 
Beginner
Beginner

Joined: Tue Feb 08, 2005 7:01 am
Posts: 21
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 4:44 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
dshk wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 10:21 am 
Regular
Regular

Joined: Thu Jan 27, 2005 8:58 am
Posts: 80
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 6:10 pm 
C3P0 Developer
C3P0 Developer

Joined: Tue Jan 06, 2004 8:58 pm
Posts: 145
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)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.