Remove directory results in "could not execute statement" error due to directory ID mismatch in cwd_user_attribute_directory
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
When attempting to remove a directory, the action fails with the following error.
2022-11-14 12:06:23,848 http-nio-8095-exec-243 ERROR [console.action.directory.RemoveDirectory] could not execute statement
org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`crowdhvm`.`cwd_user_attribute`, CONSTRAINT `fk_user_attribute_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `cwd_user` (`id`))
Diagnosis
Run the SQL query below and filter out results with NO
select DISTINCT (cu.id) as cwd_user_id, cu.directory_id as cwd_user_directory, cua.directory_id as cwd_user_attribute_directory,
CASE
WHEN cu.directory_id = cua.directory_id THEN 'YES'
ELSE 'NO'
END as "filter"
from cwd_user cu
inner join cwd_user_attribute cua on cua.user_id = cu.id 
Cause
User(s) records doesn't have the same directory_id in both cwd_user and cwd_user_attirbute tables for the same user_id
Solution
The solution requires database manipulation, hence always take a backup of the database and test the solution in a lower environment before implementing it on a production environment
- Stop Crowd
- Take a backup of the database
Execute the SQL query below:
update cwd_user_attribute set directory_id = <directory_id_from_cwd_user> where user_id = <user_id>The above queries are tested with the PostgreSQL database. You may need to modify the syntax of the queries as per your DBMS.
- Start Crowd
- Proceed to remove the directory