Cannot Delete a User From Crowd due to Foreign Key Constraint Failure

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

症状

  1. Trying to deleting a user from Crowd results in a "System Error" with a stack trace
  2. 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
    

atlassian-crowd.log に次のメッセージが表示される。

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.

Technical details:

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.

Please see the following bug report additional details:  CWD-1979 - Getting issue details... STATUS

診断

  1. Within Crowd check out your user directory's configurations via: Directories > View > Configuration.
  2. 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.)
  3. 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.

(warning) As a general precaution for any direct database manipulation, we strongly recommend that you first take a back up of the database (warning)

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.

ソリューション

There is currently no permanent fix for this bug. Please keep an eye on the bug report itself CWD-1979 - Getting issue details... STATUS for updates.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Last modified on Mar 30, 2016

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.