Login/Register
Dealing with "Database is already closed" with H2 embedded database and spring-jdbc
Asked Answered
h2spring-jdbc
C

1

6

Event though the built-in H2 EmbeddedDatabaseConfigurer provides a JDBC connection with DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false options set, the annoying exception is caused by AbstractEmbeddedDatabaseConfigurer.shutdown().

There are already plenty of discussions around this topic on stackoverflow. I have tried to play a bit with the options and have come to the conclusion that the rather odd settings (DB_CLOSE_DELAY=20;DB_CLOSE_ON_EXIT=false) provided with a custom DataSourceFactory reduce the number of occurrences but do not completely eliminate it.

/**
 * This factory tries to solve the problem that Spring tries to close a data source that has already been closed by the H2 engine/H2 driver.
 */
public class H2DataSourceFactory implements DataSourceFactory {
    private final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();

    @Override
    public ConnectionProperties getConnectionProperties() {
        return new ConnectionProperties() {
            @Override
            public void setDriverClass(Class<? extends Driver> driverClass) {
                dataSource.setDriverClass(driverClass);
            }

            @Override
            public void setUrl(String url) {
                // ignore the URL and use a custom URL
                dataSource.setUrl("jdbc:h2:mem:%s;DB_CLOSE_DELAY=20;DB_CLOSE_ON_EXIT=false;MODE=PostgreSQL");
            }

            @Override
            public void setUsername(String username) {
                dataSource.setUsername(username);
            }

            @Override
            public void setPassword(String password) {
                dataSource.setPassword(password);
            }
        };
    }

    @Override
    public DataSource getDataSource() {
        return this.dataSource;
    }
}

Has someone else come to a solution that really gets rid of this effect?

Here is the stacktrace:

14:12:32.691 [localhost-startStop-2] WARN  o.s.j.d.e.H2EmbeddedDatabaseConfigurer - Could not shutdown embedded database
org.h2.jdbc.JdbcSQLException: Database is already closed (to disable automatic closing at VM shutdown, add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL) [90121-160]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:146) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.message.DbException.get(DbException.java:135) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1394) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1369) ~[h2-1.3.160.jar:1.3.160]
    at org.h2.jdbc.JdbcConnection.createStatement(JdbcConnection.java:191) ~[h2-1.3.160.jar:1.3.160]
    at org.springframework.jdbc.datasource.embedded.AbstractEmbeddedDatabaseConfigurer.shutdown(AbstractEmbeddedDatabaseConfigurer.java:42) ~[spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.shutdownDatabase(EmbeddedDatabaseFactory.java:177) [spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory$EmbeddedDataSourceProxy.shutdown(EmbeddedDatabaseFactory.java:250) [spring-jdbc-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_25]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_25]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_25]
    at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_25]
    at org.springframework.beans.factory.support.DisposableBeanAdapter.invokeCustomDestroyMethod(DisposableBeanAdapter.java:350) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DisposableBeanAdapter.destroy(DisposableBeanAdapter.java:273) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroyBean(DefaultSingletonBeanRegistry.java:578) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingleton(DefaultSingletonBeanRegistry.java:554) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.destroySingleton(DefaultListableBeanFactory.java:900) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingletons(DefaultSingletonBeanRegistry.java:523) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.destroySingletons(DefaultListableBeanFactory.java:907) [spring-beans-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.destroyBeans(AbstractApplicationContext.java:908) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.doClose(AbstractApplicationContext.java:884) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.close(AbstractApplicationContext.java:836) [spring-context-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.web.context.ContextLoader.closeWebApplicationContext(ContextLoader.java:579) [spring-web-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.springframework.web.context.ContextLoaderListener.contextDestroyed(ContextLoaderListener.java:115) [spring-web-4.1.5.RELEASE.jar:4.1.5.RELEASE]
    at org.apache.catalina.core.StandardContext.listenerStop(StandardContext.java:4773) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5385) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:232) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1425) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at org.apache.catalina.core.ContainerBase$StopChild.call(ContainerBase.java:1414) [tomcat-embed-core-8.0.20.jar:8.0.20]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_25]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_25]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_25]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_25]
Cynth answered 17/3, 2015 at 10:24 Comment(5)
Do you call AbstractEmbeddedDatabaseConfigurer.shutdown() too early (before you are done with this database)? Because that method seems to execute "shutdown", which forcibly closes the database, no matter what options are used. – Ito
shutdown()is called by the Spring bean life-cycle management during application/application-context (integration test) shutdown. As I understand the options DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false - they should make sure that all connection management should be in the hands of Spring, not the H2 driver. But obviously something is causing that the connection still is closed. My application code definitely does not perform any connection closing. It's all left to Spring which usually does a good job with that. – Cynth
If someone (it doesn't matter if it's Spring or you) executes shutdown, then the database is closed. That's the expected behavior, and not a bug. So if you don't want that behavior, you need to ensure this is not executed, or you need to use a different database for each tests (parameterize the JDBC URL). – Ito
I'm not saying that there is a bug - don't get me wrong - just trying to get feedback. Adding the stacktrace actually makes me think of another potential culprit. I remember that Tomcat has introduced some resource cleanup mechanisms to avoid leaks. However, I'm not using a container managed data source here. It's all managed by Spring. – Cynth
What I would do is append ";trace_level_system_out=3" to the database URL, to find out what SQL statements are executed and what JDBC methods are called. But it requires quite a bit of low-level knowledge... Also, I would probably try using a persistent database (persist to file), and possibly append ";trace_level_file=3" – Ito
I
0

For anyone still facing this:

Using the async protocol seems to allow a connection to be interrupted without breaking all other connections. Trying this option to see if this reduces these errors.

You can set this as:

spring.datasource.url=jdbc:h2:async:mem:testdb

Documentation: http://www.h2database.com/html/advanced.html

Isom answered 13/4, 2023 at 20:57 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.