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

huangapple go评论78阅读模式
英文:

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

问题

我目前正在处理一个切换MySQL数据库的问题,以检查特定的更改。然而,当切换数据库和主机时,使用下面的switchSource方法,虽然切换成功,但连接保持打开状态,似乎不会关闭或遵守setMaxIdleTime设置。

因此,问题在于每次连接到数据库主机和数据库时,它都会创建更多连接,每次重新连接时这些连接就会累积,直到数据库主机停止接受连接并返回"连接过多"的错误。

我想知道如何最好地关闭这些连接。

当运行查询时,它们位于try-catch块中(例如try (Connection conn = DataSource.getInstance().getConnection())),语句也会在catch之前关闭。因此,结合setMaxIdleTime,我不确定为什么连接没有被关闭。

如果有人能够为这个问题提供一些解答,我会非常感谢。感谢阅读。

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;

public class DataSource {

    // ... 其他代码 ...

    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());

        // 下面的设置是可选的 -- c3p0 可以使用默认值
        // cpds.setInitialPoolSize(5);
        // cpds.setMinPoolSize(5);
        // cpds.setAcquireIncrement(5);
        // cpds.setMaxPoolSize(100);
        // cpds.setMaxStatements(100);

        /*
         * 将此设置得较低以防止连接在工作器离开后继续存在
         * 否则会导致在单个节点上创建过多连接,服务器会开始拒绝新连接。
         */
        // cpds.setMaxIdleTime(1);
    }

    /* 刷新数据源以使用新的ID */
    public static void switchSource(int shardId, boolean dbNotSpecified) throws PropertyVetoException {
        // ... 其他代码 ...
    }

    public static DataSource getInstance() throws PropertyVetoException {
        // ... 其他代码 ...
    }

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

请注意,上述代码是您提供的Java代码的翻译版本,只包括注释和方法名的翻译,不包括完整的代码逻辑。

英文:

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();
}
}

答案1

得分: 1

在类似您这样的长期运行的程序中,当您完成使用连接对象后,必须调用 .close() 来关闭从 .getConnection() 获取的任何 Connection 对象。如果不这样做,您将会遇到您在问题中描述的连接泄漏情况。

看起来您的数据源支持连接池连接。这将消除了重复的 .getConnection() / .close() 循环带来的性能损耗。

英文:

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().

huangapple
  • 本文由 发表于 2020年9月1日 07:01:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/63679259.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定