Hi,

During LDAP integration with Keycloak (v1.3.1), we get to see a “Unique index or primary key violation” exception while trying to login with an LDAP using on Keycloak’s account service site. I setup latest Keycloak source (from Github) to debug this issue. During build, I saw the same error when LDAP integration tests were running. Here are the logs –

21:40:24,624 INFO [org.keycloak.testsuite.KeycloakServer] Imported realm test

21:40:24,709 INFO [org.keycloak.federation.ldap.LDAPIdentityStoreRegistry] Creating new LDAP based partition manager for the Federation provider: test-ldap, LDAP Configuration: {bindDn=uid=admin,ou=system, userObjectClasses=null, baseDn=dc=keycloak,dc=org, usersDn=ou=People,dc=keycloak,dc=org, vendor=other, kerberosRealm=KEYCLOAK.ORG, syncRegistrations=false, userAccountControlsAfterPasswordUpdate=false, debug=true, connectionPooling=true, serverPrincipal=HTTP/localhost@KEYCLOAK.ORG, usernameLDAPAttribute=null, allowKerberosAuthentication=false, useKerberosForPasswordAuthentication=false, rdnLDAPAttribute=null, keyTab=/home/USER/apps/keycloak/testsuite/integration/target/test-classes/kerberos/http.keytab, batchSizeForSync=3, connectionUrl=ldap://localhost:10389, allowPasswordAuthentication=true, editMode=WRITABLE, updateProfileFirstLogin=true, pagination=true}

21:40:25,790 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users from LDAP to local store: realm: test, federation provider: test-ldap

21:40:25,845 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users finished: 5 imported users, 0 updated users, 0 removed users

21:40:26,862 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync changed users from LDAP to local store: realm: test, federation provider: test-ldap, last sync time: Wed Jul 08 21:40:25 IST 2015

21:40:26,900 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync changed users finished: 1 imported users, 1 updated users, 0 removed users

21:40:26,920 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users from LDAP to local store: realm: test, federation provider: test-ldap

21:40:26,962 WARN [org.keycloak.federation.ldap.LDAPFederationProviderFactory] User 'user7' is not updated during sync as he already exists in Keycloak database but is not linked to federation provider 'test-ldap'

21:40:26,969 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users finished: 0 imported users, 6 updated users, 0 removed users, 1 users failed sync! See server log for more details

21:40:26,981 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users from LDAP to local store: realm: test, federation provider: test-ldap

21:40:27,054 ERROR [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Failed during import user from LDAP

org.keycloak.models.ModelDuplicateException: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Unique index or primary key violation: "UK_DYKN684SL8UP1CRFEI6ECKHD7_INDEX_D ON PUBLIC.USER_ENTITY(REALM_ID, EMAIL_CONSTRAINT) VALUES ('test', 'user7@email.org', 21)"; SQL statement:

update USER_ENTITY set CREATED_TIMESTAMP=?, EMAIL=?, EMAIL_CONSTRAINT=?, EMAIL_VERIFIED=?, ENABLED=?, federation_link=?, FIRST_NAME=?, LAST_NAME=?, REALM_ID=?, TOTP=?, USERNAME=? where ID=? [23505-187]

at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert(PersistenceExceptionConverter.java:40)

at org.keycloak.connections.jpa.JpaKeycloakTransaction.commit(JpaKeycloakTransaction.java:30)

at org.keycloak.services.DefaultKeycloakTransactionManager.commit(DefaultKeycloakTransactionManager.java:58)

at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:247)

at org.keycloak.federation.ldap.LDAPFederationProviderFactory.importLdapUsers(LDAPFederationProviderFactory.java:286)

at org.keycloak.federation.ldap.LDAPFederationProviderFactory.syncImpl(LDAPFederationProviderFactory.java:241)

at org.keycloak.federation.ldap.LDAPFederationProviderFactory.syncAllUsers(LDAPFederationProviderFactory.java:200)

at org.keycloak.services.managers.UsersSyncManager.syncAllUsers(UsersSyncManager.java:50)

at org.keycloak.testsuite.federation.SyncProvidersTest.test02duplicateUsernameSync(SyncProvidersTest.java:200)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)

at org.junit.rules.RunRules.evaluate(RunRules.java:20)

at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:264)

at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)

at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:124)

at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:200)

at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:153)

at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:103)

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Unique index or primary key violation: "UK_DYKN684SL8UP1CRFEI6ECKHD7_INDEX_D ON PUBLIC.USER_ENTITY(REALM_ID, EMAIL_CONSTRAINT) VALUES ('test', 'user7@email.org', 21)"; SQL statement:

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361)

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289)

at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:78)

at org.keycloak.connections.jpa.JpaKeycloakTransaction.commit(JpaKeycloakTransaction.java:28)

... 33 more

Caused by: org.hibernate.exception.ConstraintViolationException: Unique index or primary key violation: "UK_DYKN684SL8UP1CRFEI6ECKHD7_INDEX_D ON PUBLIC.USER_ENTITY(REALM_ID, EMAIL_CONSTRAINT) VALUES ('test', 'user7@email.org', 21)"; SQL statement:

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:128)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)

at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)

at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)

at com.sun.proxy.$Proxy54.executeUpdate(Unknown Source)

at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:56)

at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3006)

at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2908)

at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3237)

at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:113)

at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:272)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:264)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:187)

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326)

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)

at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1081)

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:315)

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)

at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:73)

... 34 more

Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "UK_DYKN684SL8UP1CRFEI6ECKHD7_INDEX_D ON PUBLIC.USER_ENTITY(REALM_ID, EMAIL_CONSTRAINT) VALUES ('test', 'user7@email.org', 21)"; SQL statement:

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.get(DbException.java:155)

at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:102)

at org.h2.mvstore.db.MVSecondaryIndex.checkUnique(MVSecondaryIndex.java:233)

at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:191)

at org.h2.mvstore.db.MVTable.addRow(MVTable.java:638)

at org.h2.table.Table.updateRows(Table.java:478)

at org.h2.command.dml.Update.update(Update.java:145)

at org.h2.command.CommandContainer.update(CommandContainer.java:78)

at org.h2.command.Command.executeUpdate(Command.java:254)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)

at sun.reflect.GeneratedMethodAccessor261.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)

... 51 more

21:40:27,103 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users finished: 1 imported users, 6 updated users, 0 removed users, 1 users failed sync! See server log for more details

21:40:27,110 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users from LDAP to local store: realm: test, federation provider: test-ldap

21:40:27,167 INFO [org.keycloak.federation.ldap.LDAPFederationProviderFactory] Sync all users finished: 1 imported users, 6 updated users, 0 removed users

21:40:28,175 INFO [org.keycloak.testsuite.DummyUserFederationProviderFactory] syncChangedUsers invoked

Is this a known issue?

-- Rajat