Update h2 database - Githubissues

famod commented 4 years ago

1.4.200 was released over half a year ago and another release doesn't seem so far away. Quarkus is still on 1.4.197 which prevents us in our current project to benefit from the Postgres compatibilty enhancements and other fixes that went in after 1.4.197.

I understand that 1.4.197 is still in place due to #1522. But has there ever been an effort to try to fix that problem in h2 (GH issue/PR?)? I don't think it is a good idea to stay on that old version forever.

famod commented 4 years ago

I forgot to mention that 1.4.200 does not fix the Agroal problem: https://github.com/famod/quarkus/actions/runs/105151071

famod commented 4 years ago

cc @Sanne

Sanne commented 4 years ago

What do you propose we do?

A first requirement for me is that a chosen H2 version is able to pass the testsuite of Hibernate ORM. As far as I know, we're using the last released version which was able to do it.

famod commented 4 years ago

A first requirement for me is that a chosen H2 version is able to pass the testsuite of Hibernate ORM.

Makes perfect sense!

What do you propose we do?

Well, I guess it should start by creating issues at https://github.com/h2database/h2database/issues, no?

Sanne commented 4 years ago

yes that's a good plan, except that I can't volunteer for it as I already have many things on my plate.

Someone would need to volunteer to try out the latest (and possibly H2 master), diagnose any problem and provide some good reports to the H2 team. I'd certainly love to see that happen, as H2 is amazing :)

famod commented 4 years ago

I can try to support but I fear this will quickly exceed my time budget (and knowledge about Hibernate and h2).

Do we need a ticket over at https://hibernate.atlassian.net/projects/HHH/issues/ ?

Sanne commented 4 years ago

I can try to support but I fear this will quickly exceed my time budget (and knowledge about Hibernate and h2).

That's great! No fear, if it gets complicated I understand and we'll postpone - we'll just cross fingers and hope it's not that bad.

Do we need a ticket over at https://hibernate.atlassian.net/projects/HHH/issues/ ?

To send a pull request to Hibernate ORM, yes you'll need a JIRA first as we require commit logs to refer to some HHH-code . But you could try to upgrade the version locally first, and create the ticket later if you prefer.

piyushwadhwani commented 4 years ago

i can help testing it if someone can guide me , I am new to quarkus though

famod commented 4 years ago

We now have a Hibernate ticket for that: https://hibernate.atlassian.net/projects/HHH/issues/HHH-14031

@piyushwadhwani Hibernate has to update to a newer h2 version first, so there is nothing Quarkus-specific for now.

famod commented 4 years ago

FYI: I have started working on the update in Hibernate: https://github.com/hibernate/hibernate-orm/pull/3412

There is still a lot to do and feedback is a bit sparse.

Sanne commented 4 years ago

sorry for the delays @famod , I've been dragged into urgent bugfixes and couldn't check this out yet .. but we'll get to it eventually!

shahdadpuri-varun commented 4 years ago

Any updates to this issue?

famod commented 4 years ago

@shahdadpuri-varun My PR https://github.com/hibernate/hibernate-orm/pull/3412 is more or less stuck due to lack of feedback from the Hibernate team (no offence!) and lack of time on my side.

famod commented 4 years ago

One example why this outdated h2 version can be a real PITA:

We are using h2 for simple tests und Postgres (PG) for production. PG has an AGE function and we tried to add this to h2 but this fails because h2 1.4.197 does not yet know INTERVAL. See also: https://stackoverflow.com/a/58668363

famod commented 4 years ago

Workaround: For now we added this to our root dependencyManagement:

            <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <version>1.4.200</version>
            </dependency>

The Quarkus team will surely not recommend this officially, so use at your own risk!

shahdadpuri-varun commented 4 years ago

Workaround: For now we added this to our root dependencyManagement:
            <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <version>1.4.200</version>
            </dependency>
The Quarkus team will surely not recommend this officially, so use at your own risk!

This works well if you use mvn quarkus:dev. But it fails when you try to build a native image.

famod commented 4 years ago

It also works in JVMmode but yeah, I haven't checked native mode.

famod commented 4 years ago

I have rather bad news: Hibernate can only update from 1.4.196 to 1.4.197 for now! The PR for that is here: https://github.com/hibernate/hibernate-orm/pull/3516 And the reasoning for not being able to update to .198, .199 or .200 is here: https://github.com/hibernate/hibernate-orm/pull/3412#issuecomment-678813877 (see also subsequent comments)

Since Quarkus is already using .197, I don't see an update in Quarkus anytime soon. 😞

I will still try to move things forward in Hibernate in the draft PR https://github.com/hibernate/hibernate-orm/pull/3412 but don't expect anything before h2 2.0 (yes, major version jump!) is released, which does not yet have an ETA at all: https://groups.google.com/d/msg/h2-database/aoGRFlSX2hU/1N5xCaUuBQAJ

Sanne commented 4 years ago

Many thanks for all your help @famod !

Felk commented 2 years ago

Any news on this? Judging from https://github.com/hibernate/hibernate-orm/pull/3412#issuecomment-852180192 hibernate has updated to H2 version 2.x by now. Is anything blocking an upgrade to H2 2.x for Quarkus?

gsmet commented 2 years ago

No I think we should probably do it. It's a breaking change on the H2 side but people will have to live with it I suppose.

gsmet commented 2 years ago

See https://github.com/quarkusio/quarkus/pull/24663

Felk commented 2 years ago

I tried using Quarkus 2.9.0.CR1 and had to work around hibernate generating the limit keyword into queries even though I am using MODE=Oracle (causing sql grammar exceptions), as described here: https://groups.google.com/g/h2-database/c/yxnv64Ak-u8

The workaround is adding this code somewhere at startup:

org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
mode.limit = true;

Would it be a good idea to include this workaround in quarkus itself, until a hibernate version with this particular issue fixed is used?

Sanne commented 2 years ago

If you use H2, Hibernate ORM will assume it needs to use the H2 dialect - it doesn't know about any dialect changes you might apply to it. Would it work fine if you forced the Hibernate Dialect to use the Oracle dialect?

Generally speaking, would you mind explaining why you do this? I'm assuming you want to emulate an Oracle database for testing / development purposes, but in that case why not use the Oracle database container? We made it super easy to start one automatically, the only requirement is to have access to a container engine such as docker or podman.

Felk commented 2 years ago

Would it work fine if you forced the Hibernate Dialect to use the Oracle dialect?

quarkus.hibernate-orm.dialect=org.hibernate.dialect.Oracle12cDialect

That seems like that fixes this particular issue. But it causes a different problem:

Feature not supported: "VIEW"; SQL statement:
select * from ( select t.ID as id, t.BUSINESS_KEY as business_key from MY_TABLE t where t.BUSINESS_KEY=? ) where rownum <= ? for update [50100-197]

I don't understand how that makes sense (the table being queried is not a view), but I'll assume it is an unrelated issue for now. I can work around it by replacing all panache .singleResultOptional() with .stream().findFirst() for now.

I'm assuming you want to emulate an Oracle database for testing / development purposes

Exactly

why not use the Oracle database container? We made it super easy to start one automatically, the only requirement is to have access to a container engine such as docker or podman.

We do sometimes, and it indeed works great. However we still need H2, mostly because:

H2 starts way faster. dev mode helps a lot, but a full restart is required often enough for the 40s boot time of the oracle docker image to be an annoyance
not everyone in our team has or wants to use docker (not really a good reason but it is what it is)
for CI/CD on our gitlab runner we are blocked by https://github.com/gvenzl/oci-oracle-xe/issues/64

Sanne commented 2 years ago

Would it work fine if you forced the Hibernate Dialect to use the Oracle dialect?
quarkus.hibernate-orm.dialect=org.hibernate.dialect.Oracle12cDialect
That seems like that fixes this particular issue. But it causes a different problem:
Feature not supported: "VIEW"; SQL statement:
select * from ( select t.ID as id, t.BUSINESS_KEY as business_key from MY_TABLE t where t.BUSINESS_KEY=? ) where rownum <= ? for update [50100-197]
I don't understand how that makes sense (the table being queried is not a view), but I'll assume it is an unrelated issue for now.

It's because the H2 ability to emulate Oracle is not complete. And since I doubt it will ever be you'll always be in some undefined limbo - which is why:

I wouldn't recommend this approach
I'm not inclined to support this "better" in Hibernate ORM as it clearly is a pandora box

sorry :/ don't really have a good solution, but I hope this could be useful to know.

I'm assuming you want to emulate an Oracle database for testing / development purposes

Exactly

why not use the Oracle database container? We made it super easy to start one automatically, the only requirement is to have access to a container engine such as docker or podman.

We do sometimes, and it indeed works great. However we still need H2, mostly because:

H2 starts way faster. dev mode helps a lot, but a full restart is required often enough for the 40s boot time of the oracle docker image to be an annoyance

not everyone in our team has or wants to use docker (not really a good reason but it is what it is)

for CI/CD on our gitlab runner we are blocked by ORA-00821: Specified value of sga_target 1184M is too small, needs to be at least 1344M gvenzl/oci-oracle-xe#64

ah, I think I know that one :-D

Can't you keep an Oracle container running? In fact Testcontainers has some features (Ryuk) in which it should do this for you; they're also having a cloud offering, re-directing traffic from your developer's machine to the actual database instance running somewhere else - all transparently.

But also, if you figure out which parameters of docker (podman) will have the oracle instance boot successfully, I'm happy to adjust our integration to match.

Today I (personally) run it with:

podman run --memory-swappiness=0 --memory="4g" --cpus="4.0" --rm=true --name=HibernateTestingOracle -p 1521:1521 -e ORACLE_PASSWORD=hibernate_orm_test gvenzl/oracle-xe:21.3.0-slim

Could you try adjustment on your 100 core machine? Also, could use such scripts for your developers to avoid restarting.

famod commented 2 years ago

@Felk I feel your pain. I've been using h2 in tests for many years, even way before using Quarkus, in Oracle, Postgres or MariaDB/MySQL compatibility mode, mainly because of that speed aspect.

We even ended up buliding our own hibernate dialect classes to compensate the gaps of those compatibiliy modes. At some point I've had enough of all the effort that went into finding workarounds (e.g. liquibase changeset x works on real db, but not on h2) and so we ditched h2.

I know this won't solve points two and three, but I recommend having a look at "reusable" testcontainers. This did the trick for our tests and it's more or less on par with h2 in terms of speed (to be fair, liquibase does play a role here as well).

Sanne commented 2 years ago

I just noticed our containers started by DevServices aren't reusable, working on a fix.. #25365

Felk commented 2 years ago

thanks @Sanne and @famod, you made a few solid points regarding H2, and we are going to move away from it somewhat soon. With reusable testcontainers, https://github.com/gvenzl/oci-oracle-xe/pull/107 merged and a bit of convincing work within the team, that should be pretty doable.

quarkusio / quarkus

Update h2 database #9354