Confluence Site or Space XML import fails with "Duplicate key value violates unique constraint" error for unq_lwr_username or u2c_relation_unique

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 restoring a Site or Space XML it can fail due to duplicate key value violation for unique constraint "unq_lwr_username" or “u2c_relation_unique“.

Environment

Confluence 6.2.4 and higher.

Diagnosis

The following errors can be seen in the application logs located under the <Confluence_home>/logs/ folder when trying to import a Site or Space XML:

2022-04-03 18:43:41,930 ERROR [Long running task: Importing data] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: duplicate key value violates unique constraint "unq_lwr_username"
  Detail: Key (lower_username)=() already exists.

and/or

2022-04-03 15:24:08,766 ERROR [Long running task: Importing data] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: duplicate key value violates unique constraint "u2c_relation_unique"
  Detail: Key (targetcontentid, sourceuser, relationname)=(123456789, 9fd560a779e784fb017b30c921ef01ff, touched) already exists.
 -- url: /admin/restore-local-file.action | referer: http://localhost:8090/wiki/admin/backup.action | traceId: f9d3c2ce94a5b1f9 | userName: atlassian | action: restore-local-file


You can confirm you are hitting this issue by running the following two queries on the Confluence database from where you did the export:

  1. Query: 

    select count(username), username from user_mapping group by username having count(username) > 1;
  2. Query: 

    select count(um.username), uc.targetcontentid, um.username, uc.relationname from usercontent_relation uc join user_mapping um on um.user_key = uc.sourceuser group by uc.targetcontentid, um.username,uc.relationname having count(um.username) > 1;
    SELECT COUNT(um.username), uc.TARGETCONTENTID, um.username, uc.RELATIONNAME FROM USERCONTENT_RELATION uc JOIN user_mapping um ON um.user_key = uc.SOURCEUSER GROUP BY uc.TARGETCONTENTID, um.username,uc.RELATIONNAME HAVING COUNT(um.username) > 1;

Cause

There is or there was more than one user with same username in user_mapping table.

 Even if they are actually the same user,  they have different user_key and its lower_username is NULL for the problematic entry on user_mapping table. It is not clear yet how or when these “Shadow Users” are created, and they do not cause any issue on the current environment.

The “Shadow Users” are an issue only when exporting from a Confluence where they exist and importing to a new Confluence because of CONFSERVER-56054.

“Shadow Users” doesn’t seem to affect Confluence daily normal operation.

Workaround

Where a workaround involves running SQL scripts directly on your database, we strongly recommend you do this first on a non-production, staging or test environment before running the scripts on your production database. We also strongly recommend you take a backup of your data before making any modifications via SQL scripts.

To be able to complete the import it will be necessary to “merge“ users with their “shadows” on Confluence where you did the export, then export it again and finally import it on the other Confluence.

Step 1: Stop Confluence

As we are manipulating data, the instance must be stopped before proceeding. This will prevent any new records from being created or modified during the update process below.

Stop the Confluence where you were did the export.

Step 2: Create a temp table to map users and their shadows

This will create the tmp_user_mapping_migration table and will load it mapping users and their “shadows”.

(info) (These queries were tested on PostgreSQL DB)

DROP TABLE if exists tmp_user_mapping_migration;
CREATE TABLE tmp_user_mapping_migration (old_user_key varchar(255), new_user_key varchar(255));
INSERT INTO tmp_user_mapping_migration (old_user_key, new_user_key)
select umNull.user_key, umNotNull.user_key from user_mapping umNotNull    
      inner join user_mapping umNull on lower(umNotNull.username) = lower(umNull.username) 
where umNotNull.lower_username is not null
and umNull.lower_username is null;

Step 3: Fix the data associated to “shadow users”

This will fix all data associated to “shadow users” and delete them at the end.

(info) (These queries were tested on PostgreSQL DB)

update spacepermissions set permusername = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spacepermissions.permusername = tmp_user_mapping_migration.old_user_key;
update spacepermissions set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where	spacepermissions.creator = tmp_user_mapping_migration.old_user_key;
update spacepermissions set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spacepermissions.lastmodifier = tmp_user_mapping_migration.old_user_key; 
update spaces set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spaces.creator = tmp_user_mapping_migration.old_user_key; 		
update spaces set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where spaces.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content_label set owner = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_label.owner = tmp_user_mapping_migration.old_user_key; 		
update pagetemplates set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where pagetemplates.creator = tmp_user_mapping_migration.old_user_key; 		
update pagetemplates set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where pagetemplates.lastmodifier = tmp_user_mapping_migration.old_user_key; 	
update logininfo set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where logininfo.username = tmp_user_mapping_migration.old_user_key
and not exists (
    select 1
    from logininfo l
    where l.username=tmp_user_mapping_migration.new_user_key
);
update label set owner = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where label.owner = tmp_user_mapping_migration.old_user_key; 		
update user_relation set sourceuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.sourceuser = tmp_user_mapping_migration.old_user_key; 		
update user_relation set targetuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.targetuser = tmp_user_mapping_migration.old_user_key; 		
update user_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.creator = tmp_user_mapping_migration.old_user_key; 		
update user_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where user_relation.lastmodifier = tmp_user_mapping_migration.old_user_key;
update usercontent_relation set sourceuser = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.sourceuser = tmp_user_mapping_migration.old_user_key
and not exists (
    select 1
    from usercontent_relation u
    where u.targetcontentid=usercontent_relation.targetcontentid
      and u.sourceuser=tmp_user_mapping_migration.new_user_key
      and u.relationname=usercontent_relation.relationname
);	
update usercontent_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.creator = tmp_user_mapping_migration.old_user_key; 
update usercontent_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where usercontent_relation.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content_relation set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_relation.creator = tmp_user_mapping_migration.old_user_key; 		
update content_relation set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_relation.lastmodifier = tmp_user_mapping_migration.old_user_key; 
update links set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where links.creator = tmp_user_mapping_migration.old_user_key; 		
update links set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where links.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update likes set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where likes.username = tmp_user_mapping_migration.old_user_key; 		
update content set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.creator = tmp_user_mapping_migration.old_user_key; 		
update content set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update content set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content.username = tmp_user_mapping_migration.old_user_key; 		
update notifications set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.username = tmp_user_mapping_migration.old_user_key; 		
update notifications set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.creator = tmp_user_mapping_migration.old_user_key; 		
update notifications set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where notifications.lastmodifier = tmp_user_mapping_migration.old_user_key; 	
update content_perm set username = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.username = tmp_user_mapping_migration.old_user_key
and not exists (
    select 1
    from content_perm c
    where c.cps_id=content_perm.cps_id
      and c.cp_type=content_perm.cp_type
      and c.username=content_perm.username
);	
update content_perm set creator = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.creator = tmp_user_mapping_migration.old_user_key; 		
update content_perm set lastmodifier = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where content_perm.lastmodifier = tmp_user_mapping_migration.old_user_key; 		
update follow_connections set follower = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where follow_connections.follower = tmp_user_mapping_migration.old_user_key; 	
update follow_connections set followee = tmp_user_mapping_migration.new_user_key from tmp_user_mapping_migration where follow_connections.followee = tmp_user_mapping_migration.old_user_key;
delete from logininfo where username in (select old_user_key from tmp_user_mapping_migration);
delete from usercontent_relation where sourceuser in (select old_user_key from tmp_user_mapping_migration);
delete from content_perm where username in (select old_user_key from tmp_user_mapping_migration);
delete from user_mapping where user_key in (select old_user_key from tmp_user_mapping_migration);
DROP TABLE if exists tmp_user_mapping_migration;

Step 4: Validate it was fixed

Run these two queries: (These queries were tested on PostgreSQL DB)

select count(username), username from user_mapping group by username having count(username) > 1;
select count(um.username), uc.targetcontentid, um.username, uc.relationname from usercontent_relation uc join user_mapping um on um.user_key = uc.sourceuser group by uc.targetcontentid, um.username,uc.relationname having count(um.username) > 1;
  • If both of these queries return no records it means the workaround did the “merge”.
  • If it still returns any record, review the previous queries output for any errors and contact Atlassian Support for further investigation.

Step 5: Start Confluence

Start Confluence.

Step 6: Export

Run the export, now the data will be fixed in the export file.

Step 7: Import

Run the import on the Confluence where it failed before.


(warning) Note: In case you intend to continue using the Confluence where you "merged” users with their shadows we recommend to:




Last modified on Nov 1, 2023

Was this helpful?

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