Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

tomcat mysql connection issues

 
Edwinsky Ongolovich
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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....)
 
Amit Ghorpade
Bartender
Posts: 2854
10
Fedora Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This isn't really a JSP question, I'll move it to an appropriate place.
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Edwinsky Ongolovich
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic