JAVA: MySql: too many connection

Question

I think that my application is cursed, debug goes where it wants and I don't know why. Line by line debugging seems to analyze also the commented rows. I think that the problems are on my Connection method, I see a significant performance slowdown, and at the third (or fourth nvm) connection I get this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

I'm sure that I close the connection each time I've access to the DB (with finally statement out of the try catch in the implementation).

Here's my connection class:

package Connection;

import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.rmi.RemoteException; import java.sql.*; import java.util.Properties;

public class ConnectionDB {         
    public static ResultSet rs = null;  
    public static Statement stat = null;    
    public static Connection cn = null;

    public static void connect() throws RemoteException     {
            String dbHost="",dbUser="",dbPassword="";
            Properties prop=new Properties();
            try
            {
                //carico il file:
                prop.load(new FileInputStream("***/config.properties"));
                //Leggo le proprietà del file:
                dbHost=prop.getProperty("host");
                dbUser=prop.getProperty("user");
                dbPassword=prop.getProperty("password");
            }catch(FileNotFoundException fe){
                System.out.println("config file not found");
            }catch(IOException ex){
                System.out.println("Error reading config file");
            }
            try 
            {
                String driver = "com.mysql.jdbc.Driver";
                Class.forName(driver);
                dbHost = "jdbc:mysql://"+dbHost;
                cn = DriverManager.getConnection(dbHost,dbUser,dbPassword);
                stat = cn.createStatement();
            }catch(SQLException e){
                System.out.println("Can't connect to the DB");
            }
            catch(ClassNotFoundException cln){
                System.out.println("Error using JDBC driver");
            }   
    }   

    public static void disconnect() throws RemoteException  {
            try{
                if(rs != null) rs.close();
                if(stat != null) stat.close();
                if(cn != null) cn.close();
            }
            catch(SQLException sqlEx){
                System.out.println("Error: disconnect");
            }   
      }
}

http://stackoverflow.com/questions/1392304/com-mysql-jdbc-exceptions-jdbc4-mysqlnontransientconnectionexception-in-mysql — kosa, Jul 19 '12 at 15:23
If you check in Workbench, how many connections are actually open? Less than the allowed maximum? And are all connections that you suppose to be closed also really closed? — fvu, Jul 19 '12 at 15:25
how many times do you call connect before disconnecting? one more thing .. every thing is static.. if you do connect() connect() disconnect() how will you ever disconnect the first connection? — Osama Javed, Jul 19 '12 at 15:41
@fvu:How can i see that (using netbeans as IDE)? Basically the slowdown comes at first connection, and i don't know why... — Stiva, Jul 19 '12 at 15:45
@thinksteep I think that it's better to modify my own "my.cnf" file allowing more connections, but i don't think that's the solution, this is just a temporary fix... (but i'm a newbie so all suggestion could be great for me ^_^) — Stiva, Jul 19 '12 at 15:45
@OsamaJaved I call connect method just one time, the slowdown comes at first connect, then for each consecutive connection this effect increase a lot until i get this error :S — Stiva, Jul 19 '12 at 15:46
ok in the connect method put a print statement to see how many times it gets called ( if it is called only once you should not get too many connections error).. creating new connections is expensive and there fore connection pools are used to reuse connections (See answers below).. so for performance use connection pools.. for error , I dont believe you are just calling it once :) — Osama Javed, Jul 19 '12 at 15:50
@Stiva download and install MySQL Workbench CE to inspect the server while it's running. — fvu, Jul 19 '12 at 15:53
Are you trying to make a singleton? Why do you have a statement and a resultset as members of a class that seems like all it should do is create a connection to a database? — ChadNC, Jul 19 '12 at 15:53
yes i'm trying to use connection as a singleton to manage the connection, btw it doesn't seems a nice solution :S — Stiva, Jul 19 '12 at 15:57
I suggest you to profile your app with profiler, like JVisualVM, look which method takes longest time, that will solve problem. — Ramanqul Buzaubak, Jul 19 '12 at 16:49
Resolved... just used @OsamaJaved's suggestion, there was a method (into a cycle) that was without the proper disconnect invocation. Sorry for "timewasting" and thank you all! — Stiva, Jul 19 '12 at 17:06

score 2 · Answer 1 · answered Jul 19 '12 at 18:56

The disconnect method may need following enhancement. In this one, we close ResultSet, Statement & Connection separately. This will save from situation where individual exception will not result into not closing the connection object.

public static void disconnect() throws RemoteException {
        try{
            if(rs != null) rs.close();
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   


        try{
           if(stat != null) stat.close();
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   

        try{
            if(cn != null) cn.close();
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   
  }

score 0 · Answer 2 · edited May 23 '17 at 11:44

There're a couple of options that I think you can try out:

As in the comments provided, you can increase the max number of connections allowed.
You talked about performance degradation. If this has to do with number of connection open, I would suggest that you consider reusing your connection. In this scenario using a connection pool might be of help. This might be of help in this case.

score 0 · Answer 3 · answered Jul 19 '12 at 15:57

If you're using a developer version of your database it probably has a restriction on the number of connections that can be made. So you'll need to manage them better which means that you should make sure they are closed when you've finished with them. (EDIT: just realised you're using mysql so this shouldn't be an issue)

Also, you mentioned that your debugger is going through comments - this is typically the case where your code is out of synch with your build. Clean your build (in eclipse it would be Project > clean ... > clean all projects) and that problem should disappear.

I've tried to Clean all projects, but the sync problem persist :( — Stiva, Jul 19 '12 at 16:18

score 0 · Accepted Answer · edited May 23 '17 at 11:44

Normally there is limited number of connections available for user. Additionally, establishing connections is costly operation. Therefore, if you experience performance problems you should start using connection pool, discussion about connection pools

Regarding your code, make sure you always release connections in finally block. Also we don't know what kind of queries you run. Print your queries and try running them manually in mysql and see if the problems is your SQLs, not Java. Also you never close FileInputStream meaning you can run out of file handlers.

In case you run the same statement in a loop consider using PreparedStatements and batch updates and transactions. If you use transactions, make sure you don't have much data in uncommitted state.

If you want to analyze performance of your queries you can use JAMon (Java Application Monitor)

Suggestion to increase number of connections is similar to advising a person with diarrhea to eat more food.

score 0 · Answer 5 · answered May 13 '14 at 12:01

0

Try this

if(cn == null){
    String driver = "com.mysql.jdbc.Driver";
    Class.forName(driver);
    dbHost = "jdbc:mysql://"+dbHost;
    cn = DriverManager.getConnection(dbHost,dbUser,dbPassword);
}

answered May 13 '14 at 12:01

Hammad Ali Butt

75
4
13

JAVA: MySql: too many connection

5 Answers5

Linked