Row with generated ID is not being created in DB2 in some configurations

[fedinskiy]

Describe the bug

When we use Hibernate Reactive entity with an id field annotated with @GenericGenerator together with DB2 database, then row with this id is not being created on some hardware+OS configurations, but is created on others. The same scenario always works successfully with Postgres, MS SQL, MySQL and MariaDB.

Known working configurations: Fedora 34 with GraalVM 17 Fedora 34 with Temurin 11 RHEL 8.4 with OpenJDK 17 and openJDK 11 Ubuntu 22.10 with Temurin 11

Known non-working configuration: Ubuntu 20.04 with Temurin 11 in GH actions

Expected behavior

The row should be created

Actual behavior

The row is not being created or, at least, not result is not seen on the next read.

How to Reproduce?

Reproducer code can be found here:

https://github.com/fedinskiy/quarkus-test-suite/blob/db_failure/sql-db/hibernate-reactive/src/test/java/io/quarkus/ts/reactive/DB2DatabaseIT.java test method testGeneratedId

The log of the failing result: https://github.com/quarkus-qe/quarkus-test-suite/runs/4223423337?check_suite_focus=true

Output of uname -a or ver

No response

Output of java -version

No response

GraalVM version (if different from Java)

No response

Quarkus version or git rev

2c1be38c1f7dcdb0176c871f2d142f5f2a4cd1f2

Build tool (ie. output of mvnw --version or gradlew --version)

No response

Additional information

No response

[quarkus-bot[bot]]

/cc @mswatosh

[DavideD]

Mmmh... when I run the test on my machine I can see the error but it happens because the insert query is not executed. I'm still trying to figure out what's happening

[DavideD]

I think the problem is that there is no transaction when doing the creation of the book:

    @PUT
    @Path("books/{author}/{name}")
    public Uni<Response> createBook(Integer author, String name) {
        return Panache.withTransaction( () -> Book.create( author, name ) )
                .map( nothing -> Response.status( Response.Status.CREATED ) )
                .onFailure().recoverWithItem( error -> Response.status( Response.Status.BAD_REQUEST )
                        .entity( error.getMessage() ) )
                .map( Response.ResponseBuilder::build );
    }

An example of panache for CRUD operations is available in the quickstarts.

[fedinskiy]

Thank you, I can confirm, that Panache.withTransaction does solve the problem, but I have a couple of questions:

1. Why does it affect only one method and only for single database?
2. According to the guide[1], this solution can be replaced with @Transactional annotation on createBook method. Is this correct? I am asking, since right now it makes things worse, so either I misinterpret the guide, or I need to create another issue in regards to annotation.

3. [1] https://quarkus.io/guides/hibernate-orm-panache#transactions

[DavideD]

On second though, Book.create is using persistAndFlush so I would have expected that to work even without the transaction. @FroMage, any idea? It would be interesting to see if this works without Panache and only using Hibernate Reactive.

I think @Transactional works with Hibernate ORM but not with Hibernate Reactive. I don't think there is something equivalent for Panache Reactive

[DavideD]

The same issues happens with both vanilla Hibernate Reactive and Panache. It might be a bug in something related to the db2 integration with Hibernate Reactive.

I will keep you up to date if I discover something

[DavideD]

Why does it affect only one method and only for single database?

Persisting with identity requires a different query so it might be related. Every db uses a different Vertx SQL client for the connection and it's possible there are differences in the way each databases behave in some situations. That said, Hibernate Reactive should already keep these differences into account already, so it shouldn't be a problem.

I've tried replacing the code using Hibernate Reactive and it seems to work except that it takes some time before the value is actually available in the database after the flush.

I couldn't make it work with panache though.

I've also created some tests for the Hibernate Reactive project and they seem to work fine with Db2. If somebody else has some other ideas, I'm listening.

@fedinskiy I've found some issues in the test suite and the way entities are mapped:

• Using ORM dependencies instead of the reactive ones: 3ccc55affa91054951385d696bb1a385b6bde333
• Mapping of the association Book-Author: 270df7e2bd1cbff8b235dfe49a041a82c4f9f844