Connection of c3p0 throws exception after MySQL timeout expired

Question

I use c3p0 library of version 0.9.2.1 for creating a connection pool in a web application. After MySQL timeout I get exception:

"HTTP Status 500 - The last packet successfully received from the server was 627 301 milliseconds ago. The last packet sent successfully to the server was 627 302 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem."

I tried to append an autoReconnect parameter to JDBC url but there is no effect as well. So, in that way I use connection pool in my application:

For testing I have set wait_timeout of MySQL to 180 sec

set @@global.wait_timeout=180;

show global variables like "wait_timeout";

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  |  180  |
+---------------+-------+

and further there are next pieces of code

c3p0.properties

# JDBC paramters are ommited
# Basic pool configuration
c3p0.initialPoolSize=5
c3p0.minPoolSize=5
c3p0.maxPoolSize=50
c3p0.acquireIncrement=5
# Managing connection age
c3p0.maxConnectionAge=100
c3p0.maxIdleTime=90
# Configuring connection testing
c3p0.idleConnectionTestPeriod=30
c3p0.testConnectionOnCheckin=true
c3p0.preferredTestQuery=SELECT 1

DBConnectionUtil.java

public class DBConnectionUtil {
    // initialized through c3p0.properties
    private static ComboPooledDataSource ds = new ComboPooledDataSource();

    public static ComboPooledDataSource getConnectionPool() {
        return ds;
    }

    public static void destroyConnectionPool() {
        ds.close();
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

UserDAO.java

public class UserDAO {

    private Connection connection;

    public UserDAO() throws SQLException {
        connection = DBConnectionUtil.getConnection();
    }

    public User find(Integer id) throws SQLException {
        User user = null;
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM `USERS` WHERE ID = ?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("ID"));
            user.setName(rs.getString("NAME"));
            user.setUsername(rs.getString("USERNAME"));
            user.setPassword(rs.getString("PASSWORD"));
            user.setParentId(rs.getInt("PARENT_ID"));
        }
        rs.close();
        ps.close();
        return user;
    }
}

DAOUtil.java

public class DAOUtil {
    private static UserDAO userDAO;

    public static UserDAO getUserDAO() {
        return userDAO;
    }

    static {
        try {
            userDAO = new UserDAO();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

After expired MySQL wait_timeout, for instance, when I call DAOUtil.userDAO.find(id) it throws exception which was described above.

Could you help me to realize what I'm doing wrong, please? Note: I can't change MySQL ini file.

Answer 1

Try to close connection in your Dao class and ask for new one from connection pool for every request to database.

Connection con;
try {
  con=DBConnectionUtil.getConnection();
  //some code here
} finally {
  if(con!=null){
      con.close();
  } 

And it's not safe to have Connection as a object field, better to use it as a local variable, because connection is not thread safe.