CODEWITHSUNDEEP
javamysql
Saturn
Saturn

Reputation: 18149

MySQL connection pool is exceeding max number of connections in Java

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.

Upvotes: 1

Views: 4971

Answers (2)

Federico Sierra
Federico Sierra

Reputation: 5208

Unless you're an application server vendor, you shouldn't be using MysqlConnectionPoolDataSource.

ConnectionPoolDataSources are not connection pools. They are vendors of connections to connection pooling code. Besides creating those physical connections a ConnectionPoolDataSource shouldn't do anything else.

In any case, the physical connection does not go away until you call PooledConnection.close(), calling conn.close() only closes the logical connection, which requires that any listeners on the ConnectionPoolDataSource be called so that it can reclaim but not close the physical connection so that it can be handed out again

So if you are working in an application server, use the pooling provided by the DataSources of the application server

If you need a standalone connection pool use third party connection pools like BoneCP, c3p0 or Apache DBCP

Upvotes: 3

user207421
user207421

Reputation: 310874

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

Upvotes: 3

Related Questions