Tags · arnotixe/keycloak

nightly

Fix pagination issue with H6

With Hibernate ORM 6, pagination started to be unreliable: When
setting the max results only if the first row was 0 has randomly
affected other threads where first row was greater than 0. The
latter thread sometimes produced query which did *not* account
for the offset (cf. threads `-t1` and `-t2` below, while `-t2`
missed the `offset ? rows` part whic `-t3` has).

This has been fixed by setting the first row offset unconditionally.

Closes: keycloak#20202
Closes: keycloak#16570

```
2023-06-02 10:19:03.855000 TRACE [org.keycloak.models.sessions.infinispan.initializer.SessionInitializerWorker] (blocking-thread-node-2-p8-t1) Running computation for segment 0 with worker 0
2023-06-02 10:19:03.856000 TRACE [org.keycloak.models.sessions.infinispan.initializer.OfflinePersistentUserSessionLoader] (blocking-thread-node-2-p8-t1) Loading sessions for segment=0 lastSessionId=00000000-0000-0000-0000-000000000000 first=0
2023-06-02 10:19:03.856000 DEBUG [org.keycloak.models.jpa.PaginationUtils] (blocking-thread-node-2-p8-t1) Set max to 64 in org.hibernate.query.sqm.internal.QuerySqmImpl@2fb60f8b
2023-06-02 10:19:03.856000 DEBUG [org.keycloak.models.jpa.PaginationUtils] (blocking-thread-node-2-p8-t1) After pagination: 0, 64
2023-06-02 10:19:03.857000 TRACE [org.keycloak.models.sessions.infinispan.initializer.SessionInitializerWorker] (blocking-thread-node-2-p8-t2) Running computation for segment 1 with worker 1
2023-06-02 10:19:03.857000 TRACE [org.keycloak.models.sessions.infinispan.initializer.OfflinePersistentUserSessionLoader] (blocking-thread-node-2-p8-t2) Loading sessions for segment=1 lastSessionId=00000000-0000-0000-0000-000000000000 first=64
2023-06-02 10:19:03.857000 TRACE [org.keycloak.models.sessions.infinispan.initializer.SessionInitializerWorker] (blocking-thread-node-2-p8-t3) Running computation for segment 2 with worker 2
2023-06-02 10:19:03.857000 DEBUG [org.keycloak.models.jpa.PaginationUtils] (blocking-thread-node-2-p8-t2) Set first to 64 in org.hibernate.query.sqm.internal.QuerySqmImpl@71464e9f
2023-06-02 10:19:03.857000 DEBUG [org.keycloak.models.jpa.PaginationUtils] (blocking-thread-node-2-p8-t2) Set max to 64 in org.hibernate.query.sqm.internal.QuerySqmImpl@71464e9f
2023-06-02 10:19:03.857000 DEBUG [org.keycloak.models.jpa.PaginationUtils] (blocking-thread-node-2-p8-t2) After pagination: 64, 64
2023-06-02 10:19:03.857000 TRACE [org.keycloak.models.sessions.infinispan.initializer.OfflinePersistentUserSessionLoader] (blocking-thread-node-2-p8-t3) Loading sessions for segment=2 lastSessionId=00000000-0000-0000-0000-000000000000 first=128
10:19:03,859 DEBUG [org.hibernate.SQL] (blocking-thread-node-2-p8-t1)
    select
        p1_0.OFFLINE_FLAG,
        p1_0.USER_SESSION_ID,
        p1_0.CREATED_ON,
        p1_0.DATA,
        p1_0.LAST_SESSION_REFRESH,
        p1_0.REALM_ID,
        p1_0.USER_ID
    from
        OFFLINE_USER_SESSION p1_0,
        REALM r1_0
    where
        r1_0.ID=p1_0.REALM_ID
        and p1_0.OFFLINE_FLAG=?
        and p1_0.USER_SESSION_ID>?
    order by
        p1_0.USER_SESSION_ID fetch first ? rows only
10:19:03,859 DEBUG [org.hibernate.SQL] (blocking-thread-node-2-p8-t2)
    select
        p1_0.OFFLINE_FLAG,
        p1_0.USER_SESSION_ID,
        p1_0.CREATED_ON,
        p1_0.DATA,
        p1_0.LAST_SESSION_REFRESH,
        p1_0.REALM_ID,
        p1_0.USER_ID
    from
        OFFLINE_USER_SESSION p1_0,
        REALM r1_0
    where
        r1_0.ID=p1_0.REALM_ID
        and p1_0.OFFLINE_FLAG=?
        and p1_0.USER_SESSION_ID>?
    order by
        p1_0.USER_SESSION_ID fetch first ? rows only
2023-06-02 10:19:03.860000 TRACE [org.hibernate.orm.jdbc.bind] (blocking-thread-node-2-p8-t1) binding parameter [1] as [VARCHAR] - [1]
2023-06-02 10:19:03.860000 TRACE [org.hibernate.orm.jdbc.bind] (blocking-thread-node-2-p8-t1) binding parameter [2] as [VARCHAR] - [00000000-0000-0000-0000-000000000000]
2023-06-02 10:19:03.860000 TRACE [org.hibernate.orm.jdbc.bind] (blocking-thread-node-2-p8-t1) binding parameter [3] as [INTEGER] - [64]
10:19:03,860 DEBUG [org.hibernate.SQL] (blocking-thread-node-2-p8-t3)
    select
        p1_0.OFFLINE_FLAG,
        p1_0.USER_SESSION_ID,
        p1_0.CREATED_ON,
        p1_0.DATA,
        p1_0.LAST_SESSION_REFRESH,
        p1_0.REALM_ID,
        p1_0.USER_ID
    from
        OFFLINE_USER_SESSION p1_0,
        REALM r1_0
    where
        r1_0.ID=p1_0.REALM_ID
        and p1_0.OFFLINE_FLAG=?
        and p1_0.USER_SESSION_ID>?
    order by
        p1_0.USER_SESSION_ID offset ? rows fetch first ? rows only
2023-06-02 10:19:03.861000 TRACE [org.hibernate.orm.jdbc.bind] (blocking-thread-node-2-p8-t3) binding parameter [3] as [INTEGER] - [128]
2023-06-02 10:19:03.861000 TRACE [org.hibernate.orm.jdbc.bind] (blocking-thread-node-2-p8-t3) binding parameter [4] as [INTEGER] - [64]
```

Co-authored-by: mkanis <mkanis@redhat.com>