SpringBoot By default EmbeddedDb isn't closed in the end of context

When I build tests against EmbeddedDB (hsqldb), I noticed that in between @DirtiesContext that DB isn't closed:

Caused by: org.hsqldb.HsqlException: object name already exists: FOO

Looks like in the DataSourceAutoConfiguration

@Conditional(EmbeddedDatabaseCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
@Import(EmbeddedDataSourceConfiguration.class)
protected static class EmbeddedDatabaseConfiguration {
}

doesn't work because the next one PooledDataSourceConfiguration wins somehow. And since spring-boot-starter-jdbc provides tomcat-jdbc by default, that feels like DataSourceConfiguration.Tomcat creates a DataSource bean.

Unlike EmbeddedDataSourceConfiguration that case doesn't provide this.database.shutdown(), therefore our in-memory DB stands in between @DirtiesContexts.

The simple app to demonstrate the issue: https://github.com/artembilan/EmbeddedDbDiritesContext

Comment From: wilkinsona

See #4699 which was for a similar problem with DevTools restarts

Comment From: dsyer

There are a couple of workarounds. One is to use an explicit URL for the data souce, e.g.

spring.datasource.url=jdbc:hsqldb:mem:test;shutdown=true

or for h2:

spring.datasource.url=jdbc:h2:mem:test

Another workaround is to use devtools (just put it on the classpath) because it automatically executes SHUTDOWN on the database when the context closes. Or use a migration tool (not the schema.sql initializer).

Also worth bearing in mind: use @Transactional instead of @DirtiesContext wherever you can.

Comment From: artembilan

@SpringBootTest(properties = "spring.datasource.url=jdbc:h2:mem:test",

Does the trick for our tests.

But I still have to keep @DirtiesContext, because @Transactional doesn't help. Although I don't see reason in it since I really would like to fully close DB in between test classes. That's why we use @DirtiesContext and try to rely on the database.shutdown().

Not sure that it is worth to play with Dev Tools in our case, but will keep it in mind.

Maybe some notes in the Reference Manual would be great?

Thank you!

Comment From: chrylis

I got a nasty case of "works on my machine" because of test order dependence. Spring 4.3, Boot 1.5, H2. I expected @DirtiesContext to resolve it (as it's explicitly documented for this situation), but no luck, and the recommended H2 JDBC URL does not help. (I'm explicitly printing log information from subsequent test cases, and they're seeing the dirty data.)

I am using Spock (spock-spring) with @DirtiesContext; is there a chance that's involved somehow? I see Spock examples using @DirtiesContext.

Comment From: wilkinsona

@chrylis It's hard to say without more context. If you'd like use to take a look, please provide a small sample that reproduces the behaviour you have described.

Comment From: 62mkv

Just a guess - from what I've read so far, how can one be sure that @DirtiesContext actually closes the context? To me it reads as the context is thrown out of the cache, and is guaranteed to not be re-used but that does not imply (or does it?) that it's gracefully closed before next test class is being executed?

As of the H2 in-memory database, it's documentation actually says that even without explicit ;DB_CLOSE_ON_EXIT=-1 it stays alive until at least current JVM process termination, which to me, again, means that it's bound to be in place till all the test suites are executed.

So, long story short - what are the best practices to ensure pristine in-memory DB for every individual Test class in a typical Spring Boot application? (other than overriding spring.datasource.url on every integrated test class)

Comment From: 62mkv

talking to myself here.. so, if spring-boot-devtools are on the classpath, (and they are, if you run JHipster-generated microservice in "dev" profile), there's autoconfigured bean InMemoryDatabaseShutdown, which has destroy method that issues "SHUTDOWN" command into database handle, so if you're using @DirtiesContext without overriding spring.datasource.url, then well, get ready for "fun" ... Other tests might be re-using cached context which has same datasource URL that was instantiated before @DirtiesContext test class has been executed, thus if they shared same in-memory instance, the class that executes after the "@DirtiesContext test class" is doomed.

Definitely not a bug, but still quite frustrating experience if you never gave much thought to how doSpringRunner-based tests actually work

Comment From: binakot

The same problem.

Using Spring Boot 2.1.6.RELEASE, Kotlin 1.3.41, Java 11, macos.

Running simple integration test for RestController -> Service -> CrudRepository -> H2.

Spring context doesn't close after all tests. HikariCP spams messages with active connections and keep JVM running...

@DirtiesContext helps. Thanks for advice. Waiting for some good fix.

Comment From: wilkinsona

The provided sample works with Spring Boot 2.3.x and 2.4.x. The earliest version where it works is 2.3.0.M1. I believe that's due to https://github.com/spring-projects/spring-boot/issues/16747 where each context now generated a unique name for the database. Setting spring.datasource.generate-unique-name back to false causes the problem to occur again.

With the change to generating unique database names by default, I think the current behaviour makes sense.