H2 in mem database generates database table names in lower case but when insert data from data.sql f-CodePudding

I have a Spring Boot project with different databases for dev (MySQL) and test (H2 in memory). I also have two data.sql files configured for dev and test profiles. In test when Hibernate creates database tables, it uses lower-case table names. After database creation, app tries to fill database with insert queries inside data-test.sql file, it looks for tables in UPPER_CASE and failes:

Hibernate create table sql:
create table "activity_domain" (...)

data-test.sql:
INSERT INTO activity_domain (title) VALUES ('Activity Domain One');

Error message:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "ACTIVITY_DOMAIN" not found (candidates are: "activity_domain"); SQL statement:
INSERT INTO activity_domain (title) VALUES ('Activity Domain One') [42103-212]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502) DbException.java:502
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) DbException.java:477
    at org.h2.message.DbException.get(DbException.java:223) DbException.java:223
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8379) Parser.java:8379
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8347) Parser.java:8347
    at org.h2.command.Parser.readTableOrView(Parser.java:8336) Parser.java:8336
    at org.h2.command.Parser.readTableOrView(Parser.java:8306) Parser.java:8306
    at org.h2.command.Parser.parseInsert(Parser.java:1641) Parser.java:1641
    at org.h2.command.Parser.parsePrepared(Parser.java:814) Parser.java:814
    at org.h2.command.Parser.parse(Parser.java:691) Parser.java:691
    at org.h2.command.Parser.parse(Parser.java:661) Parser.java:661
    at org.h2.command.Parser.prepareCommand(Parser.java:568) Parser.java:568
    at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631) SessionLocal.java:631
    at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554) SessionLocal.java:554
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) JdbcConnection.java:1116
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237) JdbcStatement.java:237
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) JdbcStatement.java:223
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261)

More info:

application.properties for test profile:

spring.datasource.url=jdbc:h2:mem:my-test-db;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.defer-datasource-initialization=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.sql.init.data-locations=classpath:data-test.sql

CodePudding user response：

DATABASE_TO_UPPER=FALSE is an option suitable only for very old versions of H2 Database. It works in different way with modern versions and it effectively makes all identifiers case sensitive. It doesn't enable case insensitive identifiers any more, they are controlled by another option.

With modern versions if you need to have some compatibility with MySQL you need to add ;MODE=MySQL;DATABASE_TO_LOWER=TRUE instead. You may also need ;CASE_INSENSITIVE_IDENTIFIERS=TRUE, see documentation of compatibility modes for mode details: https://h2database.com/html/features.html#compatibility