Unable to upgrade Confluence due to duplicated users in the database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Failed to upgrade to 7.1 and onwards as the cwd_user has some duplicated records. During the upgrade, an attempt is made to add unique constraint to the table, and it eventually fails with OutOfMemoryError.
Environment
6.13.10
Diagnosis
The following error appears before the upgrade is stopped:
INFO [Catalina-utility-1] [internal.upgrade.constraint.UniqueConstraintAddition] addIfMissing Constraint [cwd_user_name_dir_id] not found on table [cwd_user]. Adding it soon.Verify if you have duplicated records with below SQL query:
SELECT lower_user_name, directory_id, count(*)
FROM cwd_user
GROUP BY lower_user_name, directory_id HAVING count(*) > 1;Cause
The unique constraint added with the following bug fix on 7.1:
As the cwd_user has large duplicated records, adding the unique constraint to the table was failed.
Solution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The issue can be fixed by removing the duplicated records with the following SQL query:
DELETE FROM cwd_user
WHERE id NOT IN (SELECT MIN(id) FROM cwd_user GROUP BY lower_user_name, directory_id);Then, try to upgrade the Confluence again.