CODEWITHSUNDEEP
javamysql
124697
124697

Reputation: 21893

What's wrong with my code Data source rejected establishment of connection, message from server: "Too many connections"

I am getting this error from the database

Data source rejected establishment of connection, message from server: "Too many connections"

I am closing my connections properly, I don't know why i am getting this error when my thread pool is only 5 and i think i am closing the connections. Anyone know why.

Below is my code. I have changed a few things to stay anonymous 

public class myMain {

    public static void main(String[] args) {
        ExecutorService executor = Executors.newFixedThreadPool(5);
        for (int i = 0; i < (569743 / 100); i++) {
            try {
                MyClass mclass = new MyClass();
                executor.execute(mclass);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        executor.shutdown();
        try {
            executor.awaitTermination(Long.MAX_VALUE, TimeUnit.MINUTES);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("Finished all threads");
    }

}

.

public class MyClass implements Runnable {

    public MyClass(){
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost/mytable?" + "user=&password=");
    }

    public  void run() {
        try {
            String sql = "My select STATEMENT";
            PreparedStatement stmt = connect.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                try {

                    int id = rs.getInt("movie_id");
                    String title = rs.getString("title");
                    int year = rs.getInt("year");
                    String trailerUrl = rs.getString("trailerUrl");
                    if (trailerUrl == null || (trailerUrl != null && trailerUrl.length() == 0)) {
                        YoutubeTrailerFetcher youtube = new YoutubeTrailerFetcher(title, year);
                        String trailer = youtube.getVideoId();

                        String updateSql = "my UPDATE Statement";
                        Statement updateStm = connect.createStatement();
                        updateStm.executeUpdate(updateSql);
                        updateStm.close();

                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

            }
            connect.close();
            stmt.close();
            rs.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

}

Upvotes: 0

Views: 260

Answers (1)

JB Nizet
JB Nizet

Reputation: 691805

The main problem with your code is that you open a connection in the main thread, when calling the MyClass constructor, but the connection is only closed after the thread pool has finished executing the task. So you're trying to open 5697 connections, which is a whole lot. And these connections wait for being closed that one of the 5 threads in the pool has finished executing the task.

As I said in my comment: the method opening the connection should be the one closing it. And it should be closed in a finally block to ensure it is closed, whatever happens in the method, or better, you should use the try-with-resources statement to ensure that

Upvotes: 2

Related Questions