Blog / 2017 / September / 27

Confluence Login Errors

September 27, 2017

I have a Confluence deployment that uses Crowd to authenticate users against Active Directory.

One of the users got a System Error at login:

org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2
    at org.springframework.orm.hibernate.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:590)
    at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:353)
    at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
    at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
    at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUserByExternalId(HibernateUserDao.java:501)
    at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindByExternalId(HibernateUserDao.java:473)
    at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.findByExternalId(HibernateUserDao.java:449)
    at com.atlassian.confluence.user.crowd.CachedCrowdUserDao.findByExternalId(CachedCrowdUserDao.java:164)

This was due to the fact that the user in question had got married and changed their username.

In the crowd database, the cwd_user table holds users, and I could see that I had two entries with the same external_id for the user in question.

This first query will allow you to identify users that have duplicate external_ids

select external_id, count(*)
from cwd_user
group by external_id 
order by 2 desc;

From here you can use the external_id returned to identify particular users:

select id, external_id, lower_user_name 
from cwd_user
where external_id = '12345:abc123';

This will show the duplicate rows:

    id    |               external_id               | lower_user_name
----------+-----------------------------------------+-----------------
  10      | 12345:abc123                            | maiden_s
  20      | 12345:abc123                            | married_s

You can't just delete from this table, you need to delete from the cwd_membership table first.

delete from cwd_membership
where child_user_id = 10;

Now we can delete from cwd_user

delete from cwd_user where lower_user_name = 'maiden_s';

Tags: confluence, crowd, active directory