Liquibase is not executing SQL on MySQL/MariaDB but H2 in Spring Boot

Question

Versions
Liquibase: 3.6.2
Spring Boot: 2.1.0.RELEASE
MySQL/MariaDB: mysql Ver 15.1 Distrib 10.1.25-MariaDB, for Win32 (AMD64)
H2: 1.4.197

Goal

I have a migration script which transforms data from two tables into one. The script is written in SQL and is working correctly (verified by executing it manually). Further, when running the migrations with a H2 database it is working fine.

Problem

When I switch to MySQL/MariaDB the data are not inserted as defined in the script. However, according to the databasechangelog table the script ran properly:

004.transform_translatable_data     vetemi  classpath:db/changelogs/0004.transfrom_translatabl...   2018-12-07 16:26:14     68  EXECUTED    8:3dc05e2db0d93de8ce10de09612dd2c0  sqlFile         NULL    3.6.2   NULL    NULL    4196374083

There is also no error when running the application (log file):

2018-12-07 16:26:14.880  INFO 13628 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelogs/0004.transfrom_translatable_data.yaml::004.transform_translatable_data::vetemi ran successfully in 0ms

Note the end of the log message, the execution time is 0ms. This is suspicious.

Running the same setting on H2 shows an execution time of 16ms:

2018-12-07 16:37:40.699  INFO 13648 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelogs/0004.transfrom_translatable_data.yaml::004.transform_translatable_data::vetemi ran successfully in 16ms

Obviously the script didn't run, even though Spring and Liquibase are not showing any error.

The migration script:

INSERT INTO `activity_translatables` (`id`, `name`, `description`, `modified`, `created`, `language_id`, `parent_id`)
        SELECT UUID(), activities.name, activities.description, activities.modified, activities.created, languages.id, activities.id
        FROM `activities`, `languages`
        WHERE languages.locale = 'de';

The liquibase changelog:

- changeSet:
    id: 004.transform_translatable_data
    author: vetemi
    changes:
    - sqlFile:
        dbms: h2, mysql
        encoding: utf8
        endDelimiter: \nGO
        path: ../data/004.transform_translatable.sql
        relativeToChangelogFile: true
        splitStatements: true
        stripComments: true

I also tried:

- changeSet:
id: 004.transform_translatable_data
        author: vetemi
        changes:
        - sql:
        comment: transfer all activities translatables into the new table with German as default
        dbms: mysql, h2
        splitStatements: true
        sql: insert into activities_translatables (id, name, modified, created, language_id, parent_id)
                    select UUID(), activities.description, activities.modified, activities.created, languages.id, activities.id
                    from activities, languages
                    where languages.locale = 'de'
        stripComments: true

The property file:

spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://localhost:3306/db
spring.datasource.username=root
spring.datasource.password=
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.platform=mysql

What am I missing? Why does it not work?

Victor Victor · Accepted Answer · 2019-10-25T08:54:52

We are currently migrating from Spring Boot 1.5.x to Spring Boot 2.1.17 and we have faced the same issue with Liquibaseand a MariaDB database.

The problem seems to be that they new distinguish MySQL from MariaDband this configuration is no longer valid:

<property name="re.component.name" value="edos-dp-project-manager" />

 <changeSet author="edosdp" id="1.0.0-${re.component.name}" logicalFilePath="path-independent">
     <sqlFile encoding="utf8" endDelimiter=";" path="Create_DataBase.sql" relativeToChangelogFile="true" splitStatements="true" stripComments="true" dbms="mysql"/>
     <sqlFile encoding="utf8" endDelimiter=";" path="Create_Database_H2.sql" relativeToChangelogFile="true" splitStatements="true" stripComments="true" dbms="h2"/>
</changeSet>

There is one log message (at DEBUG level!!) that says this when this situation happens:

2019-10-25 10:33:23,249+0200 [DEBUG] liquibase.changelog.ChangeSet edos-dp-project-manager 1.5.0-SNAPSHOT - Change sqlFile not included for database mariadb

So,You now need to set the DMBS to mariadb instead so the changelog looks like this:

<property name="re.component.name" value="edos-dp-project-manager" />
<changeSet author="edosdp" id="1.0.0-${re.component.name}" logicalFilePath="path-independent">
 <sqlFile encoding="utf8" endDelimiter=";" path="Create_DataBase.sql" relativeToChangelogFile="true" splitStatements="true" stripComments="true" dbms="mariadb"/>
 <sqlFile encoding="utf8" endDelimiter=";" path="Create_Database_H2.sql" relativeToChangelogFile="true" splitStatements="true" stripComments="true" dbms="h2"/>
</changeSet>

Liquibase is not executing SQL on MySQL/MariaDB but H2 in Spring Boot

1 Answers