Spring Boot 2.7.0 Liquibase fails with "Migration files for change set"

Question

I was using Spring Boot 2.6.8 along with Liquibase which works fine. This is used with a MySQL database when running in production. For integration tests we run it against a H2 in memory database.

Since upgrading to SB 2.7.0, which switches out H2 v1.4.200 to v2.1.212, Liquibase then fails with the H2 in memory database.

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.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set changelog/0.3.0/tables/file_type.xml::1437572803912-1::bobd (generated):
Reason: liquibase.exception.DatabaseException: Syntax error in SQL statement "CREATE TABLE PUBLIC.FILE_TYPE (ID INT[*](10) GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(45) NOT NULL, CONSTRAINT PK_FILE_TYPE PRIMARY KEY (ID))";
expected "ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )";
SQL statement: CREATE TABLE PUBLIC.FILE_TYPE (ID INT(10) GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(45) NOT NULL, CONSTRAINT PK_FILE_TYPE PRIMARY KEY (ID)) [42001-212] 
[Failed SQL: (42001) CREATE TABLE PUBLIC.FILE_TYPE (ID INT(10) GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(45) NOT NULL, CONSTRAINT PK_FILE_TYPE PRIMARY KEY (ID))]

Now Liquibase should be able to map our changelog to MySQL or H2 (or any other DB) fine right? So I'm thinking this is an issue with Liquibase not support H2 v2.x properly? Or perhaps there is a property that I've missed that now needs setting?

TIA.

Russianize

INT(10) is not a valid data type for H2, H2 accepts it only in MySQL and MariaDB compatibility modes: h2database.com/html/features.html#compatibility – Fish

From the error it seems some syntax error in the sql query you are trying to run through liquibase changeset. – Synagogue

I’ve added MODE=MYSQL to my DB connection string yet H2 v2.x still gives the above error. I get that H2 does not support INT(10) but isn’t Liquibase suppose to convert things in your change log to work with whatever DB you target? It doesn’t seem right that we’d have to retrospectively update all our old change log files. – Russianize

Could you please provide your build file? Part with spring framework and liquibase. My problem was with build file - we accidentally fetched newer version of liquibase from spring itself. Changing to 2.7 spring boot and 4.5 liquibase(newer spring gave me 4.9.1) resolved issue – Dreher

Answer 1

I've got the similar error upon migration to the Spring Boot 2.7.0. In my case it concerned the new H2 reserved word 'value'. I guess, in your case the problem is in the token (10) after INT.

Here is quotation for the section H2 2.1 from the Spring Boot doc:

Spring Boot 2.7 has upgraded to H2 2.1.120. H2 2.x is backwards incompatible and fixes a number of security vulnerabilities. See the H2 changelog and migration guide for details of the changes and how to handle the upgrade.

Mikkimiko

I could change that in all of our change logs... but then this would be changing it just to work with integration tests. In production it works fine with the MySQL database. It's sounding like Liquibase is no longer database independent since H2 v2. I guess we will have to switch to using MySQL for our integration tests. – Russianize

Answer 2

I got the same error using a MS SQL Server database, I resolved issue by configuring hibernate dialect and H2 in SQL Server Mode in the application.properties file in the test resources folder.

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2016Dialect
spring.datasource.url=jdbc:h2:mem:testdb;MODE=MSSQLServer

Or you can disable liquibase and allow the tables to be auto-generated spring.liquibase.enabled=false

Disorganization

Thanks. I popped in the dialect setting which I was missing, and worked fine without in SB 2.6.8, and I get the same error. I do not want to let the tables auto generate as this is not what happens in production. So I'm going to go back to looking at using the the same database type for integration tests as production which I guess is a better test anyway. – Russianize

Same problem as OP, but using Oracle DB for the app. Adding spring.datasource.url: jdbc:h2:mem:testdb;MODE=Oracle for the test profile did the trick. No other changes needed. Thank you! – Goatskin

Answer 3

I had this issue and fixed by downgrading the h2 to a lower version

testImplementation 'com.h2database:h2:1.4.200'

Another solution is what is discussed in the H2 Google Group, where the h2 engine mode.limit is set in the dataSource bean

@TestConfiguration
public class DataSourceTestConfig {

    @Bean
    public DataSource dataSource() {
        // Fix for the H2 2.x issue with spring boot 2.7.
        org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("Oracle");
        mode.limit = true;
        return new EmbeddedDatabaseBuilder() //
                .setType(H2) //
                .setName("testdb-h2;MODE=Oracle") //
                .ignoreFailedDrops(true) //
                .build();
    }

}

Brat

Yeah I’ve fixed it by downgrading the H2 version in for now. I consider these workarounds though and not solutions. Hopefully a future LiquiBase update will fix this properly. – Russianize

Answer 4

For completeness, I recreated an example bug for this on the Liquibase Github repo. and it has since been fixed by the developers.

Russianize