keycloak: Keycloak Quarkus - Cannot use external datasource with a table already defined in Keycloak database

Describe the bug

I try to migrate a custom UserStorageProvider to the new Quarkus distribution (version 18.0.0) but I ran into an issue never seen with the Wildfly distribution.

This provider synchronizes some user information into a User table located in an external MariaDB database. I configured the datasource to the external database by adding required information in a quarkus.properties file, as it is now supported since https://github.com/keycloak/keycloak/pull/10581

Unfortunately, the external database also has a table called “Realm” which is used when we need to retrieve the user in that database.

After building the application with ./kc.sh build I encountered an error during startup:

2022-06-08 11:15:11,627 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) SQL Error: 1146, SQLState: 42S02
2022-06-08 11:15:11,627 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) (conn=132) Table 'keycloak.Realm' doesn't exist
2022-06-08 11:15:11,628 INFO  [org.hibernate.event.internal.DefaultLoadEventListener] (main) HHH000327: Error performing load command: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:390)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:163)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:104)
	at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:285)
	at org.hibernate.persister.entity.AbstractEntityPersister.doLoad(AbstractEntityPersister.java:4521)
	at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4511)
	at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:571)
	at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:539)
	at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:208)
	at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:327)
	at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:108)
	at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:74)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:118)
	at org.hibernate.internal.SessionImpl.fireLoadNoChecks(SessionImpl.java:1226)
	at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1215)
	at org.hibernate.internal.SessionImpl.access$2100(SessionImpl.java:201)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2830)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.lambda$load$1(SessionImpl.java:2807)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.perform(SessionImpl.java:2763)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2807)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3395)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3357)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:60)
	at com.sun.proxy.$Proxy107.find(Unknown Source)
	at org.keycloak.models.jpa.JpaRealmProvider.getRealm(JpaRealmProvider.java:124)
	at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealm(RealmCacheSession.java:411)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
	at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133)
	at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:497)
	at org.hibernate.query.spi.StreamDecorator.forEach(StreamDecorator.java:153)
	at org.keycloak.utils.ClosingStream.forEach(ClosingStream.java:128)
	at org.keycloak.services.managers.UserStorageSyncManager$1.run(UserStorageSyncManager.java:63)
	at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:242)
	at org.keycloak.services.managers.UserStorageSyncManager.bootstrapPeriodic(UserStorageSyncManager.java:58)
	at org.keycloak.services.resources.KeycloakApplication.setupScheduledTasks(KeycloakApplication.java:248)
	at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.startup(QuarkusKeycloakApplication.java:50)
	at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver.onStartupEvent(QuarkusLifecycleObserver.java:37)
	at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver_Observer_onStartupEvent_b0e82415b143738dc1f986a5fa4668e83d0a5dea.notify(Unknown Source)
	at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:320)
	at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:302)
	at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:73)
	at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:128)
	at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:97)
	at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
	at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
	at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
	at io.quarkus.runtime.Application.start(Application.java:101)
	at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:103)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:67)
	at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:86)
	at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:34)
	at picocli.CommandLine.executeUserObject(CommandLine.java:1939)
	at picocli.CommandLine.access$1300(CommandLine.java:145)
	at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2358)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2352)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2314)
	at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
	at picocli.CommandLine$RunLast.execute(CommandLine.java:2316)
	at picocli.CommandLine.execute(CommandLine.java:2078)
	at org.keycloak.quarkus.runtime.cli.Picocli.parseAndRun(Picocli.java:88)
	at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:77)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:60)
	at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:31)
Caused by: java.sql.SQLSyntaxErrorException: (conn=132) Table 'keycloak.Realm' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:158)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:266)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
	at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:163)
	at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
	... 77 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'keycloak.Realm' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
	... 81 more
Caused by: java.sql.SQLException: Table 'keycloak.Realm' doesn't exist
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1695)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1557)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
	... 82 more

2022-06-08 11:56:01,283 WARN  [io.agroal.pool] (main) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (development) mode
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: could not extract ResultSet
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: (conn=171) Table 'keycloak.Realm' doesn't exist
2022-06-08 11:56:01,427 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Table 'keycloak.Realm' doesn't exist
2022-06-08 11:56:01,428 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Table 'keycloak.Realm' doesn't exist
2022-06-08 11:56:01,428 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the details about the usage of the particular command.

It seems that Quarkus mixes tables from the two databases when Hibernate loads during startup and the “Realm” table overrides the default one (“REALM”). The strange thing is that sometimes no error appears during bootstrap, maybe because Keycloak entities classes were loaded first?

After a lot of investigations, I found that this error is not related to the UserStorageProvider implementation but is caused by the presence of a Realm class into the persistence.xml file.

I made a really simple demo with only a persistence.xml file embedded in a jar, with only a Realm class. No UserStorageProvider is needed to reproduce this error.

Version

18.0.0

Expected behavior

Using an external database with a table already defined in Keycloak does not throw an exception at startup, like in the Wildfly distribution.

Actual behavior

Sometimes server does not start because the table Realm cannot be found by Hibernate.

How to Reproduce?

Update keycloak.conf as follow:

db=mariadb
db-username=keycloak
db-password=keycloak
db-url=jdbc:mariadb://127.0.0.1:3306/keycloak

Add a quarkus.properties file with:

quarkus.datasource.user-store.db-kind=mariadb
quarkus.datasource.user-store.username=sa
quarkus.datasource.user-store.password=sa
quarkus.datasource.user-store.jdbc.url=jdbc:mariadb://127.0.0.1:3306/external_db

Create two empty databases “keycloak” and “external_db” in your MariaDB instance and also the corresponding users.

Copy the jar from the tar.gz archive attached into the providers directory and launch Keycloak with command: ./kc.sh build && ./kc.sh start-dev --transaction-xa-enabled=false

Note : it may require multiple launches to display the error.

user-storage-jpa-realm.tar.gz

Anything else?

No response