title: Tomcat Connection pool creating too many connections, stuck in sleep mode
body: <p>I'm using Tomcat 6.0.29, with Tomcat 7's connection pool and MySQL. Testing my application, it doesn't reuse anything from the pool, but ends up creating a new pool, to eventually where I cannot use the database because there are hundreds of sleeping connections in the pool when the max active size for the pool is set to 20. </p>
  
  <p>See here for reference:</p>
  
  <pre><code>+----+------+-----------------+--------+---------+------+-------+------------------+
  | Id | User | Host            | db     | Command | Time | State | Info             |
  +----+------+-----------------+--------+---------+------+-------+------------------+
  |  2 | root | localhost:51877 | dbname | Sleep   |    9 |       | NULL             |
  |  4 | root | localhost       | NULL   | Query   |    0 | NULL  | show processlist |
  |  5 | root | localhost:49213 | dbname | Sleep   |   21 |       | NULL             |
  |  6 | root | localhost:53492 | dbname | Sleep   |   21 |       | NULL             |
  |  7 | root | localhost:46012 | dbname | Sleep   |   21 |       | NULL             |
  |  8 | root | localhost:34964 | dbname | Sleep   |   21 |       | NULL             |
  |  9 | root | localhost:52728 | dbname | Sleep   |   21 |       | NULL             |
  | 10 | root | localhost:43782 | dbname | Sleep   |   21 |       | NULL             |
  | 11 | root | localhost:38468 | dbname | Sleep   |   21 |       | NULL             |
  | 12 | root | localhost:48021 | dbname | Sleep   |   21 |       | NULL             |
  | 13 | root | localhost:54854 | dbname | Sleep   |   21 |       | NULL             |
  | 14 | root | localhost:41520 | dbname | Sleep   |   21 |       | NULL             |
  | 15 | root | localhost:38112 | dbname | Sleep   |   13 |       | NULL             |
  | 16 | root | localhost:39168 | dbname | Sleep   |   13 |       | NULL             |
  | 17 | root | localhost:40427 | dbname | Sleep   |   13 |       | NULL             |
  | 18 | root | localhost:58179 | dbname | Sleep   |   13 |       | NULL             |
  | 19 | root | localhost:40957 | dbname | Sleep   |   13 |       | NULL             |
  | 20 | root | localhost:45567 | dbname | Sleep   |   13 |       | NULL             |
  | 21 | root | localhost:48314 | dbname | Sleep   |   13 |       | NULL             |
  | 22 | root | localhost:34546 | dbname | Sleep   |   13 |       | NULL             |
  | 23 | root | localhost:44928 | dbname | Sleep   |   13 |       | NULL             |
  | 24 | root | localhost:57320 | dbname | Sleep   |   13 |       | NULL             |
  | 25 | root | localhost:54643 | dbname | Sleep   |   29 |       | NULL             |
  | 26 | root | localhost:49809 | dbname | Sleep   |   29 |       | NULL             |
  | 27 | root | localhost:60993 | dbname | Sleep   |   29 |       | NULL             |
  | 28 | root | localhost:36676 | dbname | Sleep   |   29 |       | NULL             |
  | 29 | root | localhost:53574 | dbname | Sleep   |   29 |       | NULL             |
  | 30 | root | localhost:45402 | dbname | Sleep   |   29 |       | NULL             |
  | 31 | root | localhost:37632 | dbname | Sleep   |   29 |       | NULL             |
  | 32 | root | localhost:56561 | dbname | Sleep   |   29 |       | NULL             |
  | 33 | root | localhost:34261 | dbname | Sleep   |   29 |       | NULL             |
  | 34 | root | localhost:55221 | dbname | Sleep   |   29 |       | NULL             |
  | 35 | root | localhost:39613 | dbname | Sleep   |   15 |       | NULL             |
  | 36 | root | localhost:52908 | dbname | Sleep   |   15 |       | NULL             |
  | 37 | root | localhost:56401 | dbname | Sleep   |   15 |       | NULL             |
  | 38 | root | localhost:44446 | dbname | Sleep   |   15 |       | NULL             |
  | 39 | root | localhost:57567 | dbname | Sleep   |   15 |       | NULL             |
  | 40 | root | localhost:56445 | dbname | Sleep   |   15 |       | NULL             |
  | 41 | root | localhost:39616 | dbname | Sleep   |   15 |       | NULL             |
  | 42 | root | localhost:49197 | dbname | Sleep   |   15 |       | NULL             |
  | 43 | root | localhost:59916 | dbname | Sleep   |   15 |       | NULL             |
  | 44 | root | localhost:37165 | dbname | Sleep   |   15 |       | NULL             |
  | 45 | root | localhost:45649 | dbname | Sleep   |    1 |       | NULL             |
  | 46 | root | localhost:55397 | dbname | Sleep   |    1 |       | NULL             |
  | 47 | root | localhost:34322 | dbname | Sleep   |    1 |       | NULL             |
  | 48 | root | localhost:54387 | dbname | Sleep   |    1 |       | NULL             |
  | 49 | root | localhost:55147 | dbname | Sleep   |    1 |       | NULL             |
  | 50 | root | localhost:47280 | dbname | Sleep   |    1 |       | NULL             |
  | 51 | root | localhost:56856 | dbname | Sleep   |    1 |       | NULL             |
  | 52 | root | localhost:58369 | dbname | Sleep   |    1 |       | NULL             |
  | 53 | root | localhost:33712 | dbname | Sleep   |    1 |       | NULL             |
  | 54 | root | localhost:44315 | dbname | Sleep   |    1 |       | NULL             |
  | 55 | root | localhost:54649 | dbname | Sleep   |   14 |       | NULL             |
  | 56 | root | localhost:41202 | dbname | Sleep   |   14 |       | NULL             |
  | 57 | root | localhost:59393 | dbname | Sleep   |   14 |       | NULL             |
  | 58 | root | localhost:38304 | dbname | Sleep   |   14 |       | NULL             |
  | 59 | root | localhost:34548 | dbname | Sleep   |   14 |       | NULL             |
  | 60 | root | localhost:49567 | dbname | Sleep   |   14 |       | NULL             |
  | 61 | root | localhost:48077 | dbname | Sleep   |   14 |       | NULL             |
  | 62 | root | localhost:48586 | dbname | Sleep   |   14 |       | NULL             |
  | 63 | root | localhost:45308 | dbname | Sleep   |   14 |       | NULL             |
  | 64 | root | localhost:43169 | dbname | Sleep   |   14 |       | NULL             |
  </code></pre>
  
  <p>It creates exactly 10 for each request, which is the minIdle &amp; InitialSize attribute as seen below.</p>
  
  <p>Here is the sample test code embedded into a jsp page. The code is not the code in my application and just used to see if the issue was with my code, but the problem still persisted.</p>
  
  <pre><code>Context envCtx;
  envCtx = (Context) new InitialContext().lookup("java:comp/env");
  DataSource datasource = (DataSource) envCtx.lookup("jdbc/dbname");
  Connection con = null;
  
  try {
    con = datasource.getConnection();
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("select * from UserAccount");
    int cnt = 1;
    while (rs.next()) {
        out.println((cnt++)+". Token:" +rs.getString("UserToken")+
          " FirstName:"+rs.getString("FirstName")+" LastName:"+rs.getString("LastName"));
    }
    rs.close();
    st.close();
  } finally {
    if (con!=null) try {con.close();}catch (Exception ignore) {}
  }
  </code></pre>
  
  <p>Here is my context.xml file:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;Context&gt;
      &lt;Resource name="jdbc/dbname" 
                auth="Container" 
                type="javax.sql.DataSource" 
                factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
                testWhileIdle="true"
                testOnBorrow="true"
                testOnReturn="false"
                validationQuery="SELECT 1"
                validationInterval="30000"
                timeBetweenEvictionRunsMillis="30000"
                maxActive="20" 
                minIdle="10" 
                maxWait="10000" 
                initialSize="10"
                removeAbandonedTimeout="60"
                removeAbandoned="true"
                logAbandoned="true"
                minEvictableIdleTimeMillis="30000" 
                jmxEnabled="true"
                jdbcInterceptors=
  "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
                username="" 
                password="" 
                driverClassName="com.mysql.jdbc.Driver"
                url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true&amp;amp;useUnicode=true&amp;amp;characterEncoding=utf8"/&gt;
  
  &lt;WatchedResource&gt;WEB-INF/web.xml&lt;/WatchedResource&gt;
  &lt;WatchedResource&gt;META-INF/context.xml&lt;/WatchedResource&gt;
  &lt;/Context&gt;
  </code></pre>
  
  <p>I'm sure I can use removeAbandonedTimeout to a low number and it would purge all these sleeping connections, but that wouldn't fix the real problem would it?   Does anyone know what I'm doing wrong? Thank you very much.</p>