bulk change of attributes using REST leads to server error 500

[John O]

Hello community,

may I explain what happened and ask you for help:

I set up a Jira instance, using a Postgresql database.
I created customized issue types, custom fields, customized workflows and links.
I created numerous issues, attributed them, linked them, everything worked fine.

Now I tried to enter a bunch of attributes, existing in CSV format.
So I used a few Powershell lines, which
- read the key-value pairs K, V from CSV
- for each line:
- look for issues with custom_attribute_1 = K using JQL query
- if exactly one issue was found:
- if custom_attribute_2 <> V, update custom_attribute_2 with V via a REST put

This worked fine for 2 and for 10 lines. I decided to run the script for some 100 entries.
(Note to myself: back up database before)

About 100 entries were processed correctly, then an internal server error 500 was reported.

Since this moment, my Jira server instance is unusable.
I shut down Jira, rebooted server (incl. database), restarted Jira.
Trying to connect, I see the login page, after login I get (translated into english):

Technical Details

Log Number: 73a7b7c5-***-7f5f85caaf74
Reason

URL: http://myjira:8080/login.jsp?permissionViolation=true&os_destination=%2Fbrowse%2FJOHN-1&page_caps=&user_role=

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:UserAttribute][name,lastAuthenticated][directoryId,1][id,10103][lowerValue,1596176734128][userId,10000][value,1596176734128] (SQL Exception while executing the following:UPDATE public.cwd_user_attributes SET user_id=?, directory_id=?, attribute_name=?, attribute_value=?, lower_attribute_value=? WHERE ID=? (ERROR: duplicate key violates Unique-Constraint »pk_cwd_user_attributes«
Detail: Key »(id)=(10103)« already exists.))

Any clue, how to repair this?
Is there something like a database health check (from command line)?

In my naive view this violation can not exist in database - so how to prevent Jira from trying this?

Thanks a lot for any hint - John

[John O · Accepted solution]

OK, solved it myself.

In fact, there were four duplicate entries in postgresql, table cwd_user_attributes, id = 1010[1234], for my user id.

I deleted them via postgresql console and Jira runs again.

Scary anyway - the REST fire apparently confused the login process.. How to avoid this in production?

Thank you - John