Spring boot testing with liquibase fails

Question

I have been trying for quite some time to figure out a solution for my problem, to no avail.

Anyway, i have a bunch of integration tests (in a nonstandard directory testRegression parallel to the standard test directory).

These integration tests use an h2 in memory database. In production as well as for testing i am using liquibase to simulate the schema evolution.

My properties (in application-testRegession.properties) look as follows:

spring.liquibase.enabled=true
spring.liquibase.user=sa
spring.liquibase.password=
spring.liquibase.change-log=classpath:/liquibase/changelog-master.xml

spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=

The error i consistenly keep getting is:

2020-07-21 15:57:34.173 INFO  [liquibase.lockservice.StandardLockService] [Test worker:13]: Successfully acquired change log lock
2020-07-21 15:57:34.303 INFO  [liquibase.changelog.StandardChangeLogHistoryService] [Test worker:13]: Creating database history table with name: PUBLIC.DATABASECHANGELOG
2020-07-21 15:57:34.305 INFO  [liquibase.executor.jvm.JdbcExecutor] [Test worker:13]: CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))
2020-07-21 15:57:34.307 INFO  [liquibase.lockservice.StandardLockService] [Test worker:13]: Successfully released change log lock
2020-07-21 15:57:34.309 WARN  [org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext] [Test worker:13]: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.DatabaseException: Table "DATABASECHANGELOG" already exists; SQL statement:
CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10)) [42101-197] [Failed SQL: (42101) CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
2020-07-21 15:57:34.309 INFO  [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - Shutdown initiated...
2020-07-21 15:57:34.324 INFO  [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - Shutdown completed.
2020-07-21 15:57:34.326 INFO  [org.apache.catalina.core.StandardService] [Test worker:13]: Stopping service [Tomcat]
2020-07-21 15:57:34.342 INFO  [org.springframework.boot.autoconfigure.logging.ConditionEvaluationReportLoggingListener] [Test worker:13]: 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-07-21 15:57:34.345 ERROR [org.springframework.boot.SpringApplication] [Test worker:13]: Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.DatabaseException: Table "DATABASECHANGELOG" already exists; SQL statement:

So how can i get around this issue? My basic understanding is that each test class creates its own ApplicationContext. For that it creates and loads a liquibase bean into it. However, this problem occurs only for 2 out of 42 tests.

I would really like to get to the bottom of this and understand whats going on. Can anyone shed light on my problem?

ADDITIONALLY The test all run fine individually, but when run as a group they fail.

UPDATE 1 The relevant properties are as follows:

spring.main.allow-bean-definition-overriding=true
spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.hikari.connectionTimeout=10000
spring.datasource.hikari.idleTimeout=60000
spring.datasource.hikari.maxLifetime=180000
spring.datasource.hikari.maximumPoolSize=50
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

My configuration is:

@Configuration
@ComponentScan(
    basePackages = {
      "com.aareal.nmc"
    },
    excludeFilters = {
      @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = CommandLineRunner.class)
    })
@EnableTransactionManagement
@Profile("testRegression")
@SpringBootApplication(exclude = SecurityAutoConfiguration.class)
@EnableConfigurationProperties(LiquibaseProperties.class)
public class RegressionTestConfig {

My two tests are annotated as:

@RunWith(SpringRunner.class)
@SpringBootTest(
    classes = {
      RegressionTestConfig.class
    },
    //webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT)
    webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)

Thanks

Lockard

When using concurrent tests they might use the same database, also using @DirtiesContext will probably try to re-run ligquibase – Flagellum

I am not using @DirtiesContext anywhere in my tests. Also, at least as far as i can see, the tests are not being run concurrently (all are on thread worker:13). – Lockard

WIthout seeing the tests and configuration it is impossible to answer this. Something must be interfering with the DB (generally the context is loaded once, unless the config is different and that might be problematic with H2 in the same VM as it might reuse the existing one). – Flagellum

Answer 1

For my special case (that is for internal testing only, not production) what i have the following:

src
|-- main
|-- test
|-- testRegression

Workaround

1. Decide on the version of liquibase to use (i chose 4.0.0, which is the most recent at this point)

2. Create a file "src/testRegression/java/liquibase/changelog/StandardChangeLogHistoryService.java"

3. Open the original liquibase file "StandardChangeLogHistoryService.java" (mine is in ~//.gradle/caches/modules-2/files-2.1/org.liquibase/liquibase-core/4.0.0/23a5317eb5005b4765cd85e6f3a2cc4bb55c0daa/liquibase-core-4.0.0-sources.jar which i copied and unzipped) and copy its contents 1:1 into the newly created file in 2.

4. Add a catch block by changing code (around line 396) from

      if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
         executor.execute(sql);
         getDatabase().commit();
      } else {
   

to

   if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
    try {
      executor.execute(sql);
      getDatabase().commit();
    } catch (DatabaseException excptn) {
      Scope.getCurrentScope()
          .getLog(getClass())
          .warning(
              "Table '"
                  + getDatabase()
                      .escapeTableName(
                          getLiquibaseCatalogName(),
                          getLiquibaseSchemaName(),
                          getDatabaseChangeLogTableName())
                  + "' already exists.");
    }
  } else {

This is simply a workaround since there could be legitimate reasons for the ChangeLogTable table creation to fail. However, it already existing should not be cause for a major failure in my opinion.

My current view is that this is something which ought to be addressed/fixed in the official liquibase code base.

The following post(s) were helpful: https://github.com/liquibase/liquibase-cache/issues/1

Lockard

I found something interesting about this : --If JunitTestCase determines that the context or db is the same it will reuse it. This would explain your error I'm not sure about the wording and what context or db or whatever but it might be a clue. – Creak

Answer 2

I had the same issue, and it seems to have been caused by case sensitive checking of the database table name. That is, the table was created as 'DATABASECHANGELOG', but Liquibase was checking for the existence of 'databasechangelog'.

The fix (at least for an H2 database) is to specify case insensitive identifiers in the database URL. For example:

jdbc:h2:mem:~/mydb;CASE_INSENSITIVE_IDENTIFIERS=TRUE

Explanation: The Spring test process fires up one or more instances of the Spring container to run tests on. If it thinks the config is exactly the same for two tests it will re-use an instance, otherwise it will start a new one. The instances are shared to avoid needing to start a whole new Springboot application for each test. But, the problem is that the instances may share some resources, such as databases and network ports. Therefore errors might occur from trying to start multiple instances at the same time. In this case, the test suite is starting two instances using the same database, but the second one is trying to re-run the whole Liquibase setup because the case sensitive issue means it doesn't see the table has already been created.

Gregorio

I finally found this after 2 hours of searching, thank you! – Preengage

@BarrySW19: Thank you for your response (i am a little late responding). I am not sure that the problem you are addressing is the one i described (or i do not understand it). My problem is that the database table "DATABASECHANGELOG" already exists. The database is shared between the 2 tests and each test tries to create the table. I agree with your explanation, but I am not sure how using "CASE_INSENSITIVE_IDENTIFIERS=TRUE" would resolve help this? – Lockard

@Lockard - Liquibase should be checking to see if the table already exists, and only creates it if it doesn't. There may be other reasons it doesn't see the table already exists, but the case sensitivity issue was the problem in my case. – Gregorio

In my case this isin't working. CASE_INSENSITIVE_IDENTIFIERS=TRUE is set to true, but liquibase doesn't seem to check if the table already exists. – Relic

Answer 3

With combination of H2 and Liquibase logic check existed changelog table and configure the URL of the database pointed to the same instance causing this issue, details below:

1. The H2, by default, it's using UPPER CASE for table name and maybe others. And H2 will be trick case-sensitive by default.
2. You configured the H2 mode is PostgresSQL, and force H2 using the lower case by DATABASE_TO_LOWER=TRUE (in newer version) or DATABASE_TO_UPPER=FALSE (in older version)
3. The Liquibase logic check existed changelog table not handle correctly as force H2 to use lower case (I don't dive deeply how Liquibase did it)

spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp

4. The 2 of 42 your tests created 2 news Spring container/Application Contexts and the (3) above will try to create re-run the Liquibase, and it will create the table then failed.

The solution is, we should configure difference Spring container/context using difference database instance.

Follow the documentation of H2 when using in-memory database, we can omit the name (the testdb in your case) then 2 connections within the same VM will open 2 database instance. Full reference docs here: In-Memory Databases

For your specific config, the final would be as below. I removed the string testdb after the string jdbc:p6spy:h2:mem:

spring.datasource.url=jdbc:p6spy:h2:mem:;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp

Site node: in case you need to use h2-console, you should specify the database name in the url. In this case, you can create 2 profiles: default (application.properties) and test (application-test.properties). Then the test profile can override the spring.datasource.url property.

Then you direct the test classes using the test profiles, there is many way to do that by using

1. ENV variable: SPRING_PROFILES_ACTIVE=test
2. If running Maven: -Dspring.profiles.active=test
3. Hard-code in the test classes: @ActiveProfiles({"test"}) and maybe more

In some older version or style. When you use DATABASE_TO_UPPER=FALSE, you can combine with CASE_INSENSITIVE_IDENTIFIERS=TRUE and still keep the database name in the url like below:

spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp

The choice is yours.

Allveta