Confluence users unknown after Crowd migration

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!

3 answers

1 accepted

0 votes
Accepted answer

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.

I'm having the same prob here, except for nothing changed after I reindexed everything. It will say "Unknown User"(username) the user names are all correct and if you hover on the user you can see the correct name.

0 votes
Wayne Wong Atlassian Team Jul 28, 2014

Hi Mircea,

Have try to reindex see if the issue persists?

https://confluence.atlassian.com/display/DOC/Content+Index+Administration

Also, have you try to search those "unknow" user? could you find them on user management?

Thank you.

Regards,


Wayne Wong

Hi Wayne,

Content index is at 100% and last refreshed 1 hour ago. The users do not exist anymore (as they were renamed). I only see the new usernames that the users have now, the old ones are obviously gone. That is also the issue here.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,176 views 20 14
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you