[keycloak-user] Persist Keycloak session cache into JDBC store, no data is written into table

[Nicolas Ocquidant]

Hi Cedric

I experimented the same... For me, the only way to get data in the jdbc
store is to enable passivation in Keycloak.
But then, set shared=false as passivation doesn't play well with shared
stores in Infinispan.

See http://lists.jboss.org/pipermail/keycloak-user/2018-November/016214.html

--nick

Le lun. 12 nov. 2018 à 13:30, Röck, Cedric <Cedric.Roeck at senacor.com> a
écrit :

> Hi,
>
> we are currently trying to persist the in-memory session cache of our
> Keycloak (9.5.0.Final) deployment into a persistent store, preferably JDBC
> based.
>
> In order to achieve this, we already updated the configuration and ended
> up with this config for the Infinispan subsystem:
>
> <subsystem xmlns="urn:jboss:domain:infinispan:6.0">
>     <cache-container name="keycloak">
>         <transport lock-timeout="60000"/>
>         <local-cache name="realms">
>             <object-memory size="10000"/>
>         </local-cache>
>         <local-cache name="users">
>             <object-memory size="10000"/>
>         </local-cache>
>         <local-cache name="authorization">
>             <object-memory size="10000"/>
>         </local-cache>
>         <local-cache name="keys">
>             <object-memory size="1000"/>
>             <expiration max-idle="3600000"/>
>         </local-cache>
>         <replicated-cache name="work"/>
>         <distributed-cache name="sessions" statistics-enabled="true"
> owners="${env.CACHE_OWNERS:1}">
>             <jdbc-store data-source="KeycloakDS" dialect="SQL_SERVER"
> fetch-state="true" passivation="false" preload="true" purge="false"
> shared="true" singleton="false">
>                 <property name="dropTableOnExit">
>                     false
>                 </property>
>                 <property name="createTableOnStart">
>                     true
>                 </property>
>                 <table/>
>             </jdbc-store>
>         </distributed-cache>
>         <distributed-cache name="clientSessions" statistics-enabled="true"
> owners="${env.CACHE_OWNERS:1}"/>
>         <distributed-cache name="authenticationSessions"
> statistics-enabled="true" owners="${env.CACHE_OWNERS:1}"/>
>         [...]
>     </cache-container>
>     [...]
> </subsystem>
>
> Even though the table „ispn_entry_sessions“ gets created once Keycloak
> starts, no data is being persisted there. Not after 5min and also not once
> several hours passed. To exclude batch sizes and alike as error cause, our
> test creates 300 users and performs repeated logins for all of them, so
> there should also be enough load on the system.
>
> Some more details:
>
>   *   The statistics already show more than 600 cache-loader-misses for
> the jdbc store, but no successful load.
>   *   Our deployment consists of three Keycloak instances running in
> Kubernetes pods / docker containers.
>   *   Target JDBC Database is an Azure managed SQL DB / SQL Server
>   *   We can’t see any errors in the logs and also the cache distribution
> appears to still work amongst all nodes in the cluster.
>
>
> If you need more details, log excerpts, the full config, …, just give me a
> ping.
>
> What are we missing? Any help is very much appreciated.
>
> Thanks and kind regards
> Cedric
>
> Cedric Röck
> ______________________________
> Senacor Technologies AG
> Äußere Cramer-Klett-Str. 21
> 90489 Nürnberg
>
> M +49 (170) 2274 878
>
> Cedric.Roeck at senacor.com
> www.senacor.com
>
>
> Senacor Technologies Aktiengesellschaft - Sitz: Eschborn - Amtsgericht
> Frankfurt am Main - Reg.-Nr.: HRB 110482
> Vorstand: Matthias Tomann, Marcus Purzer - Aufsichtsratsvorsitzender:
> Daniel Grözinger
>
> Diese E-Mail inklusive Anlagen enthält vertrauliche und/oder rechtlich
> geschützte Informationen. Wenn Sie
> nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten,
> informieren Sie bitte den  Absender
> und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die
> unbefugte Weitergabe dieser  E-Mail ist
> nicht gestattet.
>
> This e-mail including any attachments may contain confidential and/or
> privileged information. If you are
> not the intended recipient (or have received this e-mail in error) please
> notify the sender immediately and
> destroy this e-mail. Any unauthorized copying, disclosure or distribution
> of the materials in this e-mail is
> strictly forbidden.
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user