Confluence site XML import fails with "duplicate key" error

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

When attempting to restore a site XML backup to Confluence, it fails with an error.

Environment

Confluence Server and Data Center

Diagnosis

An error appears in atlassian-confluence.log:

2014-01-20 19:59:33,583 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities: 
 -- referer: http://localhost:8090/setup/setup-restore-start.action | url: /setup/setup-restore-local.action | userName: admin | action: setup-restore-local
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: SQLException: Violation of UNIQUE KEY constraint 'unq_lwr_username'. Cannot insert duplicate key in object 'dbo.user_mapping'. The duplicate key value is (user1).
	at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:72)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:387)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:355)
	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:119)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:193)

As identifiable through the error message, the error is explicitly telling us what's the reason for the import to fail – Unable to complete import – Violation of UNIQUE KEY constraint 'unq_lwr_username'. Cannot insert duplicate key in object 'dbo.user_mapping'. The duplicate key value is (user1).

Cause

The XML backup contains a duplicate record that the import is trying to insert into the user_mapping table for uniq_lwr_username = user1 (for the example above).

If you do not see any value in the duplicate entry, like Key (lower_username)=() or Duplicate entry '' for key ‘unq_lwr_username’, head to this KB instead: Restore of a site XML backup fails due to duplicate entry '' for key 'unq_lwr_username' error

Workaround

If you have no other recovery data besides the XML backup, you'll need to modify the XML backup to remove the duplicated record of the mentioned user.

Unzip the backup file and edit the entities.xml. Find the following object class which has the offending username:

<object class="ConfluenceUserImpl" package="com.atlassian.confluence.user">
<id name="key">402881a340e4a73a0140e4a7e42c0009</id>
<property name="name"><![CDATA[	 user1]]></property>
<property name="lowerName"><![CDATA[	user1]]></property>
</object>

You'll find two object class entries with the same username in the entities.xml. Delete the duplicated entry and make sure there's only one object class for this particular user. Save back the file and zip it back into the XML backup and re-import it again.


All usernames (lowerName and name) properties need to be cleared of all spaces in front and after the name, before zipping back the document. You can use bottom Linux command to find all properties and then manually update them:

grep -E 'name\=\"name\"|name\=\"lowerName\"' entities.xml | grep -E '\[\s+.*?\]|\[.*\s+\]'

or this Python script:

import re
entitiesFile = open("entities.xml")
entities = entitiesFile.read();
pattern = '<object class="ConfluenceUserImpl" package="com.atlassian.confluence.user">\n<id name="key">\S*<\/id>\n<property name="name"><!\[CDATA\[(.*)\]\]><\/property>\n<property name="lowerName"><!\[CDATA\[(.*)\]\]><\/property>\n<\/object>'
regex = re.compile(pattern, re.IGNORECASE)
for match in regex.finditer(entities):
        print "%s,%s,%s" % (match.start(), match.group(1), match.group(2))
entitiesFile.close()

Solution 

Perform cleanup in Confluence in order to avoid this issue in the future.

Solution 1

Attempt to Rebuild the index from scratch. Check if duplicated objects still exist in the database.

If duplicated objects still exist, proceed with Resolution 2 on deleting the objects on the database side.

Solution 2

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.

Step 1: Find the duplicated userkey

Run the query below to get the user key of the duplicated user(s):

SELECT * FROM user_mapping 
WHERE 
username IS NULL OR 
lower_username IS NULL OR 
lower_username NOT LIKE lower(username); 


The result should be as follow:

user_key
username
lower_username
402881a340e4a73a0140e4a7e42c0009
user111
user1
402881a340e4a73a0140e4a7e42c0008
user2
    user2
402881a340e4a73a0140e4a7e42c0010

user3
Step 2: Delete the duplicated user

Run the following SQL query to delete the offending rows:

DELETE FROM user_mapping WHERE user_key = '402881a340e4a73a0140e4a7e42c0009';
DELETE FROM user_mapping WHERE user_key = '402881a340e4a73a0140e4a7e42c0008';
DELETE FROM user_mapping WHERE user_key = '402881a340e4a73a0140e4a7e42c0010';
Step 3: Cleanup

Flush Confluence cache before attempting to create another XML backup.

  • Confluence Admin > Cache Management > Flush All

Note: 

Please keep an eye on and vote for the open bug report we have regarding this situation:


Last modified on Mar 24, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.