JIRA LDAP sync fails due to Too many rows found for query on User
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Problem
Synchronisation with LDAP is failing due to the below exception.
The following appears in the atlassian-jira.log:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2015-08-28 09:48:51,326 atlassian-scheduler-quartz1.clustered_Worker-4 ERROR ServiceRunner [com.atlassian.scheduler.JobRunnerResponse] Unable to synchronise directory
java.lang.IllegalStateException: Too many rows found for query on User
row1: [GenericEntity:User][firstName,Captain][lastName,Planet][emailAddress,captain-planet@example.com][createdDate,2015-04-29 21:10:35.537][displayName,Captain Planet][active,1][externalId,7274497:8402738095f98043b672a90b11e13e84][directoryId,10600][id,152444][updatedDate,2015-05-13 21:09:29.399][userName,captainplanet]row2:
[GenericEntity:User][firstName,Captain][lastName,Planet][emailAddress,captain.planet@example.com][createdDate,2015-04-30 09:09:26.867][displayName,Captain Planet][active,1][externalId,7274497:8402738095f98043b672a90b11e13e84][directoryId,10600][id,152453][updatedDate,2015-04-30 09:09:26.867][userName,captainplanet2]
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl$3.consume(SelectQueryImpl.java:210)
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.consumeWith(SelectQueryImpl.java:240)
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.singleValue(SelectQueryImpl.java:200)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.findByExternalId(OfBizUserDao.java:171)
at com.atlassian.crowd.directory.AbstractInternalDirectory.findUserByExternalId(AbstractInternalDirectory.java:181)
at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.userByExternalIdOrNull(DbCachingRemoteChangeOperations.java:293)
at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.updateUsers(DbCachingRemoteChangeOperations.java:233)
at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.addOrUpdateCachedUsers(DirectoryCacheImplUsingChangeOperations.java:62)
at com.atlassian.crowd.directory.ldap.cache.RemoteDirectoryCacheRefresher.synchroniseAllUsers(RemoteDirectoryCacheRefresher.java:95)
at com.atlassian.crowd.directory.ldap.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:89)
at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseAll(EventTokenChangedCacheRefresher.java:67)
at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1122)Diagnosis
Run the following SQL queries to detect the condition in the database which is causing the problem. This will show all users values which have duplicate rows, and a count of how many duplicates.
1
select lower_user_name, count(external_id) from cwd_user group by lower_user_name having count(external_id) > 1;Cause
When JIRA synchronises with Crowd it performs a lookup on the database, retrieving user records with the External ID. It's possible for this synchronisation to fail as multiple entries with the identical id can be returned (constraints in the database are on the lower user name, not the external ID). When this occurs the above exception is thrown and synchronisation will fail.
Solution
Workaround
Identifying the duplicated user and deleting them will workaround this problem - in this example deleting either the
captainplanetorcaptainplanet2user will fix the problem.Alternatively a new user directory can be added, replacing the original.
It is possible to delete the user from the
cwd_usertable however this can create orphaned records in the database - rather deleting the user through Managing Users is recommended.
Was this helpful?