Restore of a site XML backup fails due to duplicate entry '' for key 'unq_lwr_username' error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Problem
Restore of a site XML backup can fail due to duplicate key value violation for unique constraint "unq_lwr_username"
Environment
Confluence Data Center
Diagnosis
The following errors can be seen in the application logs located under the <Confluence_home>/logs/ folder:
For Confluence instances using MySQL database:
Import failed. Check your server logs for more information.
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'unq_lwr_username' OR
For Confluence instances using PostgreSQL database:
Import failed. Check your server logs for more information. com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username" Detail: Key (lower_username)=() already exists. ⚠️ Similar errors can be reported with the MS SQL Server or Oracle databases too.
ℹ️ Notice that the error does not indicate any value for the lower_username entry.
ℹ️ If you happen to see a value inside the duplicate key entry, head to the KB: Confluence site XML import fails with "duplicate key" error
Cause
Usually, this error is caused when a user entry in the USER_MAPPING table has a value in the lower_username column as either null or the value does not match the lowercase version of their username.
Solution
Resolution
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
On the source Confluence database, run the following query to check which usernames have
nulllower_usernameentry:SELECT * FROM user_mapping WHERE username IS NULL OR lower_username IS NULL OR lower_username != lower(username);ℹ️ Note down the usernames returned.
Run the following SQL commands against your database:
UPDATE user_mapping SET lower_username = '<lowercase_username>' WHERE username = '<username>';Run the SELECT statement once again to ensure the modification took effect:
SELECT * FROM user_mapping WHERE username IS NULL OR lower_username IS NULL OR lower_username != lower(username);Restart Confluence
Create a new XML backup and import on the destination server.
Was this helpful?