Details
-
Bug
-
Resolution: Fixed
-
Medium
-
5.2.3, 5.2.5
-
None
Description
Steps to Reproduce
- Set up a Confluence 5.1.x instance
- 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';
- 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
- 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;
- 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 '% ';
- 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);
- Restart Confluence. Upgrade should pick up where it failed and it should complete successfully.