Mysql Exception in JAVA project

Question

In a simple java application with jdbc and mysql only I am getting error :

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

Threading is used to open and close connection to Mysql

I have tried increasing the max_connection variable of MySQL but it only delays the exception as it will again arise if the number of threads are increased

Please provide root cause and resolution.

import java.sql.DriverManager;
import java.sql.SQLException;
public class Connection1 extends Thread{
public java.sql.Connection con = null;

public Connection1() throws ClassNotFoundException, SQLException {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        for(int i=0;i<500;i++) {
            new Thread(new Connection1().new  StartTester(i)).start();
        }
    }

    public void looper() throws ClassNotFoundException, SQLException {
        for(int i=0;i<2;i++) {
            new Thread(this.new  StartTester(i)).start();
        }
    }

    public void connect() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","esignal");
        System.out.println("con success "+con);
        con.close();
        System.out.println("con Closed "+con);
    }

    class StartTester implements Runnable {

        public StartTester(int count) {
            System.out.println("Thread count "+count);
        }

        @Override
        public void run() {
            try {
                connect();
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

Answer 1

You are spawning too many Threads (500) and there are limited number (say 20) of connection available on MySQL. When you try to create DB connection, it will take sometime. But threads will start very quickly. So almost all the threads are trying to connect to DB simultaneously but only 20 (or little more then 20) will get the connection and reset will throw the Exception that you have mentioned.

• You should use connectionPool to manage connection properly.
• Otherwise have a look at following link. It will tell you how to handle Connections properly.

I would also recommend you to use ExecutionService to limit to number of thread and it is a cleaner approach (this is what I do). You should use create only 20 active (or whatever max connection count) threads and reset in ready to run mode. Hence recommending ExecutionService (Easiest to use).

My sample solution will be (you need to do proper implementation of connect() method like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

public class Connection1 extends Thread {

    public static void main(String[] args) throws ClassNotFoundException,
    SQLException {
        // Number of max DB connection allowed
        int poolSize = 20;

        ExecutorService execService = Executors.newFixedThreadPool(poolSize);
        List<Future<String>> futureList = new ArrayList<Future<String>>();

        for (int i = 0; i < 500; i++) {
            System.out.println("Thread count " + i);
            Callable<String> worker = new StartTester();
            Future<String> future = execService.submit(worker);
            futureList.add(future);
        }
    }
}

class StartTester implements Callable<String> {
    @Override
    public String call() {
        try {
            connect();
            return "SUCCESS";
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return "FAILURE";
    }

    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
                "root", "esignal");
        System.out.println("con success " + con);
        con.close();
        System.out.println("con Closed " + con);
    }
}

Answer 2

Please check the following:-

1. Check the opened connections in database.
2. Check if your application is opening too many connections and not closing them.

Answer 3

Try adding the 'synchronised' keyword to the methods that open and close connections

Answer 4

Like Dennis said you should check if your application keep opening connections. If this is not the case then you should check how many connections your Database can handle (look in your DB configuration).

I do not know right now how many connections are established before you run your application. Is your Database productive or do you have a private database which run local?

If there are other applications or useres that use your database it is possible that connections to your DB has reached the maximum or you have not enough RAM. If you do not have enough RAM it is possible that mysql do not open more connections.