-
-
Save tobiasmh/3e5aa4f7a37d9e834647 to your computer and use it in GitHub Desktop.
-- 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'; |
I ran these on a production Confluence instance running 5.8.4
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.
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?
Hello Tobias,
Sorry to ask you again but I'm very interested by those queries. I'd like to use them for a production environment. So, did you get some feedbacks about them? On which version did you try them?
Regards,
Nicolas.