java - com.zaxxer.hikari.pool.PoolInitializationException : Exception during pool initialization

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 信息，但我不知道这里的问题是什么。

Answer 1

对于 MySQLNonTransientConnectionException:连接过多

您有一些选择:

1. 手动关闭一些连接
   使用 SHOW PROCESSLIST 查看所有连接，然后 KILL 您想终止的进程 ID。
2. 编辑超时设置让 MySQL 守护进程自行终止非 Activity 进程
   即。在 my.cnf 中:
   wait_timeout = 28800
   interactive_timeout = 28800
3. 增加连接数
   即。在 my.cnf 的 [mysqld] 部分:
   max_connections = 100
4. 限制每个用户名的连接数
   即。在 my.cnf 的 [mysqld] 部分:
   max_user_connections = 50
5. 重启MySQL服务器

HTH