Unknown Users appears in Mentions, permissions, etc due to duplicates in the user_mapping table

Related content

  • No related content found

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

Duplication of users appears in various places in Confluence due to bugs as reported in:

For example, when trying @ mention users, Confluence is returning 'Unknown Users' in the search results, or existing @ mention or restriction/permission entries are showing as 'Unknown Users', you might run into duplicates in the database, specifically the user_mapping  table.

                      


Environment

Confluence is connected to an external LDAP user directory.


Cause

This issue is observed when there are duplicated users that originated from across different user directories. For example, if Confluence is connected to LDAP, and also connected to Crowd that's connected to the same LDAP with the same sets of users. 

One of the possible steps to reproduce the issue:

  1. Set a user directory connection to LDAP A with username attribute: SAMAccountname
  2. Set another user directory connection to LDAP A with username attribute: mail
  3. Edit the second user directory's username attribute to SAMAccountname

Diagnosis

First, try a content reindex. If it doesn't help, run this query (diagnosis 1) below, if it returns any results, please proceed to the next diagnosis step.

Diagnosis 1 - NULL users


SELECT  * FROM user_mapping where lower_username is null;

Now you need to determine if you have single records or duplicated records, and which scenario types you are affected with. The workaround you perform will depend on this.



Diagnosis 2 - Determine Single/Duplicated records


select u.* from user_mapping u where lower(u.username) in (select lower(nullrecord.username) from user_mapping nullrecord where lower_username is null) order by u.username;


Below are 4 possible scenarios of how the query output from Diagnosis 2 would look like:

  • Scenario type 1 - Single user record, no lower username exists (Single Record)

    user_keyusernamelower_username
    ff8080814a5a97df014a5a97fb240001userA

  • Scenario type 2 - Duplicated users with no lower username exists, regardless of casings in the username column (Duplicated Records)

    user_keyusernamelower_username
    ff8080814a5a97df014a5a97fb240001userA
    ff8080814a5a97df014a5a97fb240002userA
    ff8080814a5a97df014a5a97fb240003usera

  • Scenario type 3 - Duplicated users with lower_username (Duplicated Records)


    		usernamelower_username
    ff8080814a5a97df014a5b16c37c0008userAusera
    ff8080814a5a97df014a5a97fb240001userA

  • Scenario type 4 - Duplicated users with lower_username, regardless of casings in the username column (Duplicated Records)

    user_keyusernamelower_username
    ff8080814a5a97df014a5b16c37c0008userAusera
    ff8080814a5a97df014a5a97fb240001UserA
    ff8080814a5a97df014a5a97fb240002userA

Please proceed to the appropriate workaround based on whether you see single or duplicated records. If you have both, apply the single records workaround first, then duplicated records.

Workaround

SINGLE RECORDS Workaround (scenario type 1)

Set the lower_username value for single records only. This will not affect nulls for duplicate records.


update user_mapping set lower_username = lower(username) where lower_username is null and lower(username) in 
(select LOWER(nullrecords.username) from user_mapping as nullrecords group by LOWER(nullrecords.username) having count(*) = 1);
Note for MySQL database

Due to a limitation in MySQL database where it doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria. 

UPDATE user_mapping 
SET lower_username = LOWER(username) 
WHERE lower_username IS NULL 
AND username IN (
    SELECT username_lower 
    FROM (
        SELECT LOWER(username) AS username_lower 
        FROM user_mapping 
        GROUP BY LOWER(username) 
        HAVING COUNT(*) = 1
    ) AS unique_usernames
);


 This query will search for all single records and update its lower_username with a lowercase of its username. 

Now run the diagnosis query (diagnosis 1) again to ensure you are no longer affected by the single records issue/Scenario 1 type issue, the query should return zero result.

DUPLICATED RECORDS Workaround (scenario type 2)

Please run the following SQL query, this query will search for records that have a scenario type 2 issue, and give one of the records to have a lower_username . Then, please proceed with #1 - Clean up null user records.

select username, lower_username, count(*) as count into temp1 from user_mapping group by username, lower_username having count(*) > 1;
select username into temp2 from temp1 where lower(username) not in (select lower(username) from user_mapping where lower_username is not null);
select username, max(user_key) as keeping into uniqueone FROM user_mapping where username in (select username FROM temp2) group by username;
update user_mapping set lower_username = lower(username) where user_key in (select keeping from uniqueone);
drop table temp1;
drop table temp2;
drop table uniqueone;
Note for MySQL database

Due to a limitation in MySQL database where it doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria. 

CREATE TEMPORARY TABLE temp1 AS
SELECT username, lower_username, COUNT(*) AS count
FROM user_mapping
GROUP BY username, lower_username
HAVING COUNT(*) > 1;

CREATE TEMPORARY TABLE temp2 AS
SELECT username
FROM temp1
WHERE lower(username) NOT IN (SELECT lower(username) FROM user_mapping WHERE lower_username IS NOT NULL);

CREATE TEMPORARY TABLE uniqueone AS
SELECT username, MAX(user_key) AS keeping
FROM user_mapping
WHERE username IN (SELECT username FROM temp2)
GROUP BY username;

UPDATE user_mapping set lower_username = lower(username) where user_key in (select keeping from uniqueone);

DROP TABLE temp1; 
DROP TABLE temp2; 
DROP TABLE uniqueone;

DUPLICATED RECORDS Workaround (scenario type 3 and scenario type 4)

Please proceed with #1 - Clean up null user records.


#1 - Clean up null user records (without content associated).

First, try to remove the null values. If there is no content that has been created by the users, this set of queries will work. If you run into any Foreign Key constraint errors during this process, please proceed to #2 - If the null user records have content associated with them.

delete from imagedetails where attachmentid in
 (select avatar.contentid from content avatar where avatar.pageid in
  (select userinfo.contentid from content userinfo 
   where userinfo.contenttype = 'USERINFO' 
   and userinfo.username in 
    (select user_key from user_mapping where lower_username is null)));

delete from contentproperties where contentid in
 (select avatar.contentid from content avatar where avatar.pageid in
  (select userinfo.contentid from content userinfo 
   where userinfo.contenttype = 'USERINFO' 
   and userinfo.username in 
    (select user_key from user_mapping where lower_username is null)));

delete from content avatar where avatar.pageid in
 (select userinfo.contentid from content userinfo 
  where userinfo.contenttype = 'USERINFO' 
  and userinfo.username in 
   (select user_key from user_mapping where lower_username is null));

delete from content userinfo 
 where userinfo.contenttype = 'USERINFO' 
 and prevver is not null
 and userinfo.username in
  (select user_key from user_mapping where lower_username is null);

delete from content userinfo 
 where userinfo.contenttype = 'USERINFO' 
 and prevver is null
 and userinfo.username in
  (select user_key from user_mapping where lower_username is null);

delete from logininfo where username in 
  (select user_key from user_mapping where lower_username is null);

delete from user_mapping where lower_username is null;
Note for MySQL database

Due to a limitation in MySQL database where it doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria.  Please use the following set of queries to clean up null user records (without content associated) instead.

DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN
 (SELECT AVATAR.CONTENTID FROM CONTENT AVATAR WHERE AVATAR.PAGEID IN
  (SELECT USERINFO.CONTENTID FROM CONTENT USERINFO 
   WHERE USERINFO.CONTENTTYPE = 'USERINFO' 
   AND USERINFO.USERNAME IN 
    (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL)));

DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN
 (SELECT AVATAR.CONTENTID FROM CONTENT AVATAR WHERE AVATAR.PAGEID IN
  (SELECT USERINFO.CONTENTID FROM CONTENT USERINFO 
   WHERE USERINFO.CONTENTTYPE = 'USERINFO' 
   AND USERINFO.USERNAME IN 
    (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL)));


CREATE TABLE TC LIKE CONTENT;

INSERT INTO TC
 (SELECT * FROM CONTENT  
  WHERE CONTENTTYPE = 'USERINFO' 
  AND USERNAME IN 
  (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL));

DELETE FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM TC);

DROP TABLE TC;

DELETE FROM CONTENT 
 WHERE CONTENTTYPE = 'USERINFO' 
 AND PREVVER IS NOT NULL
 AND USERNAME IN
  (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL);

DELETE FROM CONTENT 
 WHERE CONTENTTYPE = 'USERINFO' 
 AND PREVVER IS NULL
 AND USERNAME IN
  (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL);

DELETE FROM logininfo WHERE USERNAME IN 
  (SELECT USER_KEY FROM user_mapping WHERE LOWER_USERNAME IS NULL);
  
DELETE FROM user_mapping WHERE LOWER_USERNAME IS NULL;


#2 - If the null user records have content associated with them.

Step One: Find the affected users (Keypair Generator)

First, run the below query, and save the results. You'll need the oldKey and newKey from the results.

KeyPair Generator 
SELECT 
    user_key AS oldKey,
    (SELECT 
            user_key
        FROM
            user_mapping u
        WHERE
            lower(u.username) = u.lower_username
                AND lower(u.username) = lower(um.username)) AS newKey,
    um.username,
    um.lower_username
FROM
    user_mapping um
WHERE
    user_key IN (SELECT 
            user_key
        FROM
            user_mapping
        WHERE
            (lower(username) != lower_username))
        OR (lower_username is null)
Step Two: Validate your Keys:

Check if the result of the Keypair Generator produces any NULL values for the newkey column. NULL newkey values could exist when the scenario type 2 issue still exists in the database. Please follow SINGLE RECORDS Workaround (scenario type 1) and DUPLICATED RECORDS Workaround (scenario type 2) to ensure that scenario 1 and 2 has been ruled out.

Only proceed with Step Three when there are no NULL values in the newkey column. 

Step Three: Individual/Mass fixes
3.1 - Individual cleanup

Run the following SQL queries to map @oldkey into @newkey. This workaround is ideal when the Keypair generator only returns 1-5 users.

 If the KeyPair generator resulted in a large number of users, you can use a Python script provided in the next step to generate a set of SQL queries.

Change all Occurrence of oldkey and newkey in the SQL query template below, with the oldkey and newkey Keypair obtained from KeyPair Generator, either by using:

  • Declare method
  • Simple text editor

To use the Declare method, simply change the Declare Variables below to match the Key Pair obtained from the KeyPair Generator. Continue with running the SQL queries:

-- Declare Variables: 
SET @oldKey := 'foo'; SET @newKey := 'bar';

Or, use a simple text editor to 'find and replace' all occurrences of @oldkey and @newkey, from the SQL query template below, for each Key Pairs obtained from the KeyPair Generator.

-- TRACKBACKLINKS (Not required for version 7 or above)
UPDATE TRACKBACKLINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE TRACKBACKLINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACES
UPDATE SPACES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACE PERMISSIONS
UPDATE SPACEPERMISSIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEPERMISSIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE SPACEPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- SPACEGROUPS ( Not required for version 6 and above)
UPDATE SPACEGROUPS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEGROUPS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACEGROUPPERMISSIONS ( Not required for version 6 and above)
UPDATE SPACEGROUPPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- PAGETEMPLATES
UPDATE PAGETEMPLATES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE PAGETEMPLATES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- NOTIFICATIONS
UPDATE NOTIFICATIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE NOTIFICATIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE NOTIFICATIONS SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LINKS
UPDATE LINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE LINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LIKES
UPDATE LIKES SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LABEL
UPDATE LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- FOLLOW_CONNECTIONS
UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = @newKey WHERE FOLLOWER = @oldKey;
UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = @newKey WHERE FOLLOWEE = @oldKey;

-- EXTRNLINKS (Not required for version 7 or above)
UPDATE EXTRNLNKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE EXTRNLNKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;

-- CONTENT_PERM
UPDATE CONTENT_PERM SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE CONTENT_PERM SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT_PERM SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- CONTENT_LABEL
UPDATE CONTENT_LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- CONTENT
UPDATE CONTENT SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT SET USERNAME = @newKey WHERE USERNAME = @oldKey;
UPDATE CONTENT SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- USERCONTENT_RELATION ( Only required for version 6 and above)
UPDATE USERCONTENT_RELATION SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE USERCONTENT_RELATION SET SOURCEUSER = @newKey WHERE SOURCEUSER = @oldKey;
UPDATE USERCONTENT_RELATION SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;

-- ATTACHMENTS ( Not required for version 5.7 and above)
UPDATE ATTACHMENTS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE ATTACHMENTS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LOGIN INFO AND USER_MAPPING
DELETE FROM logininfo WHERE USERNAME = @oldKey;
DELETE FROM user_mapping where user_key = @oldKey;
Note for Microsoft SQL Server Database

For MSSQL database users, please use the following set of queries for Individual cleanup  instead.

-- TRACKBACKLINKS
UPDATE TRACKBACKLINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE TRACKBACKLINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACES
UPDATE SPACES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACE PERMISSIONS
UPDATE SPACEPERMISSIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEPERMISSIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE SPACEPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- SPACEGROUPS ( Not required for version 6 and above)
UPDATE SPACEGROUPS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEGROUPS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACEGROUPPERMISSIONS ( Not required for version 6 and above)
UPDATE SPACEGROUPPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- PAGETEMPLATES
UPDATE PAGETEMPLATES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE PAGETEMPLATES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- NOTIFICATIONS
UPDATE NOTIFICATIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE NOTIFICATIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE NOTIFICATIONS SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LINKS
UPDATE LINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE LINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LIKES
UPDATE LIKES SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LABEL
UPDATE LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- FOLLOW_CONNECTIONS
UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = @newKey WHERE FOLLOWER = @oldKey;
UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = @newKey WHERE FOLLOWEE = @oldKey;

-- EXTRNLINKS
UPDATE EXTRNLNKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE EXTRNLNKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;

-- CONTENT_PERM
delete 
old
FROM CONTENT_PERM old, CONTENT_PERM new
where old.CPS_ID = new.CPS_ID and old.CP_TYPE = new.CP_TYPE and old.USERNAME is not null and new.USERNAME is not null
and old.USERNAME = @oldKey and new.USERNAME = @newKey;

UPDATE CONTENT_PERM SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE CONTENT_PERM SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT_PERM SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- CONTENT_LABEL
UPDATE CONTENT_LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- CONTENT
UPDATE CONTENT SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT SET USERNAME = @newKey WHERE USERNAME = @oldKey;
UPDATE CONTENT SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- USERCONTENT_RELATION ( Only required for version 6 and above)
delete old
FROM USERCONTENT_RELATION old, USERCONTENT_RELATION new
where old.TARGETCONTENTID = new.TARGETCONTENTID and old.TARGETTYPE = new.TARGETTYPE and old.RELATIONNAME = new.RELATIONNAME
and old.SOURCEUSER = @oldKey and new.SOURCEUSER =  @newKey;

UPDATE USERCONTENT_RELATION SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE USERCONTENT_RELATION SET SOURCEUSER = @newKey WHERE SOURCEUSER = @oldKey;
UPDATE USERCONTENT_RELATION SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;

-- ATTACHMENTS ( Not required for version 5.7 and above)
UPDATE ATTACHMENTS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE ATTACHMENTS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LOGIN INFO AND USER_MAPPING
DELETE FROM logininfo WHERE USERNAME = @oldKey;
DELETE FROM user_mapping where user_key = @oldKey;
Note about the USERCONTENT_RELATION table

If you are getting any Unique Constraint errors while running the above SQL queries you can execute the below SQL queries and then continue to run the above SQL queries or the ones generated by mass-dup-fixer.zip.


PostgreSQL 
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;

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; 		

delete from usercontent_relation where sourceuser in (select old_user_key from tmp_user_mapping_migration);

DROP TABLE if exists tmp_user_mapping_migration;
MySQL 
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;

UPDATE USERCONTENT_RELATION 
JOIN tmp_user_mapping_migration ON USERCONTENT_RELATION.sourceuser = tmp_user_mapping_migration.old_user_key
SET USERCONTENT_RELATION.sourceuser = tmp_user_mapping_migration.new_user_key
WHERE NOT EXISTS (
    SELECT 1
    FROM (SELECT * 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
JOIN tmp_user_mapping_migration ON USERCONTENT_RELATION.creator = tmp_user_mapping_migration.old_user_key
SET USERCONTENT_RELATION.creator = tmp_user_mapping_migration.new_user_key;

UPDATE USERCONTENT_RELATION
JOIN tmp_user_mapping_migration ON USERCONTENT_RELATION.lastmodifier = tmp_user_mapping_migration.old_user_key
SET USERCONTENT_RELATION.lastmodifier = tmp_user_mapping_migration.new_user_key;

DELETE from USERCONTENT_RELATION where sourceuser in (select old_user_key from tmp_user_mapping_migration);

DROP TABLE tmp_user_mapping_migration;
Note about the SQL query template

Note about the SQL query template

The SQL queries provided could be applied to all confluence versions 5.x - 6.x - 7.x, therefore, there may be ERRORS for the following tables. Errors that say ERROR: "<table>" does not exist can be ignored. The tables that may be affected are:

  1. SPACEGROUPS
  2. SPACEGROUPPERMISSIONS
  3. USERCONTENT_RELATION
  4. ATTACHMENTS
  5. TRACKBACKLINKS
  6. EXTRNLINKS

The template also does not contain the fix for bodycontent table. This means that on pages where there were user mentions of @oldKey, you may see a 'Broken Link' instead in its place. You may choose to fix this occurrence by using the following search and replace query, however, running this query may run for a long time depending on the size of bodycontent table.

BODYCONTENT SET BODY = REPLACE(BODY,@oldkey,@newkey);


3.2 Mass Cleanup Using the mass duplicate fixer

If you have a large number of duplicates, you may choose to use the mass-dup-fixer.zipto generate the SQL queries. You'll need Python (tested on Python 2.7 and Python 3) installed to run the script: 

  1. Ensure that Python is installed
  2. Run the KeyPair Generator 

  3. Save the output of that query in a tab-separated format, oldKey<tab>newKey

    Example 
    2c9d81ef8450b135018450b2025c0000    2c9d81ef8450b135018450b269730001 
2c9d81ef8450b135018454c05f670002    2c9d80eb8473fa8c018474460f7a0001
  4. Name the file keypairs.txt
  5. Extract "mass-dup-fixer.zip" to a directory,
  6. Place your keypairs.txt  file into this directory.
  7. Run make-mass-dup-fixer.py from the directory you extracted everything to
  8. It will generate a file called output.sql
  9. Run the SQL queries inside the output.sql against Confluence database
Note about the SQL queries generated by the python script

Note about the SQL queries generated by the python script

 The SQL queries generated by the python script could be applied to all confluence versions 5.x - 6.x therefore, there may be ERRORS for the following tables. Errors that says  ERROR: "<table>" does not exist can be ignored. The tables that may be affected are:

  1. SPACEGROUPS
  2. SPACEGROUPPERMISSIONS
  3. USERCONTENT_RELATION
  4. ATTACHMENTS

 The template does not contain fix for bodycontent table. This means that on pages where there were user mentions of @oldKey, you may see broken link instead on its place.





Last modified on Feb 7, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.