0

I try to test my code with H2 DB. I've some migrations for all steps(init, schema, data). For running my migrations I use nkonev r2dbc-migrate

When I try to run a test I catch an exception "SQL Grammar exception" with means that H2 doesn't work with 'LIMIT". I made some changes

r2dbc:h2:mem:///testdb?options=MODE=PostgreSQL

But it gave me only an other problem:

io.r2dbc.spi.R2dbcBadGrammarException: Duplicate column name "?column?"; SQL statement:
insert into "migrations_lock"(id, locked) select * from (select 1, false) x  where not exists(select * from "migrations_lock") [42121-214]

I've tried all dialects like a mode property. I've tried all dialects like a migration dialect. But nothing changed.

What did I make wrong?

Application is: Spring Boot 2.7.5 + WebFlux + Kotlin

My gradle dependencies:

implementation("name.nkonev.r2dbc-migrate:r2dbc-migrate-spring-boot-starter:2.7.8")

implementation("io.r2dbc:r2dbc-pool:0.9.2.RELEASE")
implementation("io.r2dbc:r2dbc-spi:1.0.0.RELEASE")
implementation("io.r2dbc:r2dbc-postgresql:0.8.13.RELEASE")
implementation("io.r2dbc:r2dbc-h2:0.9.1.RELEASE")

When I run the app with PostgreSQL as usual everything works well.

I tried to change a dialect for H2. I thought it can help me with my problem. But PostgreSQL/Oracle didn't help. Oracle has problem with SERIAL and I changed my migrations for solving this problem.

hormunkul
  • 1

1 Answers1

0

The solution in my case was in changing the position of keyword LIMIT with OFFSET

From

order by v.id asc
offset 0
limit 10

To

order by v.id asc
limit 10
offset 0

This small change gives me an option return back to default H2 setup.

hormunkul
  • 1