Confluence サポート

プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。

このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバーバージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。

*Fisheye および Crucible は除く

要約

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.

環境

Confluence is connected to an external LDAP user directory.

原因

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:

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

診断

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_key username lower_username
ff8080814a5a97df014a5a97fb240001 userA
Scenario type 2 - Duplicated users with no lower username exists, regardless of casings in the username column (Duplicated Records)
user_key username lower_username
ff8080814a5a97df014a5a97fb240001 userA
ff8080814a5a97df014a5a97fb240002 userA
ff8080814a5a97df014a5a97fb240003 usera
Scenario type 3 - Duplicated users with lower_username (Duplicated Records)

username lower_username
ff8080814a5a97df014a5b16c37c0008 userA usera
ff8080814a5a97df014a5a97fb240001 userA
Scenario type 4 - Duplicated users with lower_username, regardless of casings in the username column (Duplicated Records)
user_key username lower_username
ff8080814a5a97df014a5b16c37c0008 userA usera
ff8080814a5a97df014a5a97fb240001 UserA
ff8080814a5a97df014a5a97fb240002 userA

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.

回避策

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;

-- CONTENT_RELATION
UPDATE CONTENT_RELATION SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE CONTENT_RELATION SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @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 MySQL 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;

-- CONTENT_RELATION
UPDATE CONTENT_RELATION SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE CONTENT_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 (see further down)

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

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:

spacegroups
spacegrouppermissions
USERCONTENT_RELATION
添付ファイル
TRACKBACKLINKS
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 to generate the SQL queries. You'll need Python (tested on Python 2.7 and Python 3) installed to run the script.

Create the following two files and put them into the same directory

template.sql

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

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

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

UPDATE SPACEGROUPS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEGROUPS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

UPDATE SPACEGROUPPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

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

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

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

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

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

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

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

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;

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

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

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;

UPDATE ATTACHMENTS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE ATTACHMENTS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

DELETE FROM logininfo WHERE USERNAME = @oldKey;
DELETE FROM user_mapping where user_key = @oldKey;

make-mass-dup-fixer.py

import os, sys

print ("Mass Dupe Fixer v1.0 - Blake Riosa [Atlassian]")
print ("with template.sql edited by mkhairuliana, btan and hlam [Atlassian]")
print ("This program requires two files:")
print (" - template.sql, containing SQL command for fixing dupe for a single @oldKey and @newKey pair")
print (" - keypairs.txt, containing {oldKey}\\t{newKey} lines (oldKey and newKey are tab delimited)")
print ("Output is written to output.sql")
print ("\n"),

for required_file in ['template.sql', 'keypairs.txt']:
	if not os.path.exists(required_file):
		print('[ERR] Could not find %s' % (required_file,))
		sys.exit(1)

with open('template.sql') as f:
	template = f.read()

key_pairs = []
with open('keypairs.txt') as f:
	for line in f:
		pair = line.strip().split('\t')
		if (len(pair) != 2):
			print('[ERROR] Line in keypairs did not contain {oldKey}\\t{newKey}: "%s"' % (line,))
			continue
		key_pairs.append(pair)

print ("[INFO] Successfully found %s oldKey/newKey pairs in keypairs.txt" % (len(key_pairs),))

with open('output.sql', 'w') as f:
	for pair in key_pairs:
		old_key, new_key = pair[0].replace('\'', '\\\''), pair[1].replace('\'', '\\\'')
		f.write(template.replace('@oldKey', "'%s'" % (old_key,)).replace('@newKey', "'%s'" % (new_key,)))

print ("[INFO] Output query successfully written to output.sql.")

Ensure that Python is installed
Run the KeyPair Generator

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

例

2c9d81ef8450b135018450b2025c0000    2c9d81ef8450b135018450b269730001 
2c9d81ef8450b135018454c05f670002    2c9d80eb8473fa8c018474460f7a0001

Name the file keypairs.txt
Place your keypairs.txt file into the same directory as the make-mass-dup-fixer.py and template.sql.
Run make-mass-dup-fixer.py from that directory
It will generate a file called output.sql
Run the SQL queries inside the output.sql against Confluence database

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:

spacegroups
spacegrouppermissions
USERCONTENT_RELATION
添付ファイル

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.

Confluence サポート

使用を開始する

ナレッジベース

製品

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

リソース

関連ドキュメント

コミュニティ

システムステータス

提案とバグ

Marketplace

請求とライセンス情報

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

お困りですか?

要約

環境

原因

診断

Diagnosis 1 - NULL users

Diagnosis 2 - Determine Single/Duplicated records

回避策

SINGLE RECORDS Workaround (scenario type 1)

DUPLICATED RECORDS Workaround (scenario type 2)

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

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

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

Step One: Find the affected users (Keypair Generator)

Step Two: Validate your Keys:

Step Three: Individual/Mass fixes

3.1 - Individual cleanup

Note about the USERCONTENT_RELATION table

NOTE about the SQL query template

3.2 Mass Cleanup Using the mass duplicate fixer

Note about the SQL queries generated by the python script

user_key	username	lower_username
ff8080814a5a97df014a5a97fb240001	userA
ff8080814a5a97df014a5a97fb240002	userA
ff8080814a5a97df014a5a97fb240003	usera

	username	lower_username
ff8080814a5a97df014a5b16c37c0008	userA	usera
ff8080814a5a97df014a5a97fb240001	userA

ページ

Viewport

Confluence

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

関連コンテンツ

お困りですか?

要約

環境

原因

診断

Diagnosis 1 - NULL users

Diagnosis 2 - Determine Single/Duplicated records

回避策

SINGLE RECORDS Workaround (scenario type 1)

DUPLICATED RECORDS Workaround (scenario type 2)

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

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

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

Step One: Find the affected users (Keypair Generator)

Step Two: Validate your Keys:

Step Three: Individual/Mass fixes

3.1 - Individual cleanup

Note about the USERCONTENT_RELATION table

NOTE about the SQL query template

3.2 Mass Cleanup Using the mass duplicate fixer

Note about the SQL queries generated by the python script

関連コンテンツ