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