LDAP Sync not working for new Users - User has no unique key mapping error in logs

[Josh]

Hi guys,

we are using Jira 7.10 Server.

Since 8th dec our LDAP Sync does not synchronize new users. Already synced users are able to authenticate. The LDAP Connection test works just fine. But when i try to manually sync it shows the following:

Beginning on 8th dec the jira log starts throwing the following error:

java.lang.IllegalStateException: User 'Lea.Lecouty' has no unique key mapping.
at com.atlassian.jira.user.ApplicationUsers.from(ApplicationUsers.java:46)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.allowDeleteUser(OfBizUserDao.java:741)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.remove(OfBizUserDao.java:707)
at com.atlassian.jira.crowd.embedded.ofbiz.OfBizUserDao.removeAllUsers(OfBizUserDao.java:637)
at com.atlassian.jira.crowd.embedded.ofbiz.DelegatingUserDao.removeAllUsers(DelegatingUserDao.java:133)
at com.atlassian.jira.crowd.embedded.ofbiz.IndexedUserDao.removeAllUsers(IndexedUserDao.java:376)
at com.atlassian.jira.crowd.embedded.ofbiz.DelegatingUserDao.removeAllUsers(DelegatingUserDao.java:133)
at com.atlassian.jira.crowd.embedded.ofbiz.SwitchingUserDao.removeAllUsers(SwitchingUserDao.java:30)
at com.atlassian.crowd.directory.AbstractInternalDirectory.removeAllUsers(AbstractInternalDirectory.java:771)
at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.deleteCachedUsersByName(DbCachingRemoteChangeOperations.java:318)
at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.deleteCachedUsersNotIn(DbCachingRemoteChangeOperations.java:414)
at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.deleteCachedUsersNotIn(DirectoryCacheImplUsingChangeOperations.java:171)
at com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresher.synchroniseAllUsers(UsnChangedCacheRefresher.java:189)
at com.atlassian.crowd.directory.ldap.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:56)
at com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresher.synchroniseAll(UsnChangedCacheRefresher.java:163)
at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1186)
at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:74)
at com.atlassian.jira.crowd.embedded.JiraDirectorySynchroniser.synchronizeDirectory(JiraDirectorySynchroniser.java:77)
at com.atlassian.jira.crowd.embedded.JiraDirectorySynchroniser.runJob(JiraDirectorySynchroniser.java:52)
at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:443)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:438)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:462)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:390)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:285)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:282)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34)
at java.lang.Thread.run(Thread.java:745)

 We already removed the stated user from the active directory and recreated it. It seems like we had some issues with that user because it had some apostrophes in its name. 

After that i restarted jira to flush the user cache etc.

But i still have the same problem with the same error in the logs.

Do you guys know how i can get rid of that? Thank you alot!

Josh

[Josh]

This is the result: 

1.
select id,user_name,lower_user_name from cwd_user where user_name like '%Lea.Lecouty%';
id user_name lower_user_name
10708 Lea.Lecouty lea.lecouty
2.
select user_key,lower_user_name from app_user where lower_user_name like '%lea.lecouty%'
user_key lower_user_name
léa.lecouty lea.lecouty#1

I guess the user_key with a ´ is the problem? Can i just completely delete this user entry in the DB?

[Radek Dostál]

Is 'lea.lecouty#1' the actual lower_user_name in APP_USER or a typo/miscopy?

Anyway don't delete it through the db just yet, there are other tables that may reference APP_USER and you likely would end up breaking it someplace else. Deleting from db is last resort only and besides the usual make a backup test it first etc., you might want to check this atlassian knowledge base: https://confluence.atlassian.com/jirakb/delete-user-from-database-in-jira-server-733937809.html

Are you able to delete the user from interface or will end up with some exception?

Have you checked for duplicates in the app_user table?

I don't know if the 'é' could be a problem, but it looks weird and does not seem like something Jira would auto-generate -- the column Jira populates automatically when a user is created so not sure how it got there or whether it should be there to be honest. Have never come across special characters in user keys myself but can't confirm that.

On second look, based on the screenshot you provided, it could be that the user does not belong to any User Directory according to the UI.

Can we check if the user's directory is null?

select * from cwd_directory;
 -- will list all available user directories on your instance

select directory_id from cwd_user where lower_user_name like '%lea.lecouty%';

If it's null or non-existing id, then that's definitely wrong, each user must belong to a directory. In which case you might be able to fix this with a few sql commands (I think there are 3 or 4 tables referring to a user's directory), but maybe deleting the user would be easier in case you are sure you don't need to retain their data. Let's see what you find first though, better to find what's wrong than running into it again later :)

[Radek Dostál]

Hi Josh,

Have you at some point tampered with the database and tried modifying the directory/user? It reads that way a bit.

Sounds similar to https://jira.atlassian.com/browse/JRASERVER-37644

I think it might be worth taking a loot in the database if you are able to fire up a psql client:

Disclaimer as usual -- do not do this on production database if you are not perfectly confident you will not cause any updates or problems with the queries. Creating backups is recommended.

select id,user_name,lower_user_name from cwd_user where user_name like '%Lea.Lecouty%';

select user_key,lower_user_name from app_user where lower_user_name like '%lea.lecouty%'

There should be the following connection:

CWD_USER - straight forward, user_name and lower_user_name must match except of course the lower case

APP_USER - the user_key must be unique, and the lower_user_name must correspond to a user in CWD_USER

Ideally you should see something like

CWD_USER
id, user_name, lower_user_name
11100, Lea.Lecouty, lea.lecouty

APP_USER
user_key, lower_user_name
JIRAUSER30010, lea.lecouty

I'm curious whether you will find anything in app_user at all, or maybe with wrong mapping as you mention apostrophes maybe something has been stored improperly.

If you are on Postgres you can use 'ilike' instead of 'like' for case-insensitive search, to maybe be more sure.

Second thing to try is if you have any duplicate user_keys

SELECT user_key FROM app_user GROUP BY user_key HAVING COUNT(*) > 1

 (Although sorry cannot verify the SQL queries as I'm writing those off memory right now, have been dealing with user accounts recently).

[Josh]

I will check with our DB admins, thanks! But you might be right since the user in Jira looks like that : 

[Josh]