Exception when removing external user directory
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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
Enable SQL logging on Confluence.
Attempt to remove the external user directory.
Trace the
logExceptionsfrom the detailed SQL logging.Look for the binding parameter for the offending user entry from the
cwd_usertable. In the following example, the offending id is1023456.1 2 3 4 52020-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 founAttempt to remove users from the database directly using the following SQL queries:
1 2 3 4DELETE 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
1023456with the offending id retrieved from the detailed SQL logging.Try to remove the external user directory again.
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:
1
2
3
4
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.
1
SELECT * FROM cwd_directory;Once you've executed the SQL queries above successfully, please delete the user directory from the UI.
Was this helpful?