Issue Summary

When a Space is imported from Cloud to Server, the import logic is comparing cwd_user.user_name instead of cwd_user.lower_user_name. This can break matching process of username when they have different casing.

Steps to Reproduce

1. In Confluence server have a single user exist with different cwd_user.user_name but same cwd_user.lower_user_name (casing will be different)

   user_name	lower_user_name	email	external ID	Directory ID
   user	user	user@example.com	937c9532-f12f-437b-a8fd-7e9e0e8d8e69	294913
   USER	user	user@example.com	86782f67-5bc1-4818-9326-a9bc63eeccb3	8126465
   USER	user	user@example.com	458af6bc-40ae-49ae-9dff-7528d854836e	8126466

1. If user_mapping table has another user which the following username,

   user_key	username	lower_username
   402880847157f278017157f699530000	user@example.com	user@example.com

1. Import a Space from Cloud with user_mapping object like follows which will be mapped to the existing user in Confluence's DB
   Cloud's XML export

   <object class="ConfluenceUserImpl" package="com.atlassian.confluence.user">
   <id name="key"><![CDATA[8a7f808564e27fae0164d4db118d0064]]></id>
   <property name="name"><![CDATA[user@example.com]]></property>
   <property name="lowerName"><![CDATA[user@example.com]]></property>
   <property name="atlassianAccountId"><![CDATA[5a0bbaa80e8b4605b373d809]]></property>
   </object>
   

Expected Results

Confluence should recognise that the imported user already exists in the database of Confluence and maps all imported content to the username user

Actual Results

During the import, when Confluence failed to match the email address from Cloud Export to a unique user in cwd_user.lower_email_address table, it will attempt to create a new entry in the user_mapping table with the email address. In the case if a user_mapping.lower_username entry already existed, the error below can be seen in the atlassian-confluence.log:

2020-11-24 01:15:26,480 WARN [Long running task: Importing data] [importexport.xmlimport.persister.ConfluenceUserPersister] persist Ambiguous result while trying to match user by email address. An Unknown User will be created for email [user@example.com] entry.

A new row (user) will be inserted into user_mapping which will eventually result into violation of the unique constraint on user_mapping.lower_username

2020-11-24 01:15:26,530 ERROR [Long running task: Importing data] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Cannot insert duplicate key row in object 'dbo.user_mapping' with unique index 'user_mapping_unq_lwr_username'. The duplicate key value is (user@example.com).

Workaround

Workaround 1

Update LDAP server to have lower case usernames temporarily. Synchronise with Confluence and re-import the Space.

Workaround 2

Since the user_key is different for the user in Cloud side, for each affected user, update the entities.xml file to match the user_key as that of target instance's.

• Unzip the XML export
• Update the entities.xml file for each affected user, from  

  <id name="key"><![CDATA[8a7f808564e27fae0164d4db118d0064]]></id>
  

  to:

  <id name="key"><![CDATA[402880847157f278017157f699530000]]></id>

• Re-zip the export and import again