Infinispan JDBC store causes UnsupportedKeyTypeException for UUID

Configuring the server
1

I’m currently trying to configure the cache JDBC store for Infinispan.
I’m running Keycloak in ECS and don’t want to lose all user sessions, etc. every time I’m deploying a new version of the Keycloak service.
Since Redis is not working in WildFly, I tried the JDBC store. With a bit of tinkering I got it set up and Keycloak even creates the database tables on start.

Whenever Keycloak pushes an entry into the cache, it throws an exception.
14:29:28,237 ERROR [org.infinispan.interceptors.impl.InvocationContextInterceptor] (default task-6) ISPN000136: Error executing command PutKeyValueCommand on Cache 'clientSessions', writing keys [7ae59e20-57b7-4a4f-85bc-dc609a96cb9c]: org.infinispan.persistence.keymappers.UnsupportedKeyTypeException: Unsupported key type: 'java.util.UUID' on key: 7ae59e20-57b7-4a4f-85bc-dc609a96cb9c

I’m using Keycloak 12.0.1 with MySQL 5.7 and the JDBC store is enabled by a script that contains these two lines for every distributed-cache entry:

/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc:add(data-source=KeycloakDS)
/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc/table=string:write-attribute(name=id-column, value={type="VARCHAR(200)"})

This happens with store=jdbc as well as store=binary-jdbc.

Am I missing something that I need to configure? Is this missing some marshaller? Do I need to create additional data-columns?
Also the default create statements for the JDBC store are not working with MySQL, is there some undocumented configuration to tell Infinispan what DB vendor it is talking to?

Keycloak seems to ignore cache property `purge` in configuration
2

I am facing the same problem, although I have used all kind of types using the following template for all type of the 6 kind of cache sessions (authenticationSessions, offlineSessions, clientSessions, offlineClientSessions, loginFailures, sessions).

/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc:add( \
  datasource="java:jboss/datasources/KeycloakDS", \
  dialect="MYSQL", \
  passivation=false, \
  fetch-state=true, \
  preload=false, \
  purge=false, \
  shared=true, \
  max-batch-size=1000 \
)
/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc:write-attribute( \
  name=properties.databaseType, \
  value=MYSQL \
)
/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc/table=string:add( \
  id-column={type=BINARY(16)}, \
  data-column={type=BLOB}, \
  drop-on-stop=false, \
  fetch-size=5000, \
  prefix=ispn \
)
/subsystem=infinispan/cache-container=keycloak/distributed-cache=clientSessions/store=jdbc/write=behind:add( \
  modification-queue-size=1024 \
)

It’s been imposible to set it up properly and there is no documentation. I’ve tried, varchar (36,255)… binary, varbinary, … I am really running out of ideas.

I can’t see in KC source code something significative, and infinispan library points to the following, which is implemented by the following classes:
org.infinispan.persistence.keymappers.WrappedByteArrayOrPrimitiveMapper
org.infinispan.persistence.keymappers.DefaultTwoWayKey2StringMapper

On the other hand, it seems the default handler is DefaultTwoWayKey as documented in
https://docs.jboss.org/infinispan/10.1/configdocs/infinispan-cachestore-jdbc-config-10.1.html

   private String key2Str(Object key) throws PersistenceException {
      if (!key2StringMapper.isSupportedType(key.getClass())) {
         throw new UnsupportedKeyTypeException(key);
      }
      String keyStr = key2StringMapper.getStringMapping(key);
      return tableManager.isStringEncodingRequired() ? tableManager.encodeString(keyStr) : keyStr;
   }

Additional information can be found at:
https://groups.google.com/g/jooq-user/c/syik9BMI8eI

Any ideas about how to solve this?
Thank you very much

3

BTW @x4121 you can see the script that solves your db-creation problem in my reply.