We are currently migrating from Keycloak version 22.0.5 to version 24.0.5. However, during the migration process, we encounter the following error:
Error details:: org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint “constraint_4a”
The migration process attempts to insert data into the realm table, but it fails with a duplicate key error. We have reviewed the JPA changelog files for versions 23 and 24, but there don’t appear to be any changes related to the realm table.
This behaviour is quite unexpected, and we’re unsure why the migration process is trying this insert operation, or why it is failing if it is a necessary step of migration.
Logs:
2024-09-24 04:54:19,067 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) SQL Error: 0, SQLState: 23505
2024-09-24 04:54:19,067 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) ERROR: duplicate key value violates unique constraint "constraint_4a"
Detail: Key (id)=(D6HJ5fghBFDre8i8) already exists.
2024-09-24 04:54:19,074 WARN [io.agroal.pool] (main) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)
2024-09-24 04:54:19,133 INFO [org.infinispan.CLUSTER] (main) ISPN000080: Disconnecting JGroups channel `ISPN`
2024-09-24 04:54:19,189 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2024-09-24 04:54:19,189 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) Error details:: org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint "constraint_4a"
Detail: Key (id)=(D6HJ5fghBFDre8i8) already exists.] [insert into public.REALM (ACCESS_CODE_LIFESPAN,LOGIN_LIFESPAN,USER_ACTION_LIFESPAN,ACCESS_TOKEN_LIFESPAN,ACCESS_TOKEN_LIFE_IMPLICIT,ACCOUNT_THEME,ADMIN_EVENTS_DETAILS_ENABLED,ADMIN_EVENTS_ENABLED,ADMIN_THEME,ALLOW_USER_MANAGED_ACCESS,BROWSER_FLOW,CLIENT_AUTH_FLOW,DEFAULT_LOCALE,DEFAULT_ROLE,DIRECT_GRANT_FLOW,DOCKER_AUTH_FLOW,DUPLICATE_EMAILS_ALLOWED,EDIT_USERNAME_ALLOWED,EMAIL_THEME,ENABLED,EVENTS_ENABLED,EVENTS_EXPIRATION,INTERNATIONALIZATION_ENABLED,LOGIN_THEME,LOGIN_WITH_EMAIL_ALLOWED,MASTER_ADMIN_CLIENT,NAME,NOT_BEFORE,OFFLINE_SESSION_IDLE_TIMEOUT,OTP_POLICY_ALG,OTP_POLICY_DIGITS,OTP_POLICY_COUNTER,OTP_POLICY_WINDOW,OTP_POLICY_PERIOD,OTP_POLICY_TYPE,PASSWORD_POLICY,REFRESH_TOKEN_MAX_REUSE,REGISTRATION_ALLOWED,REG_EMAIL_AS_USERNAME,REGISTRATION_FLOW,REMEMBER_ME,RESET_CREDENTIALS_FLOW,RESET_PASSWORD_ALLOWED,REVOKE_REFRESH_TOKEN,SSL_REQUIRED,SSO_IDLE_TIMEOUT,SSO_IDLE_TIMEOUT_REMEMBER_ME,SSO_MAX_LIFESPAN,SSO_MAX_LIFESPAN_REMEMBER_ME,VERIFY_EMAIL,ID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:97)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:108)
at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:40)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:52)
at org.hibernate.persister.entity.mutation.InsertCoordinator.doStaticInserts(InsertCoordinator.java:175)
at org.hibernate.persister.entity.mutation.InsertCoordinator.coordinateInsert(InsertCoordinator.java:113)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2873)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:363)
at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:61)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1366)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$2(ConcreteSqmSelectQueryPlan.java:136)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:362)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:303)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:509)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
at org.hibernate.query.Query.getResultList(Query.java:120)
at org.keycloak.models.jpa.JpaRealmProvider.getRealmByName(JpaRealmProvider.java:157)
at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealmByName(RealmCacheSession.java:465)
at org.keycloak.models.jpa.RealmAdapter.getMasterAdminClient(RealmAdapter.java:1182)
at org.keycloak.models.cache.infinispan.entities.CachedRealm.<init>(CachedRealm.java:269)
at org.keycloak.models.cache.infinispan.RealmCacheSession.prepareCachedRealm(RealmCacheSession.java:439)
at org.keycloak.models.cache.infinispan.RealmCacheManager.computeSerialized(RealmCacheManager.java:142)
at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealm(RealmCacheSession.java:421)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133)
at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1845)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
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:596)
at org.keycloak.utils.ClosingStream.forEach(ClosingStream.java:128)
at org.keycloak.migration.migrators.MigrateTo24_0_0.migrate(MigrateTo24_0_0.java:45)
at org.keycloak.storage.datastore.DefaultMigrationManager.migrate(DefaultMigrationManager.java:143)
at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:33)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.migrateModel(QuarkusJpaConnectionProviderFactory.java:213)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.initSchema(QuarkusJpaConnectionProviderFactory.java:207)
at org.keycloak.models.utils.KeycloakModelUtils.lambda$runJobInTransaction$1(KeycloakModelUtils.java:257)
at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransactionWithResult(KeycloakModelUtils.java:379)
at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:256)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.postInit(QuarkusJpaConnectionProviderFactory.java:132)
at org.keycloak.quarkus.runtime.integration.QuarkusKeycloakSessionFactory.init(QuarkusKeycloakSessionFactory.java:105)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.createSessionFactory(QuarkusKeycloakApplication.java:67)
at org.keycloak.services.resources.KeycloakApplication.startup(KeycloakApplication.java:131)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.onStartupEvent(QuarkusKeycloakApplication.java:54)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication_Observer_onStartupEvent_GNZ8m5QenZ9h9VNelo7awjUZFDE.notify(Unknown Source)
at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:346)
at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:328)
at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:82)
at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:155)
at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:106)
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:111)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:108)
at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:33)
at picocli.CommandLine.executeUserObject(CommandLine.java:2026)
at picocli.CommandLine.access$1500(CommandLine.java:148)
at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
at picocli.CommandLine.execute(CommandLine.java:2170)
at org.keycloak.quarkus.runtime.cli.Picocli.parseAndRun(Picocli.java:125)
at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:98)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:62)
at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:33)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "constraint_4a"
Detail: Key (id)=(D6HJ5fghBFDre8i8) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155)
at jdk.internal.reflect.GeneratedMethodAccessor232.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:447)
at jdk.proxy2/jdk.proxy2.$Proxy79.executeUpdate(Unknown Source)
at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:90)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194)
Has anyone else experienced this issue when upgrading to Keycloak 24 or any other version? Any insights or suggestions on how to resolve this would be greatly appreciated!