Problem

External directory fails to synchronize with Confluence.

The following appears in the atlassian-confluence.log

2017-04-20 16:20:26,901 ERROR [Caesium-1-2] [atlassian.confluence.event.ConfluenceEventDispatcher] run There was an exception thrown trying to dispatch event [com.atlassian.crowd.event.user.UserEditedEvent@35de7957] from the invoker [com.atlassian.confluence.event.ConfluenceListenerHandlersConfiguration$TimingListenerHandler$1$1@3f8cf28]
java.lang.RuntimeException: Unable to find user mapping for charlie

Diagnosis

For every entry in cwd_user, there should be one entry with a matching lower user name in the user_mapping table. However, for the user in this error, the entry is missing. In the example above, I'm using 'charlie' as the user that is affected.

Scenario 1

Run the following query to find any users that are affected by this:

SELECT * FROM cwd_user WHERE lower_user_name NOT IN (SELECT lower_username FROM user_mapping);

If you get results for charlie, proceed with Resolution 1 below.

Scenario 2

If the query above doesn't return any results, run the query below based on the username you see in the error message in the logs (e.g. charlie) and ensure you insert the username in lowercase.

SELECT * FROM user_mapping WHERE lower_username like '%charlie%' or lower(username) like '%charlie%'; 

If you get results returned where charlie has a mismatched username and lower_username, then proceed with Resolution 2. Here's an example of such output:

Resolution

Resolution 1

1. Shutdown Confluence and backup your database

2. Remove the user(s) from the database:

   SELECT id FROM cwd_user WHERE lower_user_name = 'charlie';
   /* Use the ID value from this SELECT query to run the next set of queries */
    
   DELETE FROM cwd_user_credential_record WHERE user_id = '<id_from_select_query>';
   DELETE FROM cwd_membership WHERE child_user_id = '<id_from_select_query>';
   DELETE FROM cwd_user_attribute WHERE user_id = '<id_from_select_query>';
   DELETE FROM cwd_user WHERE id = '<id_from_select_query>';

3. Start Confluence and trigger a sync again

Resolution 2

1. Shutdown Confluence and backup your database

2. Run the query below to update the user's record so that the username and lower_username match:

   UPDATE user_mapping SET lower_username = 'charlie' WHERE user_key = '2c97819f6532baa5016538bf211b0004';

3. Start Confluence and trigger a sync again