Duplicate entity created (unique restraint ignored)

Support
1

I have…

I’m submitting a…

  • [ ] Regression (a behavior that stopped working in a new release)
  • [x] Bug report
  • [ ] Performance issue
  • [ ] Documentation issue or request

Current behavior

Our content editor managed to make a user with duplicate orcids and emails within our cloud hosted app (asap-hub). She mentioned that she told the UI not to update after saving as she has had problems with this before. Here is her bug report.

This morning I created a new user Pawan removed and when I hit save, Squidex asked if I wanted to reload content. I said no - because of past behavior that it overwrites the connection code being created. (This was a bug reported previously, and the response was to refresh squidex - but it still reoccurs from time to time),

Then there were TWO Pawan removed created (see screenshot). Obviously this is an issue.

I then moved one to draft -and sent Pawan an email to disregard one email and use the other one so that there wasn’t confusion. I clearly did not input Pawan’s name in twice in a row :-p.

The one that is in draft now is causing problems because it now is flagging that there is the same email and ORCID when I try to remove the connection - so I replaced the email with a bogus one, and removed ORCID and connection so that I could save it as a draft - but that you can at least have the page to figure out what happened:

Squidex Headless CMS

image
image1694×295 39.7 KB

Expected behavior

It should not be possible to create two users with the same email and orcid.

Minimal reproduction of the problem

I can only report what my end user has reported above. I wonder if its having a user as draft which ignores constraints and then making that user live or something. It seems weird.

Environment

  • [ ] Self hosted with docker
  • [ ] Self hosted with IIS
  • [ ] Self hosted with other version
  • [x] Cloud version

:grinning:

2

I do not see any unique constraints in the schema. Have you removed them?

3

Thats a little worrying

image
image2542×1136 187 KB

4

Oh, I had a look to your asap-hub-dev app I think.

In general the unique constraints is only a validation. There is no guarantee that duplications will never happen, because this would require some kind of a global lock, which hurts performance.

I can have a closer look why is happened in your case, but I need a reproducible example.

5

to reproduce simply create the same record really quickly :smiley: its how we got into this situation.

As for the performance - it would only really affect the performance on an insert operation, right?

If the currently available constraints are only really done as validation (not exactly sure what you mean by that though) - does this mean that squidex has no real enforceable constraints for the stored data?

6

Yes, exactly. In a “normal” database you would add a unique constraint and then it would be checked at a database level. But even then there is not guarantee in scenarios with multiple database servers. In this case it is only tested at insert level.