Saving a draft with externalId as a draft gives 500

[dpancic]

In GitLab by @KlausIllmayer on Dec 5, 2022, 17:42

🐛 Bug Report

Creating a draft item having an externalId and then again saving it as a draft gives a 500 error.

🤔 Expected Behavior

No 500 error, item should be saved again as a draft.

😯 Current Behavior

It gives a 500 error could not execute statement; SQL [n/a]; constraint [item_external_ids_identifier_service_code_identifier_item_i_key]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement.

🧭 How to reproduce

1. Login
2. Create a new item, e.g. a tool
3. Enter a title, a description and an externalId, e.g. Id service "Wikidata" and Identifier "1234"
4. Save as draft
5. You will get a green successfully saved message and you stay in the edit form, now again "Save as draft" (you do not need to change anything)
6. You will get a "500 - Internal Server Error"

Observations

In the database backend raises this sql-error:

DETAIL:  Key (identifier_service_code, identifier, item_id)=(Wikidata, 1234, 48697) already exists.
STATEMENT:  insert into item_external_ids (identifier, identifier_service_code, item_id, id) values ($1, $2, $3, $4)

Not sure, why this happens. There is clearly only one externalId set (tried it out with direct API calls having only one externalId) - but looking at the sql-error, it shouldn't do an insert but instead an update (or before delete the externalIds).

---

[dpancic]

In GitLab by @KlausIllmayer on Dec 5, 2022, 18:02

Thanks to @laureD19 I looked again into the issue and I had a wrong observation. It is not connected to the frontend and the additional persistentId-parameter does not have an effect. Sorry about that, will move it to the backend issue list.

[dpancic]

In GitLab by @KlausIllmayer on Dec 5, 2022, 18:02

moved from sshoc-marketplace-frontend#136

[KlausIllmayer]

@tparkola This error now pops up very often, due to the recommended workflow we give to people. Every time you have an externalId defined in your item, storing it as draft and either update the draft or try to publish it will lead to a 500. The problem seems to be the constraint item_external_ids_identifier_service_code_identifier_item_i_key on table item_external_ids - as a draft is not changing its id and if publishing it first it will also take the id, it could be, that this is the issue of the constraint. Anyway, it would imply that when saving it a second time it tries first to create a new entry in the item_external_ids-table instead of deleting the old entry before (or doing an update) - not sure, if this makes sense.

[KlausIllmayer]

That means that most probably the issue was not fixed, depending on the date of the fix - need to check it on develop

[laureD19]

Tested on the dev instance. Still get a 500.

[KlausIllmayer]

Unfortunately, there is now a new problem. The workflow described in the first post of this issue now does not give anymore a 500 error, but it also deletes the externalId. How to reproduce on frontend:

1. Login
2. Create a new item, e.g. a tool
3. Enter a title, a description and an externalId, e.g. Id service "Wikidata" and Identifier "1234"
4. Save as draft
5. You will get a green successfully saved message and in the edit forms it looks like if everything is fine, especially the externalId stays. But if you leave the draft view, click somewhere else and then go back into the draft version, you will see that loading it again now does not have the externalId anymore. It is suddenly gone. Strangely if you again add the externalId and now save the draft again, it stays. So it seems to me, that the problem only occurs for freshly created items.

I've also tried it with pure API calls:

1. POST /api/auth/sign-in - signing in as internal user
2. POST /api/tools-services?draft=true - creating a draft version of a tool, I've used this: { "label": "test if draft creates externalId #173", "description": "like to see the external id", "externalIds": [ { "identifierService": { "code": "GitHub" }, "identifier": "home/common" } ] }
3. The response body says, that an externalId was applied to the new tool, but when calling GET /api/tools-services/{persistentId}?draft=true the externalId does not show up anymore, which is not expected.

Interesting, this only occurs for drafts. If you create a new tool with an externalId and publish it directly, the externalId shows up and is not lost.

[tparkola]

True, fix for that is ready (see https://github.com/SSHOC/sshoc-marketplace-backend/pull/386).

[KlausIllmayer]

Cool, works now - thanks for fixing!