Last active
August 21, 2018 18:31
-
-
Save tobiasmh/3e5aa4f7a37d9e834647 to your computer and use it in GitHub Desktop.
Migrate a Confluence users activity to another user
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- WARNING | |
-- WARNING THIS IS PROBABLY INCOMPLETE AND MAY BREAK CONFLUENCE. USE AT YOUR OWN RISK | |
-- WARNING | |
-- Migrate a Confluence users activity to another user | |
SELECT * FROM user_mapping WHERE lower_username='old_username' OR lower_username='new_username'; | |
+----------------------------------+------------------+----------------+ | |
| user_key | username | lower_username | | |
+----------------------------------+------------------+----------------+ | |
| ff8080814094fe77014094ffd60c0115 | old_username | old_username | | |
| ff8080814094fe77014094ffd516005d | new_username | new_username | | |
+----------------------------------+------------------+----------------+ | |
-- Content | |
UPDATE CONTENT SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_LABEL SET OWNER = 'ff8080814094fe77014094ffd516005d' WHERE OWNER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE LABEL SET OWNER = 'ff8080814094fe77014094ffd516005d' WHERE OWNER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Permisssions | |
UPDATE CONTENT_PERM SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115' | |
UPDATE SPACEPERMISSIONS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACEPERMISSIONS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACEPERMISSIONS SET PERMUSERNAME = 'ff8080814094fe77014094ffd516005d' WHERE PERMUSERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER 'ff8080814094fe77014094ffd60c0115'; | |
-- Last edited | |
UPDATE CONTENT SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Likes | |
UPDATE LIKES SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Mentions | |
UPDATE BODYCONTENT SET BODY = REPLACE(BODY, 'ff8080814094fe77014094ffd60c0115', 'ff8080814094fe77014094ffd516005d') WHERE BODY LIKE '%ff8080814094fe77014094ffd60c0115%'; | |
-- Space owners | |
UPDATE SPACES SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACES SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Follows | |
UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = 'ff8080814094fe77014094ffd516005d' WHERE FOLLOWEE = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = 'ff8080814094fe77014094ffd516005d' WHERE FOLLOWER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Links | |
UPDATE LINKS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE LINKS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Remove login attempts for user to delete | |
DELETE FROM logininfo WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Notifications | |
UPDATE NOTIFICATIONS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE NOTIFICATIONS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE NOTIFICATIONS SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Page templates | |
UPDATE PAGETEMPLATES SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE PAGETEMPLATES SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Remove the user, so they can no longer be mentioned | |
DELETE FROM user_mapping WHERE user_key = 'ff8080814094fe77014094ffd60c0115'; |
This was very useful. However, there's an error on line 27:
UPDATE CONTENT_PERM SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER 'ff8080814094fe77014094ffd60c0115';
Should be:
UPDATE CONTENT_PERM SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115';
Thanks works like a charm. For Confluence 6.70 I had to add the following:
UPDATE usercontent_relation SET CREATOR = :newUserId WHERE CREATOR = :oldUserId;
UPDATE usercontent_relation SET SOURCEUSER = :newUserId WHERE SOURCEUSER = :oldUserId
UPDATE usercontent_relation SET LASTMODIFIER = :newUserId WHERE LASTMODIFIER = :oldUserId
Otherwise I got an FK constraint violation
Line 21 -- two questions -- first, isn't it redundant (or rather, isn't Line 26 redundant of 21)? Also, shouldn't there be a semicolon at the end of line 21?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello Tobias,
In order to avoid some duplicates in the database (and in the user management administration in Confluence), here are some additional queries:
delete from cwd_user_attribute where user_id='28311553';
delete from cwd_membership where child_user_id='28311553';
delete from cwd_user where id ='28311553';
Where "28311553" is the ID of the "old" user in the database.
Then, this query (provided by Atlassian in this ticket https://jira.atlassian.com/browse/CONF-30050) helped me to clean everything that needed to be cleaned :
DELETE FROM CONTENT
WHERE contentid IN
(select * from (SELECT DISTINCT c1.contentid
FROM CONTENT c1
JOIN CONTENT c2 ON c1.username = c2.username
WHERE c1.contenttype = 'USERINFO'
AND c2.contenttype = 'USERINFO'
AND c1.prevver is null
AND c2.prevver is null
AND c1.contentid > c2.contentid)
as tmp);
Note: For MySQL.
Nicolas.