Upgrade to Confluence 3.5.x fail due to duplicated users or groups
The upgrade to Confluence 3.5.x fails with a constraint violation error message at the logs. The following appears in the
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
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.
- 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;
- 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 = ?;
- Check the group membership to identify which is the correct group and user.
- Roll back your instance to a point pre-upgrade.
- 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 = ?
update users set name = 'a_name_of_your_choice' where id = ?;
- Try the upgrade again.
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.