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.

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.