How to add the mode=mysql to embedded H2 DB in Spring Boot 1.4.1 for @DataJpaTest?

Question

I have some problems with using a schema.sql file to create my sql schema when executing a junit test while this schema contains mysql specific expression. I have to add the mode=mysql to the H2 url.

For example something like this: jdbc:h2:mem:testd;MODE=MYSQL

But Spring boot automatically uses the url defined in the enum org.springframework.boot.autoconfigure.jdbc.EmbeddedDatabaseConnection with its url

jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE.

I have tried similiar approaches to get this to work, but spring does not take the spring.datasource.url=jdbc:h2:mem:testdb;MODE=MYSQL from my test-application.properties. All other settings from my test-application.properties have been read successfully.

If I let spring/hibernate create the schema (without the schema.sql file) with the javax.persistence annotations in my entities everything works fine.

Is there a simple way to add a mode?

Bakehouse

Answer 1

Set

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL

in application-test.properties, plus

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ActiveProfiles("test")

on the test class

Avelar

Answer 2

I was having this same issue. It would not pick up the url when running tests. I'm using flyway to manage my scripts. I was able to get all of these working together by following these few steps.

Created a V1_init.sql script in src/test/resources/db/migration so that it is the first script run by flyway.

SET MODE MYSQL; /* another h2 way to set mode */

CREATE SCHEMA IF NOT EXISTS "public"; /* required due to issue with flyway --> https://mcmap.net/q/503380/-schema-related-problems-with-flyway-spring-and-h2-embedded-database*/

Updated application-test.yaml to include the schema name public:

flyway:
  schemas: public

Ensure the test specified the profile: @ActiveProfiles("test")

Desmond

Answer 3

I have tried similiar approaches to get this to work, but spring does not take the spring.datasource.url=jdbc:h2:mem:testdb;MODE=MYSQL from my test-application.properties

Did you try to append this parameters instead of rewriting the existing ones?

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL

All other settings from my test-application.properties have been read successfully.

I thought that file should be named application-test.properties.

Gilud

Answer 4

You need to set MYSQL mode on h2 and disable replacing of datasource url for embedded database:

Modify application-test.yaml

spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;MODE=MYSQL  
  test:
    database:
      replace: NONE

Segno

Answer 5

I was able to run it with this config:

# for integration tests use H2 in MySQL mode
spring.datasource.url=jdbc:h2:mem:testdb;DATABASE_TO_LOWER=TRUE;MODE=MySQL;
spring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect

The main trick here is to force Hibernate to generate SQL scripts for MariaDB dialect because otherwise Hibernate tries to use H2 dialect while H2 is already waiting for MySQL like commands.

Also I tried to use more fresh MariaDB103Dialect for MariaDB 10.3 but it doesn't worked properly.

Thyratron

Answer 6

The issue is case sensitivity. I was able in the same fashion to run the liquibase init and failed on the jpa datasource because the schema was not found. If you are using MODE=MYSQL the schema is created in all-caps, so in order to reuse the same h2 (initialized by liquibase) in the tests, then you need to ensure the schema is by definition in all-caps.

spring.application.name=WEB TEST
spring.jpa.show-sql=true
spring.liquibase.enabled=true
app.db.name=TEST_DB
spring.test.database.replace=none
spring.datasource.url=jdbc:h2:mem:${app.db.name};DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS ${app.db.name};MODE=MYSQL
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
logging.level.org.h2=DEBUG

Peppermint