Symptoms

After importing an XML backup into Confluence, user mentions will appear as a broken link for users that exist on both instances: 

Diagnosis

The problem is that the user keys associated to the user names are different between Confluence instances. User mentions using the old user keys will no longer work. To confirm that you are affected by this, please follow these steps:

1. On a page with broken mentions on the target instance, click . . . in the top right-hand corner, and select View Storage Format. In your storage format, any references to user mentions will look like this:

   <p><ac:link><ri:user ri:userkey="ff8080814ba236dc014ba236f4e40001" /></ac:link></p>
   <p><ac:link><ri:user ri:userkey="ff8080814ba236dc014ba236f4e60002" /></ac:link></p>

2. In the Confluence database on target instance:

   select * from user_mapping;

   Or, to view the user_key of the broken user to test:

    select * from user_mapping WHERE username='username';

3. You will receive a list that looks like this, with either one row for each user queried.

               user_key             | username | lower_username 
   
   ----------------------------------+----------+----------------
   ff8080814ba236dc014ba239c8eb000d | username | username
   
   (1 row)

4. Compare the user key with the one from the Storage Format. You will notice they are different.

Resolution

To resolve this problem, we are going to modify the Confluence pages with the affected macro and replace the old user keys with the new ones. To do this we will need to have access to the database on the source instance, and the target instance where it was restored.

1. Create a .csv file of the user_mapping table from the database of both the target and source instances. This is a example of how the content of the CSV file should look:

   ff80808147fe63530147fe6365d80001,username1,username1
   ff80808147fe63530147fe6365d90002,username2,username2
   ff80808147fe63530147fe6462e00004,username3,username3

2. Download the following script: fixUserKeySqlGenerator.py

   1. If you're on SQL Server, use the following script for the appropriate syntax: SQLSERVERfixUserKeySqlGenerator.py

3. Run the script, either from your local computer or the server. You may need to grant execution permission to the script to be able to run it.

   ./fixUserKeySqlGenerator.py Source_Instance_user_mapping.csv Target_Instance_user_mapping.csv

   The script will generate a list of SQL queries on the standard output. You will need to execute those queries in your new Confluence database to apply the changes.

4. Shutdown Confluence.

5. Create a backup of your data before proceeding.

6. Run the queries generated by the script in your Confluence database.
7. Start Confluence.
8. Check the broken pages, to ensure the script worked.
   
   
   

# ./fixUserKeySqlGenerator.py Source_Instance_user_mapping.csv Target_Instance_user_mapping.csv
  File "./fixUserKeySqlGenerator.py", line 19
    with open(sys.argv[1], mode='r') as OldUsersCsv:
            ^
SyntaxError: invalid syntax

 # python2.6 fixUserKeySqlGenerator.py Source_Instance_user_mapping.csv Target_Instance_user_mapping.csv

# python2.6 fixUserKeySqlGeneratornew.py Source_Instance_user_mapping.csv Target_Instance_user_mapping.csv
Traceback (most recent call last):
  File "fixUserKeySqlGeneratornew.py", line 21, in <module>
    oldUsers = dict((rows[2].strip(),rows[0]) for rows in oldUsersFileReader)
  File "fixUserKeySqlGeneratornew.py", line 21, in <genexpr>
    oldUsers = dict((rows[2].strip(),rows[0]) for rows in oldUsersFileReader)
IndexError: list index out of range