aspose file tools*
The moose likes JDBC and the fly likes tomcat mysql connection issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "tomcat mysql connection issues" Watch "tomcat mysql connection issues" New topic
Author

tomcat mysql connection issues

Edwinsky Ongolovich
Greenhorn

Joined: Jan 07, 2009
Posts: 2
Hi,
My web application works fine for sometime (e.g. a day) and then it fails to connect to mysql. The error reported is that the result set is already closed.
When I restart Tomcat the problem goes away. This only happens on the internet. It does not occur on my local machine.

I'm using:
Tomcat 6.0.18
MySQL 5.0.18
mysql-connector-java-3.1.10-bin

My application involves JSP files that call methods from some Java classes which in turn interact with the mysql database.
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

The error reported is that the result set is already closed.


There may be some code trying to access a result set which is closed or no connection with mysql. First check your source code which interact with mysql (data manipulation with ResultSet etc....)


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Amit Ghorpade
Bartender

Joined: Jun 06, 2007
Posts: 2716
    
    6

This isn't really a JSP question, I'll move it to an appropriate place.


SCJP, SCWCD.
|Asking Good Questions|
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Sounds like that you´re connecting only once and getting hold of the connection in an instance variable or maybe even a static variable.

Don´t do that. A connection is a very expensive resource and should be released as soon as possible. The database server may close the connection when it is opened for a too long time. Which may sometimes be only 10 minutes, which may be over 24 hours. It depends on the database used and its configuration.

At any way, your JDBC code may be broken. You should be acquiring and closing the connection (and statement and resultset!) in the shortest possible scope. Preferably already inside the same method block. To improve connecting performance, use a connection pool (but also then, you still need to write the JDBC code the right way --acquire and close in the shortest possible scope).
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1


Whats detail of the stack trace/log ?

I came across the same problem, with my Tomcat DBCP configured, I was able to remove it. The detail solution I blogged here !

Also, make sure that all connection closed before returning it into a pool, as Bauke suggested.


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Edwinsky Ongolovich
Greenhorn

Joined: Jan 07, 2009
Posts: 2
I would like to thank all of you for your responses.
Bauke Scholtz, your response seemed closer to what I'm trying to do - I've followed your advice. I'm now observing it to see if the problem will recur.
I have another question though. Could you please direct me to material on how to configure connection pooling for what I'm doing ?. The online material I've come across so far is with respect to JNDI and I'm not using that. My attempt to follow some of them (modify context.xml and server.xml) failed - Tomcat would not start.


Thanks in advance.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Edwinsky Ongolovich wrote:
I have another question though. Could you please direct me to material on how to configure connection pooling for what I'm doing ?. The online material I've come across so far is with respect to JNDI and I'm not using that. My attempt to follow some of them (modify context.xml and server.xml) failed - Tomcat would not start.




Connecting database using JNDI is the best method one can have. Its prime advantage is , you can keep you queries and underlying business logic intact , if you want to change the database vendor.

I don't know which resources you are following, but this is best resource to configure Tomcat with MySQL using JNDI.

And as you said you tried but tomcat fails, then post the logs/error you are facing, we try to solve it !!

Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Indeed read the given link. As my fellow poster disabled BB code in his message, the link isn't clickable. Here it is again: resource to configure Tomcat with MySQL using JNDI.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Bauke Scholtz wrote:Indeed read the given link. As my fellow poster disabled BB code in his message,


Now I get to know, why my post doesn't shows any URL. :roll: Thanks for pointer.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: tomcat mysql connection issues