JIRA LDAP sync fails due to Too many rows found for query on ApplicationUser
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Symptoms
JIRA is failing to synchronise a directory with LDAP/Active Directory.
The following appears in the atlassian-jira.log:
2014-03-03 14:04:03,589 QuartzScheduler_Worker-2 ERROR ServiceRunner [atlassian.crowd.directory.DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 10300 ].
java.lang.IllegalStateException: Too many rows found for query on ApplicationUser
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl$2.consume(SelectQueryImpl.java:183)
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.consumeWith(SelectQueryImpl.java:212)
...Diagnosis
Run the following SQL queries to detect the condition in the database which is causing the problem:
select lower_user_name, count(lower_user_name) from app_user group by lower_user_name having count(lower_user_name) > 1; -- Show all lower_user_name values which have duplicate rows, and a countCause
There are duplicate lower_user_name values in the app_user table, causing the synchronisation to get multiple results when trying to link the user in the Embedded Crowd tables to a user_key. Ordinarily it shouldn't be possible for this to happen due to the unique indexes on this table, so this means that the schema for that table is not correct.
Resolution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
- Stop JIRA.
- Remove duplicate entries from the table that you discovered during diagnosis, so that there is only ever one entry for a particular
lower_user_name. You can check this has been fixed by running the SQL in the diagnosis section, and observing there are zero results. - Start JIRA.
JIRA starting up with no duplicates in this table should create the unique indexes automatically.