Remove directory results in "could not execute statement" error due to directory ID mismatch in cwd_user_attribute_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
When attempting to remove a directory, the action fails with the following error:
org.hibernate.exception.ConstraintViolationException: could not execute statement
1
2
3
4
5
6
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
1
2
3
4
5
6
7
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 don'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:
1update 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.
Was this helpful?