Cannot Delete a User From Crowd due to Foreign Key Constraint Failure
- Trying to deleting a user from Crowd results in a "System Error" with a stack trace
The following may appear on screen as part of the stack trace:
Transaction is already completed - do not call commit or rollback more than once per transaction
The following appears in the
2012-02-22 17:05:17,896 TP-Processor5 INFO [atlassian.xwork.interceptors.TransactionalInvocation] Invoking rollback for transaction on action '/console/secure/user/remove.action (RemovePrincipal.default())' due to throwable: org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update ... Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`auth`.`cwd_user_credential_record`, CONSTRAINT `fk_user_cred_user` FOREIGN KEY (`user_id`) REFERENCES `cwd_user` (`id`))
This may be due to a bug caused by the user's password being reset more times than the value set in the directory's Password History Count configuration. This setting enforces a rule during password changes that new passwords must not match the previous N number of old passwords.
The bug is that all password reset attempts past the set value will erroneously add an extra row in cwd_user_credential_record with the list_index value of zero. The result is multiple rows for the same user with list_index of zero. When attempting to remove this user from Crowd, these additional rows are not removed, causing constraint violation of fk_user_cred_user, in which cwd_user_credential_record.user_id references cwd_user.id.
- Within Crowd check out your user directory's configurations via: Directories > View > Configuration.
- Verify that there is a non-zero value set for Password History Count. (A value of zero would indicate new passwords can be the same as old passwords.)
Run the following SQL query against your Crowd database to identify any users that are running into this problem:
SELECT user_id, count(*) FROM cwd_user_credential_record WHERE list_index = 0 GROUP BY user_id, list_index HAVING COUNT(*) > 1;
If the user you are having trouble deleting does not show up in the above query, you are running into some other problem. Do not proceed with the rest of this article.
A workaround is to manually remove the user's records in cwd_user_credential_record via the DB before attempting to delete the user from the Crowd UI, where <user_name> is the user you want to remove.
As a general precaution for any direct database manipulation, we strongly recommend that you first take a back up of the database
DELETE FROM cwd_user_credential_record WHERE user_id IN (SELECT id FROM cwd_user WHERE lower_user_name = '<user_name>');
If you do not require new passwords to be different from previous passwords, you can set Password History Count to zero to prevent this from happening in the future.