Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Mysql conncections won't close, please help!!

 
White brendan
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am struggling to close mysql connections from my application. As the application continues, more and more connections (threads) open until I get errors back from mysql.
I close the statement and resultset and dereference everything, but the connections increase until i kill the class itself. The class runs in a thread and just loops continously doing some housekeeping on the db.
Any help will be very much appreciated.
Here is my code:
import ie.omk.smpp.*;
import ie.omk.smpp.net.*;
import ie.omk.smpp.message.*;
import java.util.*;
import java.sql.*;
class Ccheckregister extends Thread {
java.sql.Connection conn;
Ccheckregister() {
System.out.println("check register class");
try{
Class.forName("org.gjt.mm.mysql.Driver");
conn = DriverManager.getConnection("jdbc:mysql://**********", "*****", "*****");
}
catch(Exception ex){System.out.println ("sql except check register");}
}
public void run() {
while (true) {
try{
this.sleep(60000);
CReadData read = new CReadData();
String email = "";
String token = "";
Vector vec = new Vector();
Statement st1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st1.executeQuery("SELECT non_verified_received.MSISDN, non_verified_received.message, Users.email FROM non_verified_received, Users WHERE Users.MSISDN = non_verified_received.MSISDN");
while (rs.next()){
email = rs.getString("Users.email");
token = rs.getString("non_verified_received.message");
read.retrieveAndSendMail(token, email, "");
vec.add(rs.getString("non_verified_received.MSISDN"));
}
for(int x = 0; x < vec.size() - 1; x++)
st1.executeUpdate("DELETE FROM non_verified_received WHERE MSISDN = '" + vec.get(x) + "'");
st1.close();
read = null;
email = null;
token = null;
st1 = null;
rs = null;
}
catch(Exception ex){System.out.println("Critical Error: Ccheckregister (sleep)");System.out.println(ex);}
}
}
}
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by White brendan:
Hi,
I am struggling to close mysql connections from my application. As the application continues, more and more connections (threads) open until I get errors back from mysql.
I close the statement and resultset and dereference everything, but the connections increase until i kill the class itself. The class runs in a thread and just loops continously doing some housekeeping on the db.

While the code is not inherently flawed, there is nothing in your code that is causing the problem.
1. Since you sleep 60 seconds between each loop, you should close the connection when you are finished with it, and open a new connection at the beginning of the loop.
2. The run() method above is not the source of your problem. Nowhere in your run() method are additional connections being opened. Are multiple instances of this class being created? Is there some other class that might be creating all the extra connections?
3. While it doesn't matter, you should explicitly close the ResultSet as well. The reason it doesn't matter is that calling another execute method on the statement is supposed to close the ResultSet
4. Setting all the variables to null will not help at all.
If you post the code that uses this class, it might be possible to discover the real problem.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic