4 Replies Latest reply on Jun 10, 2009 4:18 AM by newnet

    Cannot get a connection, pool error Timeout waiting for idle object

    newnet

      my connection pool setting is

        maxActive="3" minIdle="2"                maxWait="10000"              removeAbandoed="true" logAbandoned="true"         removeAbandonedTimeout="30"         autoreconnection="true"  

      we have 7 people hitting the group of search functions ,
      about 3 minutes I get
      I got error like

      org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object         at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104)         at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)         at Deferment.showResult.checStudent(showResult.java:135)         at Deferment.showResult.doPost(showResult.java:99)         at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)         at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)         at org.apache.catalina.servlets.InvokerServlet.serveRequest(InvokerServlet.java:402)         at org.apache.catalina.servlets.InvokerServlet.doPost(InvokerServlet.java:170)         at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)         at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

      I just wonder
      1) Cannot get a connection, pool error Timeout waiting for idle object-> cause by connection pool leaking or other problem.
      2) Setting the maxactive to -1 ( suggestion from Google) , if I set my connection to ulimit , waht should I put on minIdle="2"
      3) How can I test my connection pool have problem or not ? is
      SHOW PROCESSLIST; tolding me someinformation? I use Mysql

      Thank you!

        • 1. Re: Cannot get a connection, pool error Timeout waiting for idle object
          843859
          Do you close the connection (and statement and resultset) after use? Only then the connection can be returned to the pool and reused for the next action.
          • 2. Re: Cannot get a connection, pool error Timeout waiting for idle object
            newnet
            I try something like
            ps2 = conn.prepareStatement(target);
                        ps2.setString(1, UNumber); // set input parameter
                        rs = ps2.executeQuery();
            
                        sendMail = 0;//how many rows we can find.
                        while (rs.next()) {
                            sendMail++;
                        }
                        ps2.close();
                        rs.close();
                        conn.close();
              if ((count1 == 0) || (count2 == 0)) {
                            conn.rollback();
                        } else {
                            conn.commit();
                            conn.setAutoCommit(true);
                        }
                        ps.close();
                        calstat2.close();
                        conn.close();
            calstat = (CallableStatement) conn.prepareCall("{call findStudent}");
                        calstat.executeQuery();
                        calstat.close();
                        conn.close();
            is that enought? what else I should do to make sure I did close all the connection, after I use it ?
            thank you
            • 3. Re: Cannot get a connection, pool error Timeout waiting for idle object
              843859
              Close in the finally block so that they also get closed in case of an exception.
              You was given a link to a DAO tutorial before. This aspect is also explained in the article's text.

              Apart from this problem, are you familiar with the SQL COUNT() function? According to your code apparently not. Just let the DB do the count and return a single integer instead of ineffectively hauling all of the rows into Java's memory and doing the count in Java.
              • 4. Re: Cannot get a connection, pool error Timeout waiting for idle object
                newnet
                1. Thank you fot the advice
                while (rs.next()) {
                                sendMail = rs.getInt("sendMail");
                            }
                2. I will go back to read you tutorials again, I did visit your blog a few times. but I get half understand it. so..
                3. if I wan to test my pool , wha suggestion you can give??
                Thank you