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:

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 

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.

Resolution

1. On the source Confluence database, run the following query to check which usernames have null lower_username  entry:

   SELECT * FROM user_mapping 
   WHERE 
   username IS NULL OR 
   lower_username IS NULL OR 
   lower_username != lower(username); 

   Note down the usernames returned.

2. Run the following SQL commands against your database:

   UPDATE user_mapping SET lower_username = '<lowercase_username>' WHERE username = '<username>'; 

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

4. Restart Confluence
5. Create a new XML backup and import on the destination server.