Getting Duplicate Key Violates Unique Constraint 'os_propertyentry_pkey' on Upgrade to 3.5.x
Symptoms
Upgrading to Confluence 3.5 or 3.5.1 fails. The following appears in the atlassian-confluence.log:
2011-03-16 16:56:43,662 ERROR [main] [confluence.user.migration.AtlassianUserDataMigrator] migrateUserProperties Error migrating user properties for: somebodysUserName, PreparedStatementCallback; SQL [update OS_PROPERTYENTRY set entity_id = 0, entity_name = ? where entity_name = ? AND entity_key <> ?]; ERROR: duplicate key violates unique constraint "os_propertyentry_pkey"; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "os_propertyentry_pkey"
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update OS_PROPERTYENTRY set entity_id = 0, entity_name = ? where entity_name = ? AND entity_key <> ?]; ERROR: duplicate key violates unique constraint "os_propertyentry_pkey"; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "os_propertyentry_pkey"
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "os_propertyentry_pkey"
:
Diagnosis
- Notice
somebodysUserNamein the error above. This indicates the user property migration failed for this user. - Running this query returns the same user name with different
entity_id:select * from os_propertyentry where entity_name like '%somebodysUserName%';
The duplicate users could exist due to manual database update.
Resolution
This has been fixed in 3.5.2, so if possible, you should upgrade directly to 3.5.2 instead of 3.5 or 3.5.1.
If upgrading to 3.5 or 3.5.1, download the zip attached to CONF-22139 and follow the instructions on the ticket for installation and upgrade.
Workaround
You should remove the entries that belong to the old user (make a backup):
delete from os_propertyentry where entity_id = XXXX;
If unsure which rows to delete, please find the user id that belongs to the current login and match it with the entity_id value. This is the one that you should retain:
select * from users where name = 'somebodysUserName'; (for internal user management)
select * from external_entities where name = 'somebodysUserName'; (if using external user management)
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.