H2: The object is already closed [90007-197]

[fletchgqc]

I'm trying to use the library with H2 and I'm finding that the connections get closed after a while, resulting in stacktraces like this:

org.h2.jdbc.JdbcSQLException: The object is already closed [90007-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1523)
at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1502)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:692)
at org.davidmoten.rx.jdbc.pool.internal.PooledConnection.prepareStatement(PooledConnection.java:57)

I have a couple of Repositories with code like this:

@Component
public class CommandRepository {

  @Autowired private ApplicationProperties applicationProperties;

  private Database db;

  @PostConstruct
  public void connect() throws Exception {
    Connection connection =
        DriverManager.getConnection(
            applicationProperties.getDatasource().getUrl(),
            applicationProperties.getDatasource().getUsername(),
            applicationProperties.getDatasource().getPassword());
    NonBlockingConnectionPool pool =
        Pools.nonBlocking()
            .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
            .connectionProvider(ConnectionProvider.from(connection))
            .healthCheck(DatabaseType.H2)
            .build();

    this.db = Database.from(pool);

    createTables();
  }

  private void createTables() {
    String commandPrefix = "CREATE TABLE COMMAND(";

    String createTablePostfix =
        "ID VARCHAR(50) NOT NULL, " + "JSON VARCHAR(5000) NOT NULL, " + "PRIMARY KEY (ID) " + ")";

    db.update(commandPrefix + createTablePostfix).counts().blockingForEach(System.out::println);
  }

  public Flux<Message> findAll() {
    return Flux.from(
        db.select("select * from command")
            .get(
                rs -> {
                  Message message = new Message();
                  message.setId(rs.getString("id"));
                  message.setJson(rs.getString("json"));

                  return message;
                }));
  }

  public Mono<Integer> save(Mono<Message> commandMono) {
    String createSql = "INSERT INTO command (id, json) VALUES (?, ?)";

    return commandMono.flatMap(
        c -> {
          return Mono.from(db.update(createSql).parameters(c.getId(), c.getJson()).counts());
        });
  }

  @PreDestroy
  public void shutdown() {
    db.update("drop table command").complete().blockingAwait();
  }
}

Am I doing something wrong?

[davidmoten]

Hi, yep there is a problem:

Connection connection =
        DriverManager.getConnection(
            applicationProperties.getDatasource().getUrl(),
            applicationProperties.getDatasource().getUsername(),
            applicationProperties.getDatasource().getPassword());
    NonBlockingConnectionPool pool =
        Pools.nonBlocking()
            .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
            .connectionProvider(ConnectionProvider.from(connection))
            .healthCheck(DatabaseType.H2)
            .build();

The pool needs to be able to create new connections whenever needed but you provided a singleton connection! It gets closed by the pool and when the pool wants a new one it gets that closed one again.

If applicationProperties.getDatasource() is a javax.sql.DataSource then you can just use:

 NonBlockingConnectionPool pool =
        Pools.nonBlocking()
            .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
            .connectionProvider(applicationProperties.getDatasource())
            .healthCheck(DatabaseType.H2)
            .build();

Otherwise make a custom ConnectionProvider that dynamically creates connections.

As this has been reported before I might add these things:

• pool should detect it has been passed ConnectionProvider.from(connection) and complain
• add ConnectionProvider.from(url), ConnectionProvider.from(url, username, password) static factory methods

[davidmoten]

I'm adding those changes but I have noticed you can do this with current version:

NonBlockingConnectionPool pool =
        Pools.nonBlocking()
            .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
            .url(url)
            .property("user", user)
            .property("password", password)
            .healthCheck(DatabaseType.H2)
            .build();

I'll add user(String user) and password(String password) methods to the builder as well.

[davidmoten]

rxjava2-jdbc 0.1-RC42 has been released to Maven Central and you can now do this:

NonBlockingConnectionPool pool =
    Pools.nonBlocking()
        .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
        .url(applicationProperties.getDatasource().getUrl())
        .user(applicationProperties.getDatasource().getUsername())
        .password(applicationProperties.getDatasource().getPassword())
        .healthCheck(DatabaseType.H2)
        .build();

[fletchgqc]

Thanks a lot. Perhaps the reason that this issue has come up repeatedly is that the first Google result for "reactive jdbc" is https://medium.com/netifi/spring-webflux-and-rxjava2-jdbc-83a94e71ba04 - that's where I got the connection code from. I have now added a comment on the article referencing this issue.

[davidmoten]

Thanks for the report, the library will be a bit stronger because of it.

[fletchgqc]

Unfortunately, the fix doesn't work. Code is still as above, with your fix appliend. After a certain period of inactivity, I now get

org.h2.jdbc.JdbcSQLException: Table "COMMAND" not found; SQL statement:
select * from command [42102-197]

I tried both

NonBlockingConnectionPool pool =
    Pools.nonBlocking()
        .maxPoolSize(Runtime.getRuntime().availableProcessors() * 5)
        .url(applicationProperties.getDatasource().getUrl())
        .user(applicationProperties.getDatasource().getUsername())
        .password(applicationProperties.getDatasource().getPassword())
        .healthCheck(DatabaseType.H2)
        .build();

and

ConnectionProvider.from(url, username, password)

with the same result.

[davidmoten]

This might be a problem peculiar to the in-memory database in that once all connections to that db are closed the database is effectively thrown away. Perhaps set the logging level to DEBUG to establish what the pool is doing?

[davidmoten]

I've just had a look at the source of NonBlockingConnectionPool and the default for maxIdleTime is 30 minutes. Set that to something large like maxIdleTime(100000, TimeUnit.DAYS). I'll look at a no-idle-release option as well in the builder.

[davidmoten]

You can pass 0 to maxIdleTime to never release. I'll add javadoc to the builder so it's obvious.

[davidmoten]

Just out of interest I noticed this in the H2 docs:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

[davidmoten]

Once you've got this working I'll add a section to the README about using H2 in-memory. Thanks for persisting with it (so to speak!).

[fletchgqc]

heh heh heh yes I'm rather persistent.

I had tried the DB_CLOSE_DELAY=-1 previously but it didn't help. Now I added it again in combination with your fix and everything works fine. Thanks!

[davidmoten]

Beaut!