Upgrade to 5.2.x fails due to trailing whitespace in the usernames

Steps to Reproduce

1. Set up a Confluence 5.1.x instance
2. Change some usernames in one of the tables to have trailing spaces. Example:

   update content set creator=concat(creator, ' ') where contenttype='USERINFO' and creator='admin';

3. Upgrade to Confluence 5.2.5

Expected Results

Upgrade should proceed smoothly without any issues

Actual Results

After Confluence retrieve all usernames:

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.

It is trying to insert all the users into user_mapping table:

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

and then it tries to insert the same username (with trailing space) to user_mapping table

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

However, this fails:

2013-09-25 17:45:56,993 ERROR [main] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Duplicate entry 'admin ' for key 'lower_username'

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)
.....

Replicated by upgrading to 5.2.3 as well.

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

Workaround

1. 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:

   drop table user_mapping;

2. Run the following SQL queries to find out which tables are affected:

   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 '% ';
   

3. Run the following SQL queries to fix affected tables:

   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);
   

4. Restart Confluence. Upgrade should pick up where it failed and it should complete successfully.