com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ReferencedUsersUserMappingUpgradeTask@6be9ac06 failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation.

Atlassian JIRA | Amalia Sanusi [Atlassian] | 4 years ago
tip
Click on the to mark the solution that helps you, Samebug will learn from it.
As a community member, you’ll be rewarded for you help.
  1. 0

    h4. Steps to Reproduce # Set up a Confluence 5.1.x instance # Change some usernames in one of the tables to have trailing spaces. Example: {code:sql}update content set creator=concat(creator, ' ') where contenttype='USERINFO' and creator='admin';{code} # Upgrade to Confluence 5.2.5 h4. Expected Results Upgrade should proceed smoothly without any issues h4. Actual Results After Confluence retrieve all usernames: {code}2013-09-25 17:45:56,713 INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Retrieving usernames from the database. 2013-09-25 17:45:56,923 INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Found 32 unique usernames that need mapping created. {code} It is trying to insert all the users into {{user_mapping}} table: {code}2013-09-25 17:45:56,961 DEBUG [main] [net.sf.hibernate.SQL] log insert into user_mapping (username, lower_username, user_key) values (?, ?, ?) 2013-09-25 17:45:56,962 TRACE [main] [sf.hibernate.type.StringType] nullSafeSet binding 'admin' to parameter: 1 2013-09-25 17:45:56,962 TRACE [main] [sf.hibernate.type.StringType] nullSafeSet binding 'admin' to parameter: 2 {code} and then it tries to insert the same username (with trailing space) to {{user_mapping}} table {code}2013-09-25 17:45:56,982 DEBUG [main] [net.sf.hibernate.SQL] log insert into user_mapping (username, lower_username, user_key) values (?, ?, ?) 2013-09-25 17:45:56,982 TRACE [main] [sf.hibernate.type.StringType] nullSafeSet binding 'admin ' to parameter: 1 2013-09-25 17:45:56,983 TRACE [main] [sf.hibernate.type.StringType] nullSafeSet binding 'admin ' to parameter: 2 {code} However, this fails: {code}2013-09-25 17:45:56,993 ERROR [main] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Duplicate entry 'admin ' for key 'lower_username' {code} {code}2013-09-25 22:44:29,391 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ReferencedUsersUserMappingUpgradeTask@6be9ac06 failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation. com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ReferencedUsersUserMappingUpgradeTask@6be9ac06 failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation. at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:278) at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:203) .... Caused by: java.lang.RuntimeException: Unable to complete user mapping creation. at com.atlassian.confluence.upgrade.upgradetask.AbstractUserMappingUpgradeTask.doUpgrade(AbstractUserMappingUpgradeTask.java:71) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ..... Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'admin' for key 'lower_username' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ..... {code} Replicated by upgrading to 5.2.3 as well. h4. Findings MySQL and SQL Server (possibly others) database servers ignore trailing space when comparing strings. On instances where a user with a certain username and another user with the same username with whitespace characters in the end (e.g. 'jdoe' and 'jdoe ') have existed, the upgrade is likely to fail h4. Workaround # Rollback your Confluence database to pre-upgrade state and ensure that the {{user_mapping}} table does not exist. If it does, run this query to drop it: {code:sql}drop table user_mapping;{code} # Run the following SQL queries to find out which tables are affected: {code:sql} select * from ATTACHMENTS where creator like binary '% ' or lastmodifier like binary '% '; select * from CONTENT where creator like binary '% ' or lastmodifier like binary '% ' or username like binary '% '; select * from CONTENT_LABEL where owner like binary '% '; select * from CONTENT_PERM where username like binary '% ' or creator like binary '% ' or lastmodifier like binary '% '; select * from cwd_user where user_name like binary '% ' or lower_user_name like binary '% '; select * from LIKES where username like binary '% '; select * from LINKS where creator like binary '% ' or lastmodifier like binary '% '; select * from logininfo where username like binary '% '; select * from NOTIFICATIONS where username like binary '% ' or creator like binary '% ' or lastmodifier like binary '% '; select * from OS_PROPERTYENTRY where entity_name like binary 'CWD_% '; select * from PAGETEMPLATES where creator like binary '% ' or lastmodifier like binary '% '; select * from remembermetoken where username like binary '% '; select * from SPACEGROUPPERMISSIONS where permusername like binary '% '; select * from SPACEGROUPS where creator like binary '% ' or lastmodifier like binary '% '; select * from SPACEPERMISSIONS where permusername like binary '% ' or creator like binary '% ' or lastmodifier like binary '% '; select * from SPACES where creator like binary '% ' or lastmodifier like binary '% '; select * from TRACKBACKLINKS where creator like binary '% ' or lastmodifier like binary '% '; {code} # Run the following SQL queries to fix affected tables: {code:sql} update ATTACHMENTS set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update CONTENT set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier), username = rtrim(username); update CONTENT_LABEL set owner = rtrim(owner); update CONTENT_PERM set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier), username = rtrim(username); update cwd_user set user_name = rtrim(user_name), lower_user_name = rtrim(lower_user_name); update LIKES set username = rtrim(username); update LINKS set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update logininfo set username = rtrim(username); update NOTIFICATIONS set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier), username = rtrim(username); update OS_PROPERTYENTRY set entity_name = rtrim(entity_name) where entity_name like 'CWD_%'; update PAGETEMPLATES set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update remembermetoken set username = rtrim(username); update SPACEGROUPPERMISSIONS set permusername = rtrim(permusername); update SPACEGROUPS set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update SPACEPERMISSIONS set permusername = rtrim(permusername), creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update SPACES set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); update TRACKBACKLINKS set creator = rtrim(creator), lastmodifier = rtrim(lastmodifier); {code} # Restart Confluence. Upgrade should pick up where it failed and it should complete successfully.

    Atlassian JIRA | 4 years ago | Amalia Sanusi [Atlassian]
    com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ReferencedUsersUserMappingUpgradeTask@6be9ac06 failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation.

    Root Cause Analysis

    1. com.atlassian.confluence.upgrade.UpgradeException

      Upgrade task com.atlassian.confluence.upgrade.upgradetask.ReferencedUsersUserMappingUpgradeTask@6be9ac06 failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation.

      at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep()
    2. com.atlassian.confluence
      AbstractUpgradeManager.runSchemaUpgradeTasks
      1. com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:278)
      2. com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:203)
      2 frames