H2 usage in test scope: v2.0.x not compatible to JPA autogenerated database modell

[Bluewhale1502]

Hi there,

first of all thanks to H2! It helps a lot in projects especially in local development setups.

In my project junit tests manages their own test data and I let the databasemodell  be generated by JPA in test scope, so I have no special *.sql file around (on which I can apply any syntax migrations) to create the schema or populate the database.

Everything worked well with v1.4 - however attempts to update to v2.0.x fails due to syntax problems on the JPA generated SQL.

Do I need to configure some more flags to get it work wit v2?

For an example and reproduction to this issue see: https://github.com/StefanSchubert/sabi/issues/106

Thanks for any help inadvance

Stefan

[Evgenij Ryazanov]

Hello!

What implementation of JPA do you use?

Hibernate ORM 5.x doesn't support H2 2.0.

Hibernate ORM 6.0 is under development, it already has some fixes and others are on the way.

There are two possible temporary workarounds:

1. You can add ;MODE=LEGACY to JDBC URL of H2 (you need H2 2.0.204 or later version).

2. You can override H2Dialect and fix wrong SQL produced by it.

EclipseLink and OpenJPA may have own issues, but I have no information about them. LEGACY mode may also help, however.

[Bluewhale1502]

Hi,

I'm using EclipseLink in this project, which implements JPA 2.0

I tried the suggested workaround with:

....(from application springboot properties)

spring.datasource.jpa.url: jdbc:h2:mem:test;MODE=LEGACY;DB_CLOSE_ON_EXIT=true;DB_CLOSE_DELAY=-1

spring.jpa.database-platform: org.hibernate.dialect.H2Dialect

This results into:

Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Funktion "IDENTITY" nicht gefunden
Function "IDENTITY" not found; SQL statement:
CALL IDENTITY() [90022-206]
Error Code: 90022
Call: CALL IDENTITY()
Query: ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="CALL IDENTITY()")

Same result if I try MariaDB10Dialect which is my production database.

Cheers

Stefan

[Evgenij Ryazanov]

You cannot use dialects from Hibernate ORM with EclipseLink.

EclipseLink has own H2Platform and perhaps its supportsIdentity() method should return false until these issues will be fixed in a some proper way:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=578086

https://github.com/eclipse-ee4j/eclipselink/issues/1393

I don't know is it possible to override platforms like dialects in Hibernate or not.

[Evgenij Ryazanov]

You can compile H2 from its current sources or wait for release of H2 2.1.210 and try the LEGACY mode again.

I added IDENTITY() and SCOPE_IDENTITY() to this mode.

[Bluewhale1502]

Thanks for help Evgenij!

 I will check again as soon I can fetch the 2.0.210 via maven.

[Bluewhale1502]

Meanwhile v2.1.210 is available on maven central now.

I can also confirm that the fix solved my issue. 

Thanks a lot for listening and solving.

Happy coding

Stefan