jira user attributes duplication

[DIGIT CITnet]

We use Jira 5.2.11 and Crowd 2.6.4. Jira delegates the user management to Crowd.

Our jira instance have duplicated records within the table cwd_user_attributes.

SELECT * FROM cwd_user_attributes WHERE user_id = 1234;

returns

ID,user_id,directory_id,"attribute_name","attribute_value","lower_attribute_value"
7399929,1234,2,"login.count","39","39"
7399930,1234,2,"login.count","39","39"
7399923,1234,2,"login.currentFailedCount","0","0"
7399924,1234,2,"login.currentFailedCount","0","0"
5343116,1234,2,"login.lastFailedLoginMillis","1360315006292","1360315006292"
7399925,1234,2,"login.lastLoginMillis","1370423681199","1370423681199"
7399926,1234,2,"login.lastLoginMillis","1370423681201","1370423681201"
7399927,1234,2,"login.previousLoginMillis","1369815841774","1369815841774"
7399928,1234,2,"login.previousLoginMillis","1369815841774","1369815841774"
5343118,1234,2,"login.totalFailedCount","3","3"

As you can see, we have multiple entries for some attributes!

Is-it a known bug?

bruno

[DIGIT CITnet]

The ID is the cwd_user_attribute primary key.

The problem is that we have more than one record for some attribute. For example, it is not good to have 2 'login.count' attributes for the same userid within the same directory_id.

Another interesting example shows 0 or 1 millisecond differences for the lastLoginMillis attribute:

"attribute_name"	"attribute_value"
"login.count"	"14"
"login.count"	"14"
"login.count"	"14"
"login.count"	"14"
"login.currentFailedCount"	"0"
"login.currentFailedCount"	"0"
"login.currentFailedCount"	"0"
"login.currentFailedCount"	"0"
"login.currentFailedCount"	"0"
"login.lastLoginMillis"	"1369910894581"
"login.lastLoginMillis"	"1369910894582"
"login.lastLoginMillis"	"1369910894582"
"login.lastLoginMillis"	"1369910894582"
"login.lastLoginMillis"	"1369910894583"
"login.previousLoginMillis"	"1369910894518"
"login.previousLoginMillis"	"1369910894518"
"login.previousLoginMillis"	"1369910894518"
"login.previousLoginMillis"	"1369910894518"

[DIGIT CITnet]

select * from cwd_user_attributes where directory_id not in (select id from cwd_directory);

does not return any record.

[Norman Abramovitz]

Hmm, There are some issues with crowd creating duplicate entries but those issues require existing Jira users with the same name or users in multiple directories. Your ID values does not suggest that behavior.

[DIGIT CITnet]

The join between cwd_user and cwd_user_attribute is done via the userid and not the username.

It's perhaps a good idea to have a constrain based on userid, directory_id and attribute_name

[Norman Abramovitz]

Understand, but we are not looking for missing users but missing directory records.

If you get back records then you have missing directory entries. If you do not get back records then you have users defined with multiple directories which could be a different problem.

[Norman Abramovitz]

Check out this knowledge base article.

https://confluence.atlassian.com/display/JIRAKB/Upgrade+to+JIRA+6x+fails+due+to+duplicate+entry+into+app_user+table

[DIGIT CITnet]

Hi Norman,

Thanks for your reply but in my case, it is something different.

Our jira contains 2 directory: the internal one and crowd.

SELECT DISTINCT directory_id FROM cwd_user_attributes;

returns 1 and 2 thus it is not linked with a deleted direcory :-(

Something else ;-)

bruno

[Norman Abramovitz]

Did you check for user names with a space at the end?

[Norman Abramovitz]

Your query does not look correct for detecting removed directories. Try this query if I am correct.

select * from cwd_user_attributes where directory_id not in (select id from cwd_directory);

Any results means you have deleled entries.