2

I have a project with Spring Boot 2.2.0, H2 and Flyway that's working fine. Spring Boot 2.2.1 just came out (so I got a Dependabot PR), and now my migrations are failing like this (paths/table names/columns sanitized):

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1__Creating_tableName_table.sql failed
------------------------------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "CREATE TABLE MY_PROJECT_TABLE (
-- table definition here
) ENGINE=[*]INNODB"; expected "identifier"; SQL statement:
CREATE TABLE my_project_table (
-- table definition here
) ENGINE=InnoDB [42001-200]
Location   : db/migration/V1__Creating_tableName_table.sql (/path/to/file.sql)
Line       : 1
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1803) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]

Looks like it doesn't like the ENGINE=INNODB part

Nothing changed except Spring Boot's version (and from the looks of things, that means some transitive dependencies, including H2), but I don't see an obvious issue in the issues or the changelog for Spring Boot that suggests where the problem lies.

I'm guessing this either has something to do with Spring Boot's auto configuration of H2 as a test database (maybe the compatibility mode?) or just a change with H2, but it's just a guess, since I'm having trouble finding an obvious cause in the issues/changelog.

Geoffrey Wiseman
  • 5,459
  • 3
  • 34
  • 52
  • Do you have a trailing comma in your table definition? H2 1.4.200 (which is the default version in Boot 2.2.1) dropped support for them: https://github.com/h2database/h2database/pull/2099. – Andy Wilkinson Nov 12 '19 at 20:26
  • Yeah, I found that issue and looked, and, no, I don't see any trailing commas. – Geoffrey Wiseman Nov 13 '19 at 18:06

1 Answers1

3

You cannot use ENGINE=InnoDB clause in H2 1.4.200 unless MySQL compatibility mode is enabled.

If you want to enable it, append ;MODE=MySQL to the connection URL. You also may want to add ;DATABASE_TO_LOWER=TRUE and possibly ;CASE_INSENSITIVE_IDENTIFIERS=TRUE. These flags aren't enabled automatically for MySQL compatibility mode.

Evgenij Ryazanov
  • 6,960
  • 2
  • 10
  • 18
  • And this is a change new to H2 1.4.200? Because I'm using Spring Boot's autoconfiguration of the test database, I'm not actually specifying the JDBC URL anywhere. My non-test database is MySQL, and that's specified, but the test database is auto configured with no input from me. – Geoffrey Wiseman Nov 13 '19 at 18:07
  • I could do something like this, but I'd prefer not to if there's another path to tweaking the autoconfiguration: https://stackoverflow.com/a/43557541/141042 – Geoffrey Wiseman Nov 13 '19 at 18:09
  • 1
    Yes, in 1.4.200 support of these MySQL compatibility clauses was improved to accept more different constructions and was also restricted to MySQL compatibility mode, older versions of H2 incorrectly accept some of them in Regular mode too. You shouldn't use vendor-specific features without a compatibility mode, they have no meaning in other databases. – Evgenij Ryazanov Nov 14 '19 at 04:10
  • Sure -- makes sense, just wanted to make sure that this explained why the migrations worked in Spring Boot 2.2.0 but not 2.2.1. I'd love for Spring Boot to set the compatibility mode based on the non-test database, but at least now I understand what changed where to look into making a fix. – Geoffrey Wiseman Nov 14 '19 at 05:33