Too many connections error using DB pooling

Question

So i am developing a web service which should handle a good amount of load. And i used snaq DBpooling. But the problem is I always get the error

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

I have checked other threads and mostly the issue is with improper conn.close() calls. But here, i am calling it inside the finally method as suggested everywhere. Here is my code. COuld you please suggest whats wrong.

public class ExampleServlet extends HttpServlet {

    public static final String MESSAGE = "message";

    private String message;
    private ConnectionPool pool;

    @Override
    public void init(final ServletConfig config) throws ServletException {
        super.init(config);
        message = config.getInitParameter(MESSAGE);
        try {
            Class c = Class.forName("com.mysql.jdbc.Driver");
            Driver driver = (Driver) c.newInstance();
            DriverManager.registerDriver(driver);
            String url = "jdbc:mysql://localhost/db15619";
            pool = new ConnectionPool("local", 20, 25, 30, 180, url, "root", "db15319root");
        } catch (/*InstantiationException | ClassNotFoundException | SQLException */ Exception ex) {
            System.out.println("Error:" + ex.toString());
        }
    }

    @Override
    protected void doGet(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException {
        PrintWriter writer = resp.getWriter();
        //System.out.println(req.getQueryString());
        String[] string_key = req.getQueryString().split("=|\\&");
        //exchange.getResponseHeaders().put(Headers.CONTENT_TYPE, "text/plain");
        string_key[3] = string_key[3].replace('+', ' ');
        String query_str = "select tweetid,score,tweettext from tweets where userid = '" + string_key[1] + "' and dttm = '" + string_key[3] + "';";

        Connection conn = null;

        long timeout = 10000;
        try {
            conn = pool.getConnection();
        } catch (SQLException ex) {
            System.out.println("Error While Creating Connection :=> " + ex.toString());
        }

        if (conn != null) {
            try {
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(query_str);
                String str = "CloudNinjas," + "0369-8371-3735" + "," + "9830-4777-6269" + "," + "3472-5239-0207" + "\n";
                while (rs.next()) {
                    for (int i = 1; i <= 3; i++) {
                        str = str + rs.getString(i);
                        if (i != 3) {
                            str = str + ":";
                        } else {
                            str = str + "\n";
                        }
                    }
                }
                byte[] bytes = str.getBytes(Charset.forName("UTF-8"));
                System.out.println(str);
                System.out.println(bytes);
                writer.write(str);

            } catch (SQLException ex) {
                System.out.println("Error While Creating Connection :=> " + ex.toString());
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException ex) {
                    }
                }
            }
        }
        writer.close();

    }

    @Override
    protected void doPost(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

Please make sure your code is well formatted when posting (most IDEs can do this for you) — Romski, Nov 06 '14 at 02:36

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Either too many clients are accessing the pool at the same time, or some clients aren't giving back their connections. I presume the latter.

You could try testing which by making single calls sequentially with a small pool (say 5) and seeing if the problem occurs on the 6th call. This would show that clients aren't cleaning up properly.

The JDK7 try with resources feature was created because so many people have the same problem (example: How should I use try-with-resources with JDBC?).

I also see that you are not closing your Statement or Resultset (see Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards?) which may be related or contribute to other problems.

edited May 23 '17 at 10:25

Community

1
1

answered Nov 06 '14 at 02:27

Romski

1,912
1
12
27

Upvoted, because you reminded the OQ to close Statement and Resultset objects. The connections won't drop until Resultsets can be gced. If you're connecting to a dbms with server-side cursor objects, failing to release Resultsets can take down the dbms. – O. Jones Nov 06 '14 at 02:31
Used the suggestions you gave but it still wont work. If i run a single query with just 2 connections, it runs well even if i query 100 times manually. Is it that the load is just too much to handle. Because it runs creates 40 connections and runs for like 3 seconds and then i get a read timeout error – Sparsh Nov 06 '14 at 05:19
It could well be that your pool is exhausted because there are more clients than it can service. You could try upping your pool size, but I would also look at why you have so many long-running queries. – Romski Nov 06 '14 at 21:59

Too many connections error using DB pooling

1 Answers1