Problem updating from 1.4.199 to 2.1.214

[Kris]

We've run into an unexpected problem upgrading from H2 1.4.199 to version 2.1.214 .

We're using H2 for unit tests in a Spring Boot/JPA application, currently version 2.5.5 .

The unit tests all ran successfully under 1.4.199, the code seems to execute correctly against our production Oracle instance, and if I use Derby for the Unit Tests they also execute successfully.

As far as I can tell the issue might be around our Parent entity, Person, which has quite a large number of child entities. In general the child entities are configured for Lazy loading like:

 @OneToMany(fetch = FetchType.LAZY, mappedBy = "person", cascade = {CascadeType.ALL}, orphanRemoval = true)

One of the unit tests that's failing is failing because the child entities are not returned. What's very perplexing is that if I change the FetchType to EAGER, _and_ I pre-fetch the child rows with a separate db call the unit test then passes.

// the call below pre-fetches the child data

Surname sn = surnameRepoForTest.findSurnameByPerson_Uuid(Integer.valueOf("222222222")).orElse(null);

// the rest of the unit test fails without the pre-fetch AND the EAGER fetchtype in Person

Person p = personSearch.getMatchingPersonRecordByEmplid("9546", null, "U", DateGetter.getDate("2012-01-01"), "Emp-last", "Emp-phone", null);

assertEquals("222222222", p.getUuid().toString());

Any suggestions on what to try to get this working in H2 2.1.214 with the LAZY fetch type and without the pre-fetch of the child data?

Thanks,

Kris

[Evgenij Ryazanov]

Hello!

2.5.5 is too old and it has hibernate-core 5.4.32.Final in its dependencies, this version doesn't support any new versions of H2. You need to check version of Hibernate ORM actually used by your application and upgrade it to 5.6.15.Final or 6.1.7.Final if necessary.

You also need to check SQL data types of columns with UUID values if you have them, because Hibernate ORM can incorrectly choose some wrong data type such as BINARY(255) for them, in historic versions of H2 it was acceptable, because it was incorrectly implemented as BINARY VARYING(255), but in new versions of H2 these data types have different standard-compliant implementations. The best data type for H2 is UUID, but BINARY(16) can also be used. Attempts to use BINARY(255) cause various problems, such as https://hibernate.atlassian.net/browse/HHH-15373

[Kris]

I was able to update to Spring 2.7.8 which mvn dependency:tree shows as using hibernate 5.6.14.Final. I get the same results with that. If I override the dependency to 5.6.15.Final I still get the same results.

The SQL datatype for the UUID field is CHAR(9) which was defined 20+ years ago so I'm mimicking that in the Unit tests in the schema.sql file.

The join in the child table object is defined by:

@ManyToOne
@JoinColumn(name = "uuid")
private Person person;

And in the parent object by:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "person", cascade = {CascadeType.ALL}, orphanRemoval = true)

private Set<Surname> surnames;

With the uuid value defined as:

@Id
@Column(name = "UUID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "personseq_generator")
private Integer uuid;

As I mentioned. it all worked before, it seems to work in Oracle outside of unit tests, and using Derby for the unit tests so I'm perplexed as to why it's not successful with H2.

Thanks,

Kris