Migrate a Confluence users activity to another user

gistfile1.sql

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

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.

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?