Migrating Confluence users from one AD to another

[Derek Fields _RightStar_]

We are migrating users from one AD instance to another. The catch is that the usernames are changing as part of this migration. JDoe changes to john.doe for instance. 

I was able to update all of the database references to substitute the new userkey (from user_mapping table) for the old userkey. 

The problem is in the @mentions - these are embedded in the body content. When I update these userkeys, Confluence complains that the user is unknown. I have tried flushing the cache and restarting confluence to see if that fixes it, but it does not.

I am aware of the kb article https://confluence.atlassian.com/confkb/how-to-rename-the-user-unknown-user-as-the-content-creator-caused-by-user-deletion-740001947.html

It appears the recreating the old usernames in the internal Confluence directory works but it is really ugly to have a set of inactive users just to make the @mentions map to the new users. 

Any other ideas as to how to address this problem? Why doesn't Confluence pick up the new usernames from the embedded userkey?

[MoroSystems Support · Accepted solution]

Hello Derek,

the migration you are describing is something we have already done for one of our customers. I think that the nicest way to fix the mentions is to run SQL query which will replace old usernames for new ones. You need to search for specific syntax of your database to replace string occurrences. For MySQL the script looked something like that:

UPDATE BODYCONTENT SET BODY = REPLACE(BODY, 'ri:userkey="402802055438103c015438b5916c0003"', 'ri:userkey="402802055579275001559c58f4bf000e"') WHERE BODY LIKE '%ri:userkey="402802055438103c015438b5916c0003"%';

Regards,
Martin

[MoroSystems Support]

This also fixes all other macros (Gliffy, etc.) as the ri:userkey= is common syntax of macros which are saving user keys.

[Derek Fields _RightStar_]

We did something very similar to this. However, the macros seem to complain that the old username isn't found, even though we changed the userkeys. When we create the old username in the database, it seems to update properly. 

After you did the database update, did you have to refresh the cache or do anything else to synchronize?

[MoroSystems Support]

I don't recall having any problems with the update. I am however sure we always done any DB updates while Confluence was down and after boot up refreshed the cache. I know we were doing this just to be sure.

One more thing that we did was that when we were updating user_mapping table we also updated all other tables that are connected to it and make use of user_key. Have you done this as well? The list of constraints for 5.9.7 was as follows:

+----------------------+--------------+--------------------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+----------------------+--------------+--------------------------------+-----------------------+------------------------+
| CONTENT | CREATOR | FK_CONTENT_CREATOR | user_mapping | user_key |
| CONTENT | LASTMODIFIER | FK_CONTENT_LASTMODIFIER | user_mapping | user_key |
| CONTENT | USERNAME | FK_CONTENT_USERNAME | user_mapping | user_key |
| CONTENT_LABEL | OWNER | FK_CONTENT_LABEL_OWNER | user_mapping | user_key |
| CONTENT_PERM | CREATOR | FK_CONTENT_PERM_CREATOR | user_mapping | user_key |
| CONTENT_PERM | LASTMODIFIER | FK_CONTENT_PERM_LASTMODIFIER | user_mapping | user_key |
| CONTENT_PERM | USERNAME | FK_CONTENT_PERM_USERNAME | user_mapping | user_key |
| CONTENT_RELATION | CREATOR | FK_C2CRELATION_CREATOR | user_mapping | user_key |
| CONTENT_RELATION | LASTMODIFIER | FK_C2CRELATION_LASTMODIFIER | user_mapping | user_key |
| EXTRNLNKS | CREATOR | FK_EXTRNLNKS_CREATOR | user_mapping | user_key |
| EXTRNLNKS | LASTMODIFIER | FK_EXTRNLNKS_LASTMODIFIER | user_mapping | user_key |
| FOLLOW_CONNECTIONS | FOLLOWEE | FK_FOLLOW_CONNECTIONS_FOLLOWEE | user_mapping | user_key |
| FOLLOW_CONNECTIONS | FOLLOWER | FK_FOLLOW_CONNECTIONS_FOLLOWER | user_mapping | user_key |
| LABEL | OWNER | FK_LABEL_OWNER | user_mapping | user_key |
| LIKES | USERNAME | FK_LIKES_USERNAME | user_mapping | user_key |
| LINKS | CREATOR | FK_LINKS_CREATOR | user_mapping | user_key |
| LINKS | LASTMODIFIER | FK_LINKS_LASTMODIFIER | user_mapping | user_key |
| NOTIFICATIONS | CREATOR | FK_NOTIFICATIONS_CREATOR | user_mapping | user_key |
| NOTIFICATIONS | LASTMODIFIER | FK_NOTIFICATIONS_LASTMODIFIER | user_mapping | user_key |
| NOTIFICATIONS | USERNAME | FK_NOTIFICATIONS_USERNAME | user_mapping | user_key |
| PAGETEMPLATES | CREATOR | FK_PAGETEMPLATES_CREATOR | user_mapping | user_key |
| PAGETEMPLATES | LASTMODIFIER | FK_PAGETEMPLATES_LASTMODIFIER | user_mapping | user_key |
| SPACEPERMISSIONS | PERMUSERNAME | FK_SPACEPERMISSIONS_PERMUSERNA | user_mapping | user_key |
| SPACEPERMISSIONS | CREATOR | FK_SPACEPERMISSIONS_CREATOR | user_mapping | user_key |
| SPACEPERMISSIONS | LASTMODIFIER | FK_SPACEPERMISSIONS_LASTMODIFI | user_mapping | user_key |
| SPACES | CREATOR | FK_SPACES_CREATOR | user_mapping | user_key |
| SPACES | LASTMODIFIER | FK_SPACES_LASTMODIFIER | user_mapping | user_key |
| TRACKBACKLINKS | CREATOR | FK_TRACKBACKLINKS_CREATOR | user_mapping | user_key |
| TRACKBACKLINKS | LASTMODIFIER | FK_TRACKBACKLINKS_LASTMODIFIER | user_mapping | user_key |
| USERCONTENT_RELATION | SOURCEUSER | FK_RELATION_U2CUSER | user_mapping | user_key |
| USERCONTENT_RELATION | CREATOR | FK_U2CRELATION_CREATOR | user_mapping | user_key |
| USERCONTENT_RELATION | LASTMODIFIER | FK_U2CRELATION_LASTMODIFIER | user_mapping | user_key |
| USER_RELATION | CREATOR | FK_U2URELATION_CREATOR | user_mapping | user_key |
| USER_RELATION | SOURCEUSER | FK_RELATION_U2USUSER | user_mapping | user_key |
| USER_RELATION | TARGETUSER | FK_RELATION_U2UTUSER | user_mapping | user_key |
| USER_RELATION | LASTMODIFIER | FK_U2URELATION_LASTMODIFIER | user_mapping | user_key |
| logininfo | USERNAME | FK_logininfo_USERNAME | user_mapping | user_key |
+----------------------+--------------+--------------------------------+-----------------------+------------------------+

Martin

[Derek Fields]

Yes, we did all of those updates. It turns out that after rebuilding the indexes, things started to look correct. Thanks again for the feedback