User mentions appear as Broken link after XML import

Symptoms

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

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:

   1 2 <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:

   1 select * from user_mapping;

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

   1  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.

   1 2 3 4 5 6 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:

   1 2 3 ff80808147fe63530147fe6365d80001,username1,username1 ff80808147fe63530147fe6365d90002,username2,username2 ff80808147fe63530147fe6462e00004,username3,username3

2. Copy the following code block in to file

   fixUserKeySqlGenerator.py

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 #!/usr/bin/python import csv import sys # This script generate the SQL queries needed to workaround https://jira.atlassian.com/browse/CONF-34994 # Input is the CSV files with the content of the table user_mapping in the old and the new databases # The SQL queries are written in the standard output SQL_PART_1 = 'UPDATE bodycontent SET body = REPLACE(body, \'ri:user ri:userkey=\"' SQL_PART_2 = '"\', \'ri:user ri:userkey="' SQL_PART_3 = '"\') WHERE contentid IN (SELECT contentid FROM content WHERE contenttype IN (\'PAGE\', \'BLOGPOST\') AND ((prevver IS NULL AND content_status = \'current\') or (content_status = \'draft\'))) AND body LIKE \'%ri:user ri:userkey="' SQL_PART_4 = '"%\';' if len(sys.argv) != 3: print "ERROR: wrong number of parameters.\nUsage:\n\tfixUserKeySqlGenerator.py old_user_mapping.csv new_user_mapping.csv\n" sys.exit(0) # Read usernames from old user mapping file and create a hash with the username as the key with open(sys.argv[1], mode='r') as OldUsersCsv: oldUsersFileReader = csv.reader(OldUsersCsv) oldUsers = dict((rows[2].strip(),rows[0]) for rows in oldUsersFileReader) # Read content from new user mapping hash with open(sys.argv[2], mode='r') as NewUsersCsv: reader = csv.reader(NewUsersCsv) for row in reader: # Check if the user exist in the old user mapping if (oldUsers.has_key(row[2].strip())): print SQL_PART_1 + oldUsers[row[2]] + SQL_PART_2 + row[0] + SQL_PART_3 + oldUsers[row[2]] + SQL_PART_4

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

      SQLSERVERfixUserKeySqlGenerator.py

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 #!/usr/bin/python import csv import sys # This script generate the SQL queries needed to workaround https://jira.atlassian.com/browse/CONF-34994 # Input is the CSV files with the content of the table user_mapping in the old and the new databases # The SQL queries are written in the standard output SQL_PART_1 = 'UPDATE dbo.BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as NVarchar(MAX)), \'ri:user ri:userkey=\"' SQL_PART_2 = '"\', \'ri:user ri:userkey="' SQL_PART_3 = '"\') AS NText) WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE IN (\'PAGE\', \'BLOGPOST\') AND PREVVER IS NULL AND CONTENT_STATUS = \'current\') AND BODY LIKE \'%ri:user ri:userkey="' SQL_PART_4 = '"%\';' if len(sys.argv) != 3: print "ERROR: wrong number of parameters.\nUsage:\n\tfixUserKeySqlGenerator.py old_user_mapping.csv new_user_mapping.csv\n" sys.exit(0) # Read usernames from old user mapping file and create a hash with the username as the key with open(sys.argv[1], mode='r') as OldUsersCsv: oldUsersFileReader = csv.reader(OldUsersCsv) oldUsers = dict((rows[2].strip(),rows[0]) for rows in oldUsersFileReader) # Read content from new user mapping hash with open(sys.argv[2], mode='r') as NewUsersCsv: reader = csv.reader(NewUsersCsv) for row in reader: # Check if the user exist in the old user mapping if (oldUsers.has_key(row[2].strip())): print SQL_PART_1 + oldUsers[row[2]] + SQL_PART_2 + row[0] + SQL_PART_3 + oldUsers[row[2]] + SQL_PART_4

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.

   1 ./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.

This script requires Python 2.6 or higher. If you run into an error, you may need to run using specifically python2.6:

Thus:

If you run into the below error, check for empty lines in the .csv files and remove it before running the script again.