MySQL connection pool is exceeding max number of connections in Java

Question

I have a remote MySQL database. In Java, I have a connection pool:

pool = new MysqlConnectionPoolDataSource();
pool.setURL("jdbc:mysql://1.2.3.4:3306/TEST?max-connections=100");
pool.setUser("USER");
pool.setPassword("PASSWORD");

The max number of connections is 100. Now let's make 100 threads:

for (int i = 0; i < 100; ++i) {
    Thread t = new Thread(new Client(i, pool));
    t.start();
}

And this is the code running in each thread:

class Client implements Runnable {
    int id;
    MysqlConnectionPoolDataSource pool;
    public Client(int id, MysqlConnectionPoolDataSource pool) {
        this.id = id;
        this.pool = pool;
    }
    public void run() {
        while (true) {
            try {
                Connection conn = pool.getConnection();
                ResultSet set = conn.createStatement().executeQuery("SELECT * FROM SOMETHING");
                if (set.next()) {
                    System.out.println(id + " finished.");
                }
                set.close();
                conn.close();
                return;
            }catch (Exception e) {
                System.out.println("ERROR " + id + "  ->  " + e.getMessage());
            }
        }
    }
}

So, I have a pool with 100 max connections, and then 100 threads trying to use one connection each.

And yet, there are several errors of the form:

ERROR 30 -> User already has more than 'max_user_connections' active connections

But why, if the max number of connections is 100?

In fact, even if you change it to

pool.setURL("jdbc:mysql://1.2.3.4:3306/TEST?max-connections=300");

The problem persists.

Of course, eventually, the connection is made and I can perform the query. But I don't understand why am I supposedly exceeding the number of connections.

NB You are leaking connections in the error case, and statements in all cases. — user207421

user207421 user207421 · Accepted Answer · 2014-11-25T23:37:45

max_user_connections is a server setting in my.ini: see here. It has nothing to do with the connection pool, Java, etc.

MySQL connection pool is exceeding max number of connections in Java

2 Answers