Summary

External user directory cannot be removed due to users deletion failure from database. User deletion failure is typically caused by referenced object from another table or integrity constraint.

Environment

• Confluence Server
• Confluence Data Center

Diagnosis

1. Enable SQL logging on Confluence.
2. Attempt to remove the external user directory.

3. Trace the logExceptions from the detailed SQL logging.

4. Look for the binding parameter for the offending user entry from the cwd_user table. In the following example, the offending id is 1023456.

   2020-03-01 18:12:18,015 DEBUG [http-nio-8090-exec-2] [org.hibernate.SQL] logStatement delete from cwd_user where id=?
   2020-03-01 18:12:18,016 TRACE [http-nio-8090-exec-2] [type.descriptor.sql.BasicBinder] bind binding parameter [1] as [BIGINT] - [1023456]
   2020-03-01 18:12:18,020 WARN [http-nio-8090-exec-2] [engine.jdbc.spi.SqlExceptionHelper] logExceptions SQL Error: 2292, SQLState: 23000
    -- referer: http://example.confluence.com:8090/plugins/servlet/embedded-crowd/directories/list | url: /plugins/servlet/embedded-crowd/directories/remove | traceId: 59839f8824650246 | userName: admin
   2020-03-01 18:12:18,023 ERROR [http-nio-8090-exec-2] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ORA-02292: integrity constraint (CONFLUENCE_B.FK_CHILD_USER) violated - child record foun

5. Attempt to remove users from the database directly using the following SQL queries:

   DELETE FROM cwd_user_attribute WHERE user_id = 1023456;
   DELETE FROM cwd_user_credential_record WHERE user_id = 1023456;
   DELETE FROM cwd_membership WHERE child_user_id = 1023456;
   DELETE FROM cwd_user WHERE id = 1023456;

    Please replace the 1023456 with the offending id retrieved from the detailed SQL logging.

6. Try to remove the external user directory again.
7. If this does not work, please follow the workaround under the Solution section below.

Cause

Data is still referencing on the user entry in the cwd_user from a different table.

Solution

 It is extremely important that you take a backup of your production database prior to making any changes with the following SQL queries:

DELETE FROM cwd_user_attribute WHERE user_id IN (SELECT id FROM cwd_user WHERE directory_id = XXXXXX);
DELETE FROM cwd_user_credential_record WHERE user_id IN (SELECT id FROM cwd_user WHERE directory_id = XXXXXX);
DELETE FROM cwd_membership WHERE child_user_id IN (SELECT id FROM cwd_user WHERE directory_id = XXXXXX);
DELETE FROM cwd_user WHERE directory_id = XXXXXX;

 Please replace the 'XXXXXX' with the respective external user directory you're trying to remove. You may use the following SQL query to determine the user directory id.

SELECT * FROM cwd_directory;

Once you've executed the SQL queries above successfully, please delete the user directory from the UI.