PUT /actors/:id throws sql unique constraint error when updating actors with external ids
when updating an actor with externalIds
via PUT /actors/:id
, this currently throws the following error:
server logs
psql_1 | ERROR: duplicate key value violates unique constraint "actor_external_ids_identifier_service_code_identifier_actor_key"
psql_1 | DETAIL: Key (identifier_service_code, identifier, actor_id)=(ORCID, 12345, 5) already exists.
psql_1 | STATEMENT: insert into actor_external_ids (actor_id, identifier, identifier_service_code, id) values ($1, $2, $3, $4)
marketplace_1 | 11-05-2021 16:30:30.489 [http-nio-8080-exec-7] WARN o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 0, SQLState: 23505
marketplace_1 | 11-05-2021 16:30:30.489 [http-nio-8080-exec-7] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - ERROR: duplicate key value violates unique constraint "actor_external_ids_identifier_service_code_identifier_actor_key"
marketplace_1 | Detail: Key (identifier_service_code, identifier, actor_id)=(ORCID, 12345, 5) already exists.
marketplace_1 | 11-05-2021 16:30:30.491 [http-nio-8080-exec-7] ERROR e.s.m.c.MarketplaceExceptionHandler.handleServerError - Runtime exception
marketplace_1 | org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [actor_external_ids_identifier_service_code_identifier_actor_key]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
to reproduce:
curl "http://localhost:8080/api/actors/5" | jq
{
"affiliations": [
{
"affiliations": [],
"email": null,
"externalIds": [],
"id": 3,
"name": "SSHOC project consortium",
"website": "https://sshopencloud.eu/"
}
],
"email": "john@example.com",
"externalIds": [],
"id": 5,
"name": "John Smith",
"website": "https://example.com/"
}
curl "http://localhost:8080/api/actors/5" -X PUT -H "Content-Type: application/json" -H "Authorization: Bearer ..." -d '
{
"affiliations": [{"id": 3}],
"email": "john@example.com",
"externalIds": [{"identifier":"12345","identifierService":{"code":"ORCID"}}],
"name": "John Smith",
"website": "https://example.com/"
}
' | jq
{
"affiliations": [
{
"affiliations": [],
"email": null,
"externalIds": [],
"id": 3,
"name": "SSHOC project consortium",
"website": "https://sshopencloud.eu/"
}
],
"email": "john@example.com",
"externalIds": [
{
"identifier": "12345",
"identifierService": {
"code": "ORCID",
"label": "ORCID"
}
}
],
"id": 5,
"name": "John Smith",
"website": "https://example.com/"
}
- repeat step 2
curl "http://localhost:8080/api/actors/5" -X PUT -H "Content-Type: application/json" -H "Authorization: Bearer ..." -d '
{
"affiliations": [{"id": 3}],
"email": "john@example.com",
"externalIds": [{"identifier":"12345","identifierService":{"code":"ORCID"}}],
"name": "John Smith",
"website": "https://example.com/"
}
' | jq
{
"error": "could not execute statement; SQL [n/a]; constraint [actor_external_ids_identifier_service_code_identifier_actor_key]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
"status": 500,
"timestamp": "2021-05-11 16:29:02"
}
Edited by Stefan Probst