SpringBoot Incompatible versions of H2 and Liquibase after update to 1.5.

After update from 1.5.10.RELEASE to 1.5.12.RELEASE. We are using Liquibase on top of H2 with PostgreSQL compatibility mode.

For such Liquibase script:

  - changeSet:
      changes:
      - addColumn:
          tableName: talent
          columns:
          - column:
              name: rate
              type: numeric(20, 2)

I get this error:

Caused by: liquibase.exception.DatabaseException: Unknown data type: "NUMBER"; SQL statement:
ALTER TABLE PUBLIC.talent ADD rate NUMBER(20, 2) [50004-197] [Failed SQL: ALTER TABLE PUBLIC.talent ADD rate NUMBER(20, 2)]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:309)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
    ... 49 more
Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "NUMBER"; SQL statement:
ALTER TABLE PUBLIC.talent ADD rate NUMBER(20, 2) [50004-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.command.Parser.parseColumnWithType(Parser.java:4400)
    at org.h2.command.Parser.parseColumnForTable(Parser.java:4215)
    at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:6662)
    at org.h2.command.Parser.parseAlterTableAddColumn(Parser.java:6324)
    at org.h2.command.Parser.parseAlterTable(Parser.java:5983)
    at org.h2.command.Parser.parseAlter(Parser.java:5373)
    at org.h2.command.Parser.parsePrepared(Parser.java:362)
    at org.h2.command.Parser.parse(Parser.java:335)
    at org.h2.command.Parser.parse(Parser.java:307)
    at org.h2.command.Parser.prepareCommand(Parser.java:278)
    at org.h2.engine.Session.prepareLocal(Session.java:611)
    at org.h2.engine.Session.prepareCommand(Session.java:549)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
    at sun.reflect.GeneratedMethodAccessor306.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy124.execute(Unknown Source)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:307)
    ... 54 more

H2 issue I created was closed as they removed incorrect "NUMBER" type in 1.4.197 version: https://github.com/h2database/h2database/issues/1050

I tried to update to Liquibase 3.6.0, but it doesn't seem to be supported by Spring Boot yet.

Comment From: lkrnac

Workaround is to downgrade to H2 1.4.196 version.

Comment From: wilkinsona

Thanks for the report, but there's nothing we can do here. We don't want to downgrade everyone to H2 1.4.196. This should be reported to and addressed in Liquibase 3.5.x if it hasn't been already.

I tried to update to Liquibase 3.6.0, but it doesn't seem to be supported by Spring Boot yet

If Liquibase 3.6 is backwards compatible with 3.5, it should work. If it doesn't, please open a new issue and we'll see what it would take to be compatible with both 3.5 and 3.6.

Comment From: lkrnac

For the record, I created Liquibase issue: https://liquibase.jira.com/browse/CORE-3202

BTW, update to 3.6.0 doesn't help

Comment From: simon-lam

Wanted to share another workaround just in case - it seems you can also get around the data type error by explicitly setting the dbms attribute in your liquibase change set to postgresql.

e.g:

- changeSet:
      dbms: postgresql
      changes:
           ...

Works with Liquibase 3.6.1 and H2 1.4.197 running in PostgresQL mode

Comment From: nick318

For the record, I created Liquibase issue: https://liquibase.jira.com/browse/CORE-3202

BTW, update to 3.6.0 doesn't help

@lkrnac I fixed it in https://github.com/liquibase/liquibase/pull/3098