Hibernate Reactive Panache MySQL

[douglasss]

Describe the bug

After Calling find() and count() on a table few times with ~10rows I am getting timeout:
I believe the connections are not being closed after the calls.

2023-08-25 11:28:34,464 ERROR [com.tha.ota.exc.ThrowableMapper] (vert.x-eventloop-thread-3) Timeout: io.vertx.core.impl.NoStackTraceThrowable: Timeout

public static Uni<PageResult<Table>> findPage(Set<String> param1,
                                                       Param2 param2,
                                                       ...,
                                                       Page page,
                                                       Sort sort) {

        Set<String> whereClauses = new HashSet<>();
        Map<String, Object> params = new HashMap<>();
        
        ... I Build the where clause here

        Uni<Long> count = countBy(whereClause, params);

        Uni<List<Table>> content = findAllBy(whereClause, params, page, sort);

        return Uni.combine().all().unis(count, content)
                .asTuple()
                .map(tuple -> new PageResult<>(page.index, page.size, tuple.getItem1(), tuple.getItem2()));
    }

    private static Uni<Long> countBy(String whereClause, Map<String, Object> params) {
        return Table.count(
                "from Table t join t.table2 t2 join t2.table3 t3" + whereClause,
                params);
    }

    public static Uni<List<Table>> findAllBy(String whereClause, Map<String, Object> params, Page page, Sort sort) {
        String query = "from Table t join fetch t.table2 t2 join fetch t2.table3 t3" + whereClause;
        return Table.<Table>find(
                        query,
                        sort,
                        params
                )
                .page(page)
                .list();
    }

Expected behavior

Not getting I timeout exception

Actual behavior

No response

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

OpenJDK Runtime Environment Corretto-17.0.7.7.1 (build 17.0.7+7-LTS)

GraalVM version (if different from Java)

No response

Quarkus version or git rev

3.3.0

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

Apache Maven 3.9.2

Additional information

No response

[quarkus-bot]

/cc @DavideD (hibernate-reactive), @FroMage (panache), @Sanne (hibernate-reactive), @gavinking (hibernate-reactive), @loicmathieu (panache)

[DavideD]

return Uni.combine().all().unis(count, content)

Hibernate Reactive doesn't support combine().all(). That's probably what's causing the TimeOut

[douglasss]

@DavideD Do you know another away to do that? I've tried something with flatMap but I got the same timout.

count.flatMap(countResult -> content.map(contentResult -> new PageResult<>(page.index, page.size, countResult, contentResult)));

[DavideD]

Mmh... that seems the right approach. If you still have the timeout, the problem might be somewhere else.

Could you provide a test project that we can run?

[douglasss]

@DavideD I create a small test project here (https://github.com/douglasss/panache-reactive-timeout)

If you run the tests with quarkus test, you can see that it repeats itself for 21 times.
At the 21th attempt we have the io.vertx.core.impl.NoStackTraceThrowable: Timeout

[DavideD]

Thanks @douglasss, that's great!
I will try to have a look at it today

[DavideD]

I tried the project and adding @WithSession seems to solve the issue:

        @Get
	@WithSession
	public Uni<PageResult<Product>> getAll() {
		return find();
	}

I was expecting some error when the annotation is missing though.
@mkouba, any insight?

[mkouba]

I was expecting some error when the annotation is missing though.
@mkouba, any insight?

@WithSessionOnDemand should be added automatically. Let me check the reproducer...

[mkouba]

TLDR> There is a bug in the implementation of @WithSessionOnDemand. @WithSession and @WithTransaction indeed work correctly.

The problem occurs if Panache entity methods are not "nested", for example like in the reproducer - count() and find() are called separately and then used to form a chain.

The following code should work just fine:

Uni<Long> count = Product
                .count("from Product p join p.secondTable join p.thirdTable where p.id > 0");
return count
                .flatMap(countResult -> Product
                        .<Product> find("from Product p join fetch p.secondTable join fetch p.thirdTable where p.id > 0",
                                Sort.ascending("p.id"))
                        .page(0, 10)
                        .list()
                        .map(contentResult -> new PageResult<>(countResult, contentResult)));

Because when the Product.find() is called the session is already stored in the duplicated Vert.x context.

If those calls are not nested then every operation (e.g. PanacheEntityBase.count()) opens a new reactive session, but only the last one is closed.

I'll try to send a PR with a fix shortly.

[douglasss]

@DavideD and @mkouba, Thanks for getting back so fast!
Both suggestions (WithSession and nested calls) worked perfectly on our project!