• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Mysql conncections won't close, please help!!

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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);}
}
}
}
 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic