Cycling between databases results in leaked connections that do not close resulting in 'too many connections'

Question

I'm currently working on something that switches between mysql databases in order to check for certain changes. However, when switching databases and the host using the switchSource method below, it results in a successful switch but the connections are left open and do not seem to close or obey the setMaxIdleTime setting.

So what happens is that it that every time it connects to a database host and a database it creates more connections which just keep accumulating each time it reconnects until the database host stops accepting connections and returns the 'too many connections' error.

I'm wondering how best to close these connections.

When queries are ran, they are in a try catch (e.g. try (Connection conn = DataSource.getInstance().getConnection())) and statements are also closed before the catch. So that combined with the setMaxIdleTime and I'm not sure why the connections are not being closed.

If anyone could shed some light on this one, I'd really appreciate it. Thanks for reading.

package com.example.database;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import com.example.base.ShardNode;
import com.example.manage.ShardManager;
import com.mchange.v2.c3p0.ComboPooledDataSource;  

import static com.example.manage.ShardManager.shardNodeList;


// see https://www.javatips.net/blog/c3p0-connection-pooling-example
public class DataSource {

    private static DataSource datasource;
    private ComboPooledDataSource cpds;
    private static int currentShardId = -1;
    private static ShardNode currentShardNode;
    private static boolean dbIsNotSpecified;
    private static boolean clearConnections = false;

    private static String GetDatabaseUrlAndDB(ShardNode selectedShard) {
        System.out.println(selectedShard.getFullUrl() + ShardManager.getDatabaseName(currentShardId));

        if (dbIsNotSpecified) {
          return selectedShard.getFullUrl();
        }

        return selectedShard.getFullUrl() + ShardManager.getDatabaseName(currentShardId);
    }

    private DataSource() throws PropertyVetoException {

        this.cpds = new ComboPooledDataSource();
        this.cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
        this.cpds.setJdbcUrl(GetDatabaseUrlAndDB(currentShardNode));
        this.cpds.setUser(currentShardNode.getUsername());
        this.cpds.setPassword(currentShardNode.getPassword());

        // the settings below are optional -- c3p0 can work with defaults
//        cpds.setInitialPoolSize(5);
//        cpds.setMinPoolSize(5);
//        cpds.setAcquireIncrement(5);
//        cpds.setMaxPoolSize(100);
//        cpds.setMaxStatements(100);

        /*
         * Set this low to prevent connections from hanging around after the worker has left
         * Otherwise it results in too many connections being made on a single node and the server
         * starts rejecting new connections.
         */
//        cpds.setMaxIdleTime(1);
    }

    /* Refreshes the datasource to use a new id */
    public static void switchSource(int shardId, boolean dbNotSpecified) throws PropertyVetoException {
        // TODO continue work here. Pass id to data source and pull through credentials
        currentShardId = shardId;
        dbIsNotSpecified = dbNotSpecified;

        for(ShardNode CurrentShard: shardNodeList) {
            if ((shardId >= CurrentShard.getStartingShard())
                    && (shardId <= CurrentShard.getEndingShard())) {
                currentShardNode = CurrentShard;
                datasource = new DataSource();

                break;
            }
        }

        if (datasource == null) {
            // Handle empty datasources
        }
    }

    public static DataSource getInstance() throws PropertyVetoException {
        /*
         * If the datasource is null the runner is likely to have
         * just been started so use the first shardNode in the list
         */
        if (datasource == null) {
            currentShardNode = shardNodeList.get(0);
            datasource = new DataSource();
        }

        return datasource;
    }

    public Connection getConnection() throws SQLException {
        return this.cpds.getConnection();
    }
}

Answer 1

In a long-lived program like yours, you must .close() any Connection object you obtain from .getConnection() when you finish using it. If you don't, you'll get the connection leak you describe in your question.

It looks like your DataSource supports pooled connections. That removes the performance hit from repeated cycles of .getConnection() / .close().