HikariCP Pool is closed

Plugins Plugin Development
1

Hello.

Someone reported this exception to me, and I’m quite unable to figure it out.
It says that the data source my MySQL was using has been closed and I don’t know what could do such a thing.

...
Caused by:
java.sql.SQLException: HikariDataSource HikariDataSource (HikariPool-2) has been closed.
   com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:81)
   com.djrapitops.plan.system.database.databases.sql.MySQLDB.getConnection(MySQLDB.java:62)
   com.djrapitops.plan.system.database.databases.sql.tables.Table.getConnection(Table.java:60)
   com.djrapitops.plan.system.database.databases.sql.tables.Table.query(Table.java:203)
   com.djrapitops.plan.system.database.databases.sql.tables.ServerTable.getServerID(ServerTable.java:120)
   com.djrapitops.plan.system.database.databases.sql.operation.SQLCheckOps.isServerInDatabase(SQLCheckOps.java:52)
   com.djrapitops.plan.system.info.connection.ConnectionIn.checkAuthentication(ConnectionIn.java:40)
   com.djrapitops.plan.system.info.connection.ConnectionIn.(ConnectionIn.java:33)
   com.djrapitops.plan.system.info.connection.InfoRequestPageHandler.getResponse(InfoRequestPageHandler.java:47)
   com.djrapitops.plan.system.webserver.ResponseHandler.getResponse(ResponseHandler.java:114)
   com.djrapitops.plan.system.webserver.ResponseHandler.getResponse(ResponseHandler.java:62)
   com.djrapitops.plan.system.webserver.RequestHandler.handle(RequestHandler.java:45)
   com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:79)
   sun.net.httpserver.AuthFilter.doFilter(AuthFilter.java:83)
   com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:82)
   sun.net.httpserver.ServerImpl$Exchange$LinkHandler.handle(ServerImpl.java:675)
   com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:79)
   sun.net.httpserver.ServerImpl$Exchange.run(ServerImpl.java:647)
   java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
   java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
   java.lang.Thread.run(Thread.java:748)

In my plugin the generic MySQL is closed in the following way:

@Override
public void close() {
    try {
        if (dataSource != null && dataSource instanceof BasicDataSource) {
            ((BasicDataSource) dataSource).close();
        }
    } catch (SQLException e) {
        Log.toLog(this.getClass(), e);
    }
    super.close();
}

AFAIK HikariDataSource isn’t a apache.dhcp2.BasicDataSource so it should not close the DataSource when shutting down the plugin.

What could be causing the closing?

Additional information:

the DataSource is initialized like this when database is opened

@Override
public void setupDataSource() throws DBInitException {
    Optional<SqlService> sqlServiceProvider = Sponge.getServiceManager().provide(SqlService.class);
    if (!sqlServiceProvider.isPresent()) {
        return;
    }

    String host = Settings.DB_HOST.toString();
    String port = Integer.toString(Settings.DB_PORT.getNumber());
    String database = Settings.DB_DATABASE.toString();
    String launchOptions = Settings.DB_LAUNCH_OPTIONS.toString();
    if (launchOptions.isEmpty() || !launchOptions.startsWith("?") || launchOptions.endsWith("&")) {
        Log.error("Launch Options were faulty, using default (?rewriteBatchedStatements=true&useSSL=false)");
        launchOptions = "?rewriteBatchedStatements=true&useSSL=false";
    }

    String url = host + ":" + port + "/" + database + launchOptions;
    String username = Settings.DB_USER.toString();
    String password = Settings.DB_PASS.toString();
    try {
        this.dataSource = sqlServiceProvider.get().getDataSource(
                "jdbc:mysql://" + username + ":" + password + "@" + url
        );
    } catch (SQLException e) {
        throw new DBInitException(e);
    }
}
2

It seems that somehow, you are closing the MySQL connection, and then trying to use the old closed connection without reopening it again. I would recommend attaching a debugger to your process, and setting a breakpoint at every close() call, and seeing where the last close call is before it spits out the error. If you would like help with this, PM me.

Alternatively, if your code is open source, post a link, and I can try to help find where its freaking out.

You should also probably in your getConnection methods, check if the connection is still open, and throw a more useful error in the case that is not (or maybe re-open the connection or something.

3

I have not been able to reproduce the problem on my machine, so I can’t really add any debug logs

The user states that it only occurs on one of 8 Sponge servers they have.

Here is the source code https://github.com/Rsl1122/Plan-PlayerAnalytics/tree/master/Plan/src/main/java/com/djrapitops/plan/system/database/databases/sql

Here are relevant bits for what is done with the connection in terms of closing it

In SQLDB.java

public void commit(Connection connection) throws SQLException {
    try {
        if (!usingMySQL) {
            connection.commit();
        }
    } catch (SQLException e) {
        if (!e.getMessage().contains("cannot commit")) {
            Log.toLog(this.getClass(), e);
        }
    } finally {
        returnToPool(connection);
    }
}

public void returnToPool(Connection connection) throws SQLException {
    if (usingMySQL && connection != null) {
        connection.close();
    }
}

Getting a connection in MySQLDB.java

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

Some database transaction in Table.java

protected boolean execute(ExecStatement statement) throws SQLException {
    Connection connection = null;
    try {
        connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(statement.getSql())) {
            return statement.execute(preparedStatement);
        }
    } finally {
        commit(connection);
        db.returnToPool(connection);
    }
}

So, the connection is closed in a finally block after an operation is complete - On DHCP2 this returns the connection to the pool, and since both that and HikariDataSource implement the same DataSource interface I assumed that is how it works, too.

I don’t think I should be the one in charge of opening and closing connections since that is the job a connection pool is designed for.

Planned solution
I’m going to add a check for the datasource being closed in the getConnection method and get a new datasource if it is closed.

4

Well it looks to me like your trying to close the connection twice… Not sure if that’s allowed or not.

finally {
    commit(connection);
    db.returnToPool(connection);
}

Commit is already calling return to pool, and therefore close the connection, and then you seem to be calling it again… Or maybe I’m missing something :stuck_out_tongue:

5

It’s probably like those typos put in just for when you ask someone to review your paper… :stuck_out_tongue:

Since it’s the same Connection instance I doubt it causes anything horrible - will be sorted though.

6

Mysql servers close connections when they arent used for a while. Maybe if the plugin isn’t used (on a less active server) the connection times out, and upon trying to use it again it fails?
But generally data pools should take care of that…