CODEWITHSUNDEEP
javamysqldatabase
Sparsh
Sparsh

Reputation: 3

Too many connections error using DB pooling

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);
    }
}

Upvotes: 0

Views: 690

Answers (1)

Romski
Romski

Reputation: 1942

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.

Upvotes: 1

Related Questions