IntegrationTest isolation fails in springboot 2.2.2.RELEASE (Error dopping tables after each SpringBootTest)

Question

Our app is working in 2.0.4 release. After upgrade to 2.2.2.RELEASE we see integration tests failing. I suspect that there is some misconfiguration, and each integration test simply does not clean after itself or there is extra initialization which weren't here before. I really do not know how to fix it properly.

To be specific. Each test works when invoked separately. When executed all of them we do see errors like:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "drop table somewhere.sometable if exists" via JDBC Statement
...
caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Cannot drop "SOME_TABLE" because "FKKJEJC7GUX6OTX5NGANQCMN83R, FK7WLRCFA21PY7CI3R4OL1OWODT, FKQPMY4YOVD3D6HBNT0XX92149P, FK1TG6AMM2NSM6UJTO9EJHPJIXY, FKLPTBKDKFCHE72RJ5RRRIH4ORJ" depends on it; SQL statement:

and

2019-12-16 21:11:00.075 org.apache.tomcat.util.modeler.Registry  : The MBean registry cannot be disabled because it has already been initialised

which suggests me, that we're trying to re-initialize something already initialized + there is wrong order of drops in hibernate initialization. But I really cannot see anything wrong on our side. Lets show some excerpts:

annotations of test:

@RunWith(SpringRunner.class)
@ActiveProfiles(...)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class SomeIT {

tests are executed via:

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-failsafe-plugin</artifactId>
  <version>2.22.2</version>
  <configuration>
    <useSystemClassLoader>false</useSystemClassLoader>
    <forkCount>0</forkCount>
    <reuseForks>false</reuseForks>
  </configuration>
  <executions>
    <execution>
      <id>integration-test</id>
      <goals>
        <goal>integration-test</goal>
      </goals>
    </execution>
    <execution>
      <id>verify</id>
      <goals>
        <goal>verify</goal>
      </goals>
    </execution>
  </executions>
</plugin>

and application.properties for tests:

spring.jpa.database=H2
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.jdbc.batch_size=5
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

#this disables option to have opened tx in view IIUC. We don't rely on that, so this just removes warning logging from console.
spring.jpa.open-in-view=false

#used to select db initialization scripts.
spring.datasource.platform=org.hibernate.dialect.H2Dialect

spring.datasource.url=jdbc:h2:mem:somewhere;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;INIT=create schema if not exists somewhere
spring.datasource.driver-class-name = org.h2.Driver

#this is probably needed for @DataJpaTest: I have no idea how to configure @DataJpaTest so that it can run with
#autoconfigured H2 db, probably it's caused by having schema defined in entities. Anyways @DataJpaTest fails to
#create schema. So alternative is to configure one DB for whole app here, and this option forces all @DataJpaTest not to
#replace this configuration with autoconfigured db.
spring.test.database.replace=none

Tested changes:

• I changed create to create-drop if it helps in any way and no, it does not help in any way.
• I tried to @DirtiesContext on class level for every IT test, which is what I'd expect anyways, that context is created/killed with every IT test class, but that did not help either.
• I tried to remove replace=none, but that just kills all unit tests(because schema is not created), and does not help in any way to integration tests.

Current workaround: well all I was able to come up with is not to reuse db. Which with replace=none is possible only via:

spring.datasource.url=jdbc:h2:mem:somewhere${random.uuid};DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;INIT=create schema if not exists somewhere

but I deeply hate this "solution". What can cause incorrect db re-initialization / missing cleanups / or what the cause of all this could be?

(edit: if you know better title to this question, please suggest. Thanks).

Answer 1

I was getting the same error after upgrading to Spring Boot 2.2.1 from 2.2.0. It looks 2.2.1 has updated the h2 dependency to 1.4.200. (https://github.com/spring-projects/spring-boot/releases/tag/v2.2.1.RELEASE)

H2 v1.4.200 has a change to update its DROP TABLE strategy (https://github.com/h2database/h2database/pull/1912). But hibernate's H2Dialect class has not been updated accordingly (both version 5.4.9, 5.4.10).

I extended the H2Dialect class to tell Hibernate org.hibernate.tool.schema.spi.SchemaMigrator to drop the constraints as follows to solve the problem.

Updates:

spring:
    jpa:
        database-platform: mypackage.MyH2Dialect


import org.hibernate.dialect.H2Dialect;

public class MyH2Dialect extends H2Dialect {

    @Override
    public boolean dropConstraints() {
        return true;
    }

    @Override
    public boolean supportsIfExistsAfterAlterTable() {
        return true;
    }

}

Related issues:

https://hibernate.atlassian.net/browse/HHH-13711

https://github.com/hibernate/hibernate-orm/pull/3093

https://github.com/h2database/h2database/pull/1912

See also: org.hibernate.tool.schema.internal.StandardForeignKeyExporter.getSqlDropStrings( )

Answer 2

I had the same issue and unfortunately Melkor's answer did not work for us because our schema was too complex to write a script dropping tables in a correct order.

H2 supports dropping all objects regardless of their dependencies, so the following script can reset the whole db.

src/test/resources/drop-tables.sql

DROP ALL OBJECTS

Then, specify the test properties to use the script to drop tables

src/test/resources/application.properties

spring.jpa.properties.javax.persistence.schema-generation.drop-source=script
spring.jpa.properties.javax.persistence.schema-generation.drop-script-source=drop-tables.sql

Answer 3

I wanted to let you know that I found a work-around for this problem. With javax.persistence properties you can define a drop script which will be executed before the automatic schema creation.

This enables you to specify the order in which the tables have to be dropped (dependend tables first). Maybe playing around with these could help you. Note that they replace the spring.jpa.hibernate.ddl property.

spring.jpa.properties.javax.persistence.schema-generation.database.action=drop-and-create
spring.jpa.properties.javax.persistence.schema-generation.drop-source=script-then-metadata
spring.jpa.properties.javax.persistence.schema-generation.drop-script-source=drop-tables.sql