Can't switch to SQL DB - Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. #16777

BrianDWatkins · 2023-02-01T19:59:20Z

BrianDWatkins
Feb 1, 2023

I got my Keycloak instance all up and running, and am in the process of preparing it for production.
When I enabled the MSSQL database connection, it created all of the tables, but when it tries to run, it seems to be trying to re-add values that already exist in the tables.

The main error I'm seeing is Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'.

I have tried building with xa transactions false, and have also cleared out the table in question and it is re-built and the error persists.

Has anyone a solution for this?

C:\Program Files\keycloak-20.0.0\bin>kc.bat --verbose start --optimized --spi-theme-static-max-age=-1 --spi-theme-cache-themes=false --spi-theme-cache-templates=false --https-port=443 --https-protocols=TLSv1.2
2023-02-01 10:53:57,107 INFO  [org.keycloak.quarkus.runtime.hostname.DefaultHostnameProvider] (main) Hostname settings: Base URL: <unset>, Hostname: ##########, Strict HTTPS: true, Path: <request>, Strict BackChannel: false, Admin URL: <unset>, Admin: <request>, Port: -1, Proxied: false
2023-02-01 10:54:02,089 INFO  [org.keycloak.broker.provider.AbstractIdentityProviderMapper] (main) Registering class org.keycloak.broker.provider.mappersync.ConfigSyncEventListener
2023-02-01 10:54:02,376 WARN  [org.infinispan.PERSISTENCE] (keycloak-cache-init) ISPN000554: jboss-marshalling is deprecated and planned for removal
2023-02-01 10:54:02,433 WARN  [org.infinispan.CONFIG] (keycloak-cache-init) ISPN000569: Unable to persist Infinispan internal caches as no global state enabled
2023-02-01 10:54:02,481 INFO  [org.infinispan.CONTAINER] (keycloak-cache-init) ISPN000556: Starting user marshaller 'org.infinispan.jboss.marshalling.core.JBossUserMarshaller'
2023-02-01 10:54:03,069 INFO  [org.infinispan.CONTAINER] (keycloak-cache-init) ISPN000128: Infinispan version: Infinispan 'Triskaidekaphobia' 13.0.10.Final
2023-02-01 10:54:03,232 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000078: Starting JGroups channel `ISPN`
2023-02-01 10:54:03,236 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000088: Unable to use any JGroups configuration mechanisms provided in properties {}. Using default JGroups configuration!
2023-02-01 10:54:05,778 INFO  [org.jgroups.protocols.pbcast.GMS] (keycloak-cache-init): no members discovered after 2019 ms: creating cluster as coordinator
2023-02-01 10:54:05,779 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000094: Received new cluster view for channel ISPN: [|0] (1)
2023-02-01 10:54:05,793 INFO  [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000079: Channel `ISPN` local address is, physical addresses are 
2023-02-01 10:54:06,705 INFO  [org.keycloak.connections.infinispan.DefaultInfinispanConnectionProviderFactory] (main) Node name: , Site name: null
2023-02-01 10:54:11,072 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) SQL Error: 2627, SQLState: 23000
2023-02-01 10:54:11,077 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. Cannot insert duplicate key in object 'dbo.CLIENT_SCOPE'. The duplicate key value is (ad656d36-4ade-45f3-8b4e-519fb48adff7, role_list).
2023-02-01 10:54:11,090 WARN  [io.agroal.pool] (main) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)
2023-02-01 10:54:11,186 INFO  [org.infinispan.CLUSTER] (main) ISPN000080: Disconnecting JGroups channel `ISPN`
2023-02-01 10:54:11,287 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2023-02-01 10:54:11,287 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) Error details:: org.keycloak.models.ModelDuplicateException: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert(PersistenceExceptionConverter.java:80)
        at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:62)
        at jdk.proxy2/jdk.proxy2.$Proxy84.flush(Unknown Source)
        at org.keycloak.models.jpa.JpaRealmProvider.addClientScope(JpaRealmProvider.java:879)
        at org.keycloak.storage.ClientScopeStorageManager.addClientScope(ClientScopeStorageManager.java:64)
        at org.keycloak.models.ClientScopeProvider.addClientScope(ClientScopeProvider.java:45)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.addClientScope(RealmCacheSession.java:1290)
        at org.keycloak.models.jpa.RealmAdapter.addClientScope(RealmAdapter.java:1958)
        at org.keycloak.protocol.saml.SamlProtocolFactory.createDefaultClientScopesImpl(SamlProtocolFactory.java:114)
        at org.keycloak.protocol.AbstractLoginProtocolFactory.createDefaultClientScopes(AbstractLoginProtocolFactory.java:61)
        at org.keycloak.models.utils.DefaultClientScopes.lambda$createDefaultClientScopes$0(DefaultClientScopes.java:43)
        at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
        at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
        at java.base/java.util.HashMap$ValueSpliterator.forEachRemaining(HashMap.java:1787)
        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.models.utils.DefaultClientScopes.createDefaultClientScopes(DefaultClientScopes.java:43)
        at org.keycloak.migration.migrators.MigrateTo4_0_0.migrateRealm(MigrateTo4_0_0.java:74)
        at org.keycloak.migration.migrators.MigrateTo4_0_0.lambda$migrate$0(MigrateTo4_0_0.java:51)
        at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
        at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
        at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
        at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
        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:1921)
        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.hibernate.query.spi.StreamDecorator.forEach(StreamDecorator.java:153)
        at org.keycloak.utils.ClosingStream.forEach(ClosingStream.java:128)
        at org.keycloak.migration.migrators.MigrateTo4_0_0.migrate(MigrateTo4_0_0.java:51)
        at org.keycloak.storage.datastore.LegacyMigrationManager.migrate(LegacyMigrationManager.java:133)
        at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:33)
        at org.keycloak.quarkus.runtime.storage.legacy.database.LegacyJpaConnectionProviderFactory.migrateModel(LegacyJpaConnectionProviderFactory.java:219)
        at org.keycloak.quarkus.runtime.storage.legacy.database.LegacyJpaConnectionProviderFactory.initSchema(LegacyJpaConnectionProviderFactory.java:213)
        at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:260)
        at org.keycloak.quarkus.runtime.storage.legacy.database.LegacyJpaConnectionProviderFactory.postInit(LegacyJpaConnectionProviderFactory.java:138)
        at org.keycloak.quarkus.runtime.integration.QuarkusKeycloakSessionFactory.init(QuarkusKeycloakSessionFactory.java:104)
        at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.startup(QuarkusKeycloakApplication.java:42)
        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:326)
        at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:308)
        at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:76)
        at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:131)
        at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:100)
        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:110)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:69)
        at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:103)
        at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:37)
        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:93)
        at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:89)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
        at java.base/java.lang.reflect.Method.invoke(Method.java:578)
        at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:61)
        at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:32)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
        at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1411)
        at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1394)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
        at java.base/java.lang.reflect.Method.invoke(Method.java:578)
        at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:60)
        ... 74 more
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:109)
        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.executeUpdate(ResultSetReturnImpl.java:200)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3381)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3908)
        at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
        at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
        at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:729)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
        at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
        at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1407)
        ... 78 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. Cannot insert duplicate key in object 'dbo.CLIENT_SCOPE'. The duplicate key value is (ad656d36-4ade-45f3-8b4e-519fb48adff7, role_list).
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1673)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:620)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:540)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:486)
        at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:88)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
        ... 89 more

BrianDWatkins · 2023-02-24T00:10:18Z

BrianDWatkins
Feb 24, 2023
Author

I was able to resolve the issue by deleting all tables in the database and re-running the config with --transaction-xa-enabled=false

This fixed my current issue.

2 replies

codespearhead Oct 4, 2024

@BrianDWatkins Can you please mark the above comment as the accepted answer [1] for this discussion, so it no longer appears as unanswered in the GitHub discussions tab?

[1] https://docs.github.com/en/discussions/managing-discussions-for-your-community/moderating-discussions#marking-a-comment-as-an-answer

voquanghoa Dec 2, 2025

Thanks. It works on my side too

Navigation Menu

Repository navigation

Can't switch to SQL DB - Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. #16777

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Replies: 1 comment 2 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Can't switch to SQL DB - Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. #16777

Uh oh!

Uh oh!

BrianDWatkins Feb 1, 2023

Replies: 1 comment · 2 replies

Uh oh!

BrianDWatkins Feb 24, 2023 Author

Uh oh!

codespearhead Oct 4, 2024

Uh oh!

voquanghoa Dec 2, 2025

BrianDWatkins
Feb 1, 2023

Replies: 1 comment 2 replies

BrianDWatkins
Feb 24, 2023
Author