Confluence users unknown after Crowd migration

Mircea Marin

Hi,

we recently migrated to using Atlassian Crowd for user management for our Atlassian tools (Confluence, JIRA). Previously the user management was done through JIRA. In the process we also changed the usernames from <name> to <SSO>. In JIRA this worked fine as we first renamed the users and upon activating the Crowd authentication, all data was there.

The problem is with Confluence where the content created by the users with the previous usernames is now authored by "Unknown User (<old_username>)". Also, when hovering over the name we get a "User <old_username> not found".

Any way to fix this so that the system recognises what happened and displays the correct names?

Thank you!

Mircea Marin

So after some long conversations with Atlassian Support they gave me the idea on how to fix this...and it is a bit complicated.

The main idea is the following: replace the user_key of the old user with the user_key of the new user in all tables of the confluence database where it appears. The tricky part is, it appears in a lot of tables and in different columns.

For that you will need a bit of SQL knowledge and access to the Confluence database. For that I used pgAdmin (as our database is in postgreSQL). Following are the steps needed.

1. Stop Confluence and make a backup of your datbase!
2. Using pgAdmin (or another tool to access the database), create a new function that updates the values in the tables where it appears:

   CREATE OR REPLACE FUNCTION "Repair_Users"("OLD_TOKEN" character varying, "NEW_TOKEN" character varying)
     RETURNS boolean AS
   $BODY$
   update attachments set creator = $2 where creator = $1;
   update attachments set lastmodifier = $2 where lastmodifier = $1;
   update content set creator = $2 where creator = $1;
   update content set lastmodifier = $2 where lastmodifier = $1;
   update content set username = $2 where username = $1;
   update content_label set owner = $2 where owner = $1;
   update label set owner = $2 where owner = $1;
   update likes set username = $2 where username = $1;
   update links set creator = $2 where creator = $1;
   update links set lastmodifier = $2 where lastmodifier = $1;
   delete from logininfo where username = $1;
   update logininfo set username = $2 where username = $1;
   update notifications set creator = $2 where creator = $1;
   update notifications set lastmodifier = $2 where lastmodifier = $1;
   update notifications set username = $2 where username = $1;
   update pagetemplates set creator = $2 where creator = $1;
   update pagetemplates set lastmodifier = $2 where lastmodifier = $1;
   update spacepermissions set creator = $2 where creator = $1;
   update spacepermissions set lastmodifier = $2 where lastmodifier = $1;
   update spacepermissions set permusername = $2 where permusername = $1;
   update spaces set creator = $2 where creator = $1;
   update spaces set lastmodifier = $2 where lastmodifier = $1;
   delete from user_mapping where user_key = $1 returning true;
   $BODY$
     LANGUAGE sql VOLATILE
     COST 100;
   ALTER FUNCTION "Repair_Users"(character varying, character varying)
     OWNER TO postgres;

   NOTE: it might be that other tables need to be updated in your case too. The tables above are the ones that were needed in my case. If more tables are affected, you will get an error message when trying to execute the function.

3. Create a new SQL Query that contains for every user something like this:

   select "Repair_Users"('ff80818144b6cc4c0144b6cc928d0021','ff808181476c32e601476dc57fb90006');

   where the first parameter is the OLD user_key and the second parameter is the NEW user key.

4. After you created this set of instructions, simply execute them.

5. Restart your postgreSQL server and start Confluence.

6. In Confluence, go to the Admin Interface -> Content Index and press Rebuild.

7. After the rebuild is completed, you should have no more "Unknown User (xxx)" in Confluence.