0

The code below doesn't appear to close MySQL connections properly even though we're closing resources in reverse order. When this code is run again, it seems to reuse the same MySQL process thread, which you can see below from looking at the MySQL process list cross-referenced with the actual MySQL queries run (pulled from mysql.general_log).

What are we doing wrong?

We're using Tomcat 6.0 (Don't ask. You don't want to know.) and Java 1.6.05.

Code:

String cat_name = request.getParameter("category");
if (cat_name != null) {
    DBWrapper db = new DBWrapper();
    ResultSet rs = null;
    try {     
      String parent_cat_name = null;      
      rs = db.executeQuery("SELECT cat_name FROM CATEGORY WHERE cat_code = (SELECT cat_main_code FROM category WHERE cat_name='" + cat_name + "')" );
      if (rs != null && rs.first()) {
        parent_cat_name = rs.getString("cat_name");
        page_title = title_prefix + parent_cat_name + " > " + cat_name;
      } else {
        page_title = title_prefix + cat_name;
      }
    } catch (Exception e) {
      System.out.println(e);
    } finally {
       try {
         if (rs != null) rs.close();
         if (db != null) db.closeConnection();
       } catch (Exception e) {
         System.out.println(e);     
       }
    }
}

DBWrapper.java:

    public class DBWrapper {
        private Connection con = null;
        private Statement stmt = null;
        private DataSource ds;
        private HashMap tables;

  public DBWrapper(){

        try {
            Context ctx = new InitialContext();
            if (ctx == null)
                throw new Exception("Boom - No Context");
            ds = (DataSource) ctx.lookup("java:comp/env/jdbc/eDB");
            if (ds != null) {
                con = ds.getConnection();
            }
            tables = new HashMap();
            loadMappings();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

        (other stuff)

        public void closeConnection(){
            try {
                if (stmt != null) stmt.close();
                if (con != null) con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

MySQL process list and queries:

https://i.stack.imgur.com/c1POA.jpg

Tomcat-database configuration:

Resource name="jdbc/eDB" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="***" password="****" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/ce?autoReconnect=true" />
Crashalot
  • 33,605
  • 61
  • 269
  • 439

1 Answers1

1

Can you share the code from (other stuff)

How are you obtaining connection? from a db pool? We need to look at what is the concrete connection class to be able to help.

My theory is that your concrete connection class may not be releasing the connections properly.

Puru--
  • 1,111
  • 12
  • 27
  • thanks! but aren't the calls to rs.close(), stmt.close(), and con.close() all we need to close a connection? will post more info shortly ... – Crashalot Mar 18 '14 at 02:03
  • just updated the question with more info from dbwrapper.java. thanks for your help! – Crashalot Mar 18 '14 at 02:05
  • 1
    If you have sub classed Connection class and it is not closing the connection properly, then you may have issues. But it is just my theory. What you have is correct order of closure. Also where are you creating the statements? Do you see any of the printStackTraces showing up? – Puru-- Mar 18 '14 at 02:09
  • 1
    I see you are using a DataSource (Tomcat data source I believe). When you call `conn.close()` it does not mean that your connection to db gets closed. As per the oracle documentation this is what happens "Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released". Also check your tomcat data source configuration [here](http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html). I suspect this is some kind of pool mis configuration – Puru-- Mar 18 '14 at 02:17
  • wow you are awesome. reviewing the doc now and will also post our tomcat config. thanks again! – Crashalot Mar 18 '14 at 02:27
  • 1
    Also I recommend re-factoring your code your `DBWrapper` class should be idempotent. Since the consumer of `DBWrapper` is not obtaining the connection and creating statement, consumer should not be responsible for closing connection and statement. – Puru-- Mar 18 '14 at 02:28
  • hmm reading the docs it seems like the connections are supposed to remain in the sleeping state? perhaps closing is not the problem but a failure to reuse connections? – Crashalot Mar 18 '14 at 02:39
  • Connection pools generally have initial size and grow size. If you see connections increasing while you use application it means that some of the code in your application (not the above) elsewhere is not releasing the connection properly. As I said you need to refactor your `DBWrapper`such that consumers do not need to worry about releasing the connections. Consider something like spring `JDBCTemplate` – Puru-- Mar 18 '14 at 05:14
  • OK, we'll definitely refactor, but we already checked the consumers and all of them are closing the result sets like the code above. Do you see anything wrong with the configuration? We added the abandoned attributes (i.e., removedAbandoned, logAbandoned) to catch abandoned connections, but so far nothing has been printed to the Tomcat logs. – Crashalot Mar 18 '14 at 05:19
  • I do not see anything wrong with posted config. Can you do a small test and let me know how it goes. 1) Restart your web server 2) Take a note of open connections 3) Run the code path which executes above code 4) Take note of open connections again. Keep doing #3 and #4 and see if connections grow continuously. – Puru-- Mar 18 '14 at 07:12