com.zaxxer.hikari.pool.PoolInitializationException：池初始化期间异常

Question

我目前有一个连接到我的主数据库的开放池，它运行良好。但是现在，我想为另一个数据库打开一个新池。我完全按照设置第一个池的方式设置了新池，显然我编辑了数据库名称等。加载 setupHikari() 方法时没有出现错误，但是一旦实际语句即将运行，我就会遇到很多错误。

 Caused by: com.zaxxer.hikari.pool.PoolInitializationException: Exception during pool initialization at com.zaxxer.hikari.pool.HikariPool.initializeConnections(HikariPool.java:580) ~[?:?] at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:150) ~[?:?] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:96) ~[?:?] at com.thecodexmc.GameLibrary.xenforo.XenUtils.getUserGroupID(XenUtils.java:51) ~[?:?] at com.thecodexmc.GameLibrary.game.GameListeners.onLogin(GameListeners.java:334) ~[?:?] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_79] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_79] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_79] at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_79] at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] ... 13 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_79] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_79] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_79] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_79] at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.Util.getInstance(Util.java:382) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:931) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4031) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1296) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2338) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2371) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2163) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_79] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_79] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_79] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_79] at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:378) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:440) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:138) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:108) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] at com.zaxxer.hikari.pool.HikariPool.addConnection(HikariPool.java:495) ~[?:?] at com.zaxxer.hikari.pool.HikariPool.initializeConnections(HikariPool.java:564) ~[?:?] at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:150) ~[?:?] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:96) ~[?:?] at com.thecodexmc.GameLibrary.xenforo.XenUtils.getUserGroupID(XenUtils.java:51) ~[?:?] at com.thecodexmc.GameLibrary.game.GameListeners.onLogin(GameListeners.java:334) ~[?:?] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_79] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_79] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_79] at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_79] at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[server.jar:git-PaperSpigot-7aa4900-62a2169] ... 13 more

我在这里做错了什么？这是我的 XenSQL 类，这是导致问题的原因。

 public class XenSQL {

private static XenSQL instance;
private HikariDataSource hikari;

public XenSQL() {
    instance = this;
}

public void setupHikari() {
    hikari = new HikariDataSource();
    hikari.setMaximumPoolSize(10);
    hikari.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    hikari.addDataSourceProperty("serverName", host);
    hikari.addDataSourceProperty("port", port);
    hikari.addDataSourceProperty("databaseName", database);
    hikari.addDataSourceProperty("user", user);
    hikari.addDataSourceProperty("password", password);
    System.out.println("[XenSQL] HikariCP connected to the database successfully.");
}

public void queryUpdate(String query) {
    Connection connection = null;

    try {
        connection = hikari.getConnection();
        PreparedStatement pre = connection.prepareStatement(query);
        pre.executeUpdate();
        pre.close();
    } catch (SQLException e) {
        System.out.println("[XenSQL] Error whilst executing the query \"" + query + "\": " + e.getMessage());
    } finally {
        closeConnection(connection);
    }
}

public void closeConnection(Connection connection) {
    if(connection != null) {
        try {
            connection.close();
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
}

public HikariDataSource getHikari() {
    return hikari;
}

public static XenSQL getInstance() {
    return instance;
}
}

出于显而易见的原因，我删除了 SQL 信息，但我不知道这里有什么问题。

原文由 Accessory 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

对于 MySQLNonTransientConnectionException：连接过多

你有一些选择是：

1. 手动终止一些连接

使用 SHOW PROCESSLIST 查看所有连接，然后 KILL 您要终止的进程 ID。 2. 编辑超时设置让 MySQL 守护进程自行终止非活动进程

IE。在 my.cnf 中：

wait_timeout = 28800

interactive_timeout = 28800

1. 增加连接数

IE。在 my.cnf 的 [mysqld] 部分：

最大连接数 = 100 4. 限制每个用户名的连接数量

IE。在 my.cnf 的 [mysqld] 部分：

max_user_connections = 50

1. 重启MySQL服务器

HTH

原文由 Nitin 发布，翻译遵循 CC BY-SA 3.0 许可协议