Upgrade to Confluence 3.5.x fail due to duplicated users or groups

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The upgrade to Confluence 3.5.x fails with a constraint violation error message at the logs. The following appears in the atlassian-confluence.log:


2011-04-16 19:37:36,310 ERROR [main] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
2011-04-16 19:37:36,341 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: com.atlassian.crowd.model.group.InternalGroup#32997392; SQL []; ORA-00001: unique constraint (CONFLU_DEVD.CWD_GROUP_NAME_DIR_ID) violated
; nested exception is java.sql.SQLException: ORA-00001: unique constraint (CONFLU_DEVD.CWD_GROUP_NAME_DIR_ID) violated

Cause

The upgrade fails due to groups and/or users with the same name on Confluence database, some times the only difference is case sensitive spelling.

Diagnosis

  1. Run these queries against the database pre-upgrade to check if there are duplicated users or groups:
    
    select * from groups ga, groups gb where lower (ga.groupname)=lower(gb.groupname) AND ga.id != gb.id;
    
    
    select * from users ua, users ub where lower (ua.name)=lower (ub.name) AND ua.id != ub.id;
    
  2. If the queries above returns any result, to check which is the correct group and/or users, run the following queries to each user or group:
    Note: The ? at the end of the queries means the id of the duplicated user/group.
    
    SELECT u.name, g.groupname FROM users u, groups g, local_members l WHERE u.id = l.userid AND g.id = l.groupid AND g.id = ?;
    
    
    SELECT u.name, g.groupname FROM users u, groups g, local_members l WHERE u.id = l.userid AND g.id = l.groupid AND u.id = ?;
    
  3. Check the group membership to identify which is the correct group and user.

Resolution

  1. Roll back your instance to a point pre-upgrade.
  2. Rename the duplicated users and/or groups running those queries:
    Note: The ? at the end of the queries means the id of the user/group.
    To rename the groups:
    
    update groups set groupname = 'a_name_of_your_choice' where id = ?
    
    To rename the users:
    
    update users set name = 'a_name_of_your_choice' where id = ?;
    
  3. Try the upgrade again.

(info) After the upgrade, if needed, you can disable the wrong users directly at Confluence web interface to prevent those users to count against the license.


Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.