Jose Jose - 3 months ago 15
Java Question

Java - MySQL doesn't close connections

I'm getting a "too many connections" error while trying to run a program that asks for connections from a connection pool implemented with tomcat library, it's failing after around 50 connections, here's the error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"


here's the main class, it has a loop that executes connections

public static void main(String[] args) throws Exception{
SimplePoolExample ex = new SimplePoolExample();

int cont = 100;

while (cont > 0)
{
ex.test();
System.out.println(cont);
cont--;
}

}


here's the test() function that executes a simple query

public void test() throws Exception
{

Connection con = getConnection();
try (Statement st = con.createStatement()) {
ResultSet rs = st.executeQuery("select * from actor");

while (rs.next()) {
System.out.println(rs.getString("actor_id") +" "+ rs.getString("first_name")+" "+ rs.getString("last_name"));
}

rs.close();
st.close();

} finally {
if (con!=null) try {
con.close();
}

catch (SQLException ignore) {

System.out.println("***SQL EXC" + ignore.getMessage());
}
}
}


and the getConnection() class that asks for a connection from a connection pool

public Connection getConnection () throws SQLException
{
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);

Connection con = null;
con = datasource.getConnection();

return con;
}


EDIT: here are the connection pool settings:

public void setPoolProperties ()
{

p.setUrl("jdbc:mysql://localhost:3306/sakila");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("user");
p.setPassword("pwd");
p.setJmxEnabled(true); // utilities to manage JVM
p.setTestWhileIdle(false); // test idle connections
p.setTestOnBorrow(true); //
p.setValidationQuery("SELECT 1"); // any test requires it
p.setTestOnReturn(false);
p.setValidationInterval(30000); // cada cuanto hace test
p.setTimeBetweenEvictionRunsMillis(30000); // how often check idle and abandoned conn
p.setMaxActive(50);
p.setInitialSize(10);
p.setMaxWait(50);
p.setRemoveAbandonedTimeout(60); // OJO: max query last
p.setMinEvictableIdleTimeMillis(30000); // time to consider a conn idle
p.setMaxIdle(10);
p.setMinIdle(10);
p.setLogAbandoned(true); // log stack traces .. overhead
p.setRemoveAbandoned(true); //abandoned timeout ...
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
}

Answer

You are creating a completely new connection pool every time getConnection() is called.

You should use a single shared instance of DataSource instead.