I integrated Liquibase in my Spring Boot application. Using it with MariaDB works fine.

For unit & integration testing I use a H2 in-memory database. While running tests it causes following exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select user0_.id as id1_19_0_, user0_.created_at as created_2_19_0_, user0_.updated_at as updated_3_19_0_, user0_.version as version4_19_0_, user0_.activated as activate5_19_0_, user0_.birthday as birthday6_19_0_, user0_.email as email7_19_0_, user0_.firstname as firstnam8_19_0_, user0_.force_password_change as force_pa9_19_0_, user0_.password_hash as passwor10_19_0_, user0_.role as role11_19_0_, user0_.surname as surname12_19_0_ from users user0_ where user0_.id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-5.3.19.jar:5.3.19]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.19.jar:5.3.19]

My prod config (working):

spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    url: jdbc:mariadb://localhost:3307/testdb
    username: user
    password: psw

  jpa:
    generate-ddl: false
    database-platform: org.hibernate.dialect.MariaDB103Dialect

  sql:
    init:
      mode: never

  liquibase:
    enabled: true
    change-log: classpath:db/db.changelog-master.yaml

My test config (not working):

spring:
  main:
    allow-bean-definition-overriding: true

  datasource:
    url: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;NON_KEYWORDS=user
    username: sa
    driver-class-name: org.h2.Driver
    password: password

  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    generate-ddl: false

  sql:
    init:
      mode: never

  liquibase:
    change-log: classpath:db/db.changelog-master.yaml
    enabled: true

  test:
    database:
      replace: none

What I tried:

• Multiple url configs including the NON_KEYWORDS option from H2 to exclude problems with user as it is maybe interpreted as a keyword.

As it is working with MariaDB I think it is a problem related to H2.

Thankful for any help, documentation, link whatever :)

CodePudding user response：

You have your h2 url set to this string:

    datasource:
    url: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;NON_KEYWORDS=user
    username: sa
    driver-class-name: org.h2.Driver
    password: password

In the documentation linked here, you can see that they've set that up slightly differently than you have with spring boot and h2:

By default, Spring Boot configures the application to connect to an in-memory store with the username sa and an empty password.

However, we can change those parameters by adding the following properties to the application.properties file:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Since the error references the hibernate URL towards the end, and the only change made was to add the h2 dataset, I searched for spring boot and h2 to find the documentation.

CodePudding user response：

Actually, the problem was with my schema. As H2 does not support some features MariaDB does, it is not starting and therefore jpa is not able to prepare the SQL statement as the tables do not exist.