why lost Mysql connection pool after a period?

Question

My application using Mysql and Postgres at the same time, the web server is tomcat 7.0. After the application runs a period, like 15, 16 hours, it lost the mysql connection pool, and i get "Cannot get a connection, pool error Timeout waiting for idle object" error message in the log. So i need to start tomcat to restart the application to reconnect to Mysql connection pool again. I close each connection after it finishes query. This never happen on Postgres, just on Mysql, so... weird.

I use Singleton to connect to connection pool for Mysql and Postgres both. Below is the code for Mysql.

public class DB {
    private static DB database = new DB();
    private DataSource ds;

    private DB() {
        try {
            InitialContext ic = new InitialContext();
            ds = (DataSource)ic.lookup("java:comp/env/jdbc/mobile_recharge");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static DB getInstance() {
        return database;
    }

    public DataSource getDS() throws SQLException {
        return ds;
    }
}

one of the queries:

public boolean preSaveData(HashMap<String, String> mapXML, String date, String serialNum) {
    try {
        con = open();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        log.writeLog(mapXML.get("terminalId"), mapXML.get("date_time"), "fails to insert order info into mysql: " + e.getMessage());
        return false;
    }

    String table = new JudgeTidField().table(mapXML.get("tidField"));

    String query = "insert into " + table + " (Terminal_id, Yewu, Phone_number, Company, " + 
                   "Order_money, Balance, Actual_money, Order_record_date, Recharge_record_date, Province, Serial_number) values " +
                   "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    try {
        presta = (PreparedStatement) con.prepareStatement(query);
        presta.setString(1, mapXML.get("terminalId"));
        presta.setString(2, "recharge");
        presta.setString(3, mapXML.get("phoneNum"));
        presta.setString(4, mapXML.get("type"));
        presta.setFloat(5, Float.parseFloat(mapXML.get("price")));
        presta.setFloat(6, (float) 0.000);
        presta.setFloat(7, Float.parseFloat(mapXML.get("actualMoney")));
        presta.setString(8, date);
        presta.setString(9, date);
        presta.setString(10, mapXML.get("province"));
        presta.setString(11, serialNum);

        presta.executeUpdate();
        log.writeLog(mapXML.get("terminalId"), mapXML.get("date_time"), "success to insert order info into mysql");
        return true;
    } catch (SQLException e) {
        e.getMessage();
        log.writeLog(mapXML.get("terminalId"), mapXML.get("date_time"), "fail to insert order info into mysql: " + e.getMessage());
        return false;
    } finally {
        try {
            presta.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

In the context.xml:

<Context  docBase="Mobile" path="/Mobile" reloadable="true">
  <Resource
    name="jdbc/mobile_recharge"
    auth="Container"
    type="javax.sql.DataSource"
    maxActive="500"
    maxIdle="5"
    maxWait="5000"
    driverClassName="com.mysql.jdbc.Driver"
    username="root"
    password="xxxxxxxx"
    url="jdbc:mysql://xxx.xxx.x.xxx:3306/mobile_recharge?characterEncoding=utf8" />

  <Resource
    name="jdbc/pgsql"
    type="javax.sql.DataSource"
    maxActive="500"
    maxIdle="2"
    username="postgres"
    maxWait="5000"
    driverClassName="org.postgresql.Driver"
    password="xxxxxxxx"
    url="jdbc:postgresql://xxx.xxx.x.xxx:5555/runningacounts"/>
</Context>

In the web.xml:

<resource-ref> 
  <description>MysqlDB Connection</description> 
  <res-ref-name>jdbc/mobile_recharge</res-ref-name> 
  <res-type>javax.sql.DataSource</res-type> 
  <res-auth>Container</res-auth> 
</resource-ref>
<resource-ref> 
  <description>PGDB Connection</description> 
  <res-ref-name>jdbc/pgsql</res-ref-name> 
  <res-type>javax.sql.DataSource</res-type> 
  <res-auth>Container</res-auth> 
</resource-ref>

Answer 1

For MySQL at least, I would recommend you research the connection pool validation and cleaning options, specifically validationQuery, testWhileIdle, testOnBorrow, timeBetweenEvictionRunsMillis, removeAbandoned, removeAbandonedTimeout etc - more details here: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes

I have had similar problems with MySQL connections via JDBC in Tomcat, and sensible application of these properties resolved it so there was no need to bounce Tomcat after long idle periods. Hope that helps.