<p>I have a webapp that uses JNDI lookups to get a connection to the database.</p>

<p>The connection works fine and returns the query no problems. The issue us that the connection does not close properly and is stuck in the 'sleep' mode (according to mysql administrator). This means that they become unusable nad then I run out of connections.</p>

<p>Can someone give me a few pointers as to what I can do to make the connection return to the pool successfully.</p>

<pre><code>public class DatabaseBean {

private static final Logger logger = Logger.getLogger(DatabaseBean.class);

private Connection conn;
private PreparedStatement prepStmt;

/**
 * Zero argument constructor
 * Setup generic databse connection in here to avoid redundancy
 * The connection details are in /META-INF/context.xml
 */
public DatabaseBean() {
    try {
        InitialContext initContext = new InitialContext();
        DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/mysite");
        conn = ds.getConnection();
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was a problem with the database connection.");
        logger.fatal(SQLEx);
        logger.fatal(SQLEx.getCause());
    }
    catch (NamingException nameEx) {
        logger.fatal("There was a naming exception");
        logger.fatal(nameEx);
        logger.fatal(nameEx.getCause());
    }
}

/**
 * Execute a query. Do not use for statements (update delete insert etc).
 *
 * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null.
 * @see #executeUpdate() for running update, insert delete etc.
 */

public ResultSet executeQuery() {
    ResultSet result = null;
    try {
        result = prepStmt.executeQuery();
        logger.debug(prepStmt.toString());
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was an error running a query");
        logger.fatal(SQLEx);
    }
    return result;
}
</code></pre>

<p><em>SNIP</em></p>

<pre><code>public void close() {
    try {
        prepStmt.close();
        prepStmt = null;

        conn.close();
        conn = null;
    } catch (SQLException SQLEx) {
        logger.warn("There was an error closing the database connection.");
    }
}
}
</code></pre>

<p>This is inside a javabean that uses the database connection.</p>

<pre><code>public LinkedList&lt;ImportantNoticeBean&gt; getImportantNotices() {

    DatabaseBean noticesDBBean = new DatabaseBean();
    LinkedList&lt;ImportantNoticeBean&gt; listOfNotices = new LinkedList&lt;ImportantNoticeBean&gt;();

    try {
        PreparedStatement preStmt = noticesDBBean.getConn().prepareStatement("SELECT pseudonym, message, date_to, date_from " +
                "FROM importantnotices, users " +
                "WHERE importantnotices.username = users.username " +
                "AND NOW() &gt;= date_from AND NOW() &lt;= date_to;");

        noticesDBBean.setPrepStmt(preStmt);
        ResultSet result = noticesDBBean.executeQuery();

        while (result.next()) {
            ImportantNoticeBean noticeBean = new ImportantNoticeBean();

            noticeBean.setAuthor(result.getString("pseudonym"));
            noticeBean.setMessage(result.getString("message"));
            noticeBean.setDateTo(result.getDate("date_to"));
            noticeBean.setDateFrom(result.getDate("date_from"));

            listOfNotices.add(noticeBean);
        }

        result.close();

    } catch (SQLException SQLEx) {
        logger.error("There was an error in ImportantNoticesBean.getImportantNotices()");
        logger.error(SQLEx);
    } finally {
        noticesDBBean.close();
    }
    return listOfNotices;
}

&lt;Context reloadable="true"&gt;

    &lt;Resource name="jdbc/mysite"
              auth="Container"
              type="javax.sql.DataSource"
              username="user"
              password="password"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysite"
              maxActive="10"
              maxIdle="5"
              maxWait="6000"
              removeAbandoned="true"
              logAbandoned="false"
              removeAbandonedTimeout="20"
            /&gt;
&lt;/Context&gt;
</code></pre>

[{'answer_id': 53285, 'author': 'Binil Thomas', 'author_id': 3973, 'author_profile': 'https://Stackoverflow.com/users/3973', 'pm_score': 2, 'selected': False, 'text': "<p>You seem to be closing the connection properly - except for the case where prepStmt.close() throws a SQLException, I can't find a connection leak.</p>\n\n<p>What pool implementation are you using? When you close a connection, the pool need not close the underlying MySQL connection immediately - after all that is the point of a connection pool! So from MySQL side, the connections would look alive, although your app is not using any; they might simply be held by the TC connection pool.</p>\n\n<p>You might want to experiment with the settings of the connection pool.Ask it to shrink the pool when the system is idle. Or, ask it to refresh all connections periodically. Or, have a strict upper bound on the number of concurrent connections it ever gets from MySQL etc.</p>\n\n<p>One way to check if your code has a connection leak is to force the ds.getConnection() to always open a new physical connection and conn.close() to release the connection (if your connection pool has settings for those). Then if you watch the connections on MySQL side, you might be able to figure out if the code really has a connection leak or not.</p>\n"}, {'answer_id': 53912, 'author': 'John Meagher', 'author_id': 3535, 'author_profile': 'https://Stackoverflow.com/users/3535', 'pm_score': 0, 'selected': False, 'text': '<p>One thing that @binil missed, you are not closing the result set in the case of an exception.  Depending on the driver implementation this may cause the connection to stay open.  Move the result.close() call to the finally block.  </p>\n'}, {'answer_id': 54095, 'author': 'ScArcher2', 'author_id': 1310, 'author_profile': 'https://Stackoverflow.com/users/1310', 'pm_score': 2, 'selected': False, 'text': '<p>This is a similar question - <a href="https://stackoverflow.com/questions/15949/javatomcat-dying-databse-connection#16168">Connection Pool Settings for Tomcat</a> </p>\n\n<p>This is my response to that question and it fixed the problem for the other guy. It may help you out too.</p>\n\n<p><a href="http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html" rel="nofollow noreferrer">Tomcat Documentation</a></p>\n\n<p>DBCP uses the Jakarta-Commons Database Connection Pool. It relies on number of Jakarta-Commons components:</p>\n\n<pre><code>* Jakarta-Commons DBCP\n* Jakarta-Commons Collections\n* Jakarta-Commons Pool\n</code></pre>\n\n<p>I\'m using the same connection pooling stuff and I\'m setting these properties to prevent the same thing it\'s just not configured through tomcat.\nBut if the first thing doesn\'t work try these.</p>\n\n<pre><code>testWhileIdle=true\ntimeBetweenEvictionRunsMillis=300000\n</code></pre>\n'}, {'answer_id': 58778, 'author': 'Doug Miller', 'author_id': 3431280, 'author_profile': 'https://Stackoverflow.com/users/3431280', 'pm_score': 2, 'selected': False, 'text': '<p>Ok I might have this sorted. I have changed the database config resource to the following:</p>\n\n<pre><code>*SNIP*\nmaxActive="10"\nmaxIdle="5"\nmaxWait="7000"\nremoveAbandoned="true"\nlogAbandoned="false"\nremoveAbandonedTimeout="3"\n*SNIP*\n</code></pre>\n\n<p>This works well enough for now. What is happening, afaik, is that once I reach the ten connections then Tomcat is checking for abandoned connections (idle time > 3). It does this in a batch job each time that max connections is reached. The potential issue with this is if i need more than 10 queries run at the same time (not unique to me). The important thing is that removeAbandonedTimeout is less than maxWait.</p>\n\n<p>Is this what should be happening? ie Is this the way that the pool should operate? If it is is seems, at least to me, that you would wait until something (the connection) is broken before fixing rather than not letting it \'break\' in the first place. Maybe I am still not getting it.</p>\n'}, {'answer_id': 1085473, 'author': 'Igor Zelaya', 'author_id': 22769, 'author_profile': 'https://Stackoverflow.com/users/22769', 'pm_score': 0, 'selected': False, 'text': '<p>I am using the same configuration as you are.  If the connection in mysql administrator(windows) shows that it is in sleep mode it only means that is pooled but not in use.  I checked this running a test program program with multiple threads making random queries to Mysql.  if it helps here is my configuration:</p>\n\n<pre><code>        defaultAutoCommit="false"\n        defaultTransactionIsolation="REPEATABLE_READ"\n        auth="Container"\n        type="javax.sql.DataSource"\n        logAbandoned="true" \n          removeAbandoned="true"\n        removeAbandonedTimeout="300" \n        maxActive="-1"\n        initialSize="15"\n        maxIdle="10"\n        maxWait="10000" \n        username="youruser"\n        password="youruserpassword"\n        driverClassName="com.mysql.jdbc.Driver"\n        url="jdbc:mysql://yourhost/yourdatabase"/&gt;\n</code></pre>\n'}, {'answer_id': 4121987, 'author': 'Doug Miller', 'author_id': 3431280, 'author_profile': 'https://Stackoverflow.com/users/3431280', 'pm_score': 2, 'selected': True, 'text': '<blockquote>\n  <p>The issue us that the connection does not close properly and is stuck in the \'sleep\' mode</p>\n</blockquote>\n\n<p>This was actually only half right.</p>\n\n<p>The problem I ran into was actually that each app was defining a new connection to the database sever. So each time I closed all the connections App A would make a bunch of new connections as per it\'s WEB.xml config file and run happily. App B would do the same. The problem is that they are <em>independent pools</em> which try to grab up to the server defined limit. It is a kind of race condition I guess. So when App A has finished with the connections it sits waiting to to use them again until the timeout has passed while App B who needs the connection now is denied the resources even though App A has finished with the and should be back in the pool. Once the timeout has passed, the connection is freed up and B (or C etc) can get at it again.</p>\n\n<p>e.g. if the limit is 10 (mySQL profile limit) and each app has been configured to use a max of 10 the there will be 20 attempts at connections. Obviously this is a bad situation.</p>\n\n<p>The solution is to RTFM and put the <a href="http://tomcat.apache.org/tomcat-5.5-doc/config/context.html" rel="nofollow">connection details in the right place</a>. This does make shared posting a pain but there are ways around it (such as linking to other xml files from the context).</p>\n\n<p>Just to be explicit: I put the connection details in the WEB.xml for each app and the had a fight about it.</p>\n'}]
['https://Stackoverflow.com/questions/53128', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3431280/']