Enabling repository results in: pq: duplicate key value violates unique constraint "UQE_orgs_name"

[simmstein]

Component

server

Describe the bug

Hello,

Encountered an issue when attempting to add a new repository from a GitLab instance hosted on-premise. The error message received is as follows: pq: duplicate key value violates unique constraint "UQE_orgs_name". It works once time but I was the only one developer linked to this organization. Now we are more.

Context: The GitLab setup involves multiple organizations, and the bug manifests when trying to add a repository of the second organization.

Should be linked to #2092

We are owners of the organization.

System Info

Version: next-adb2c82790
Forge: Gitlab (on premise)
Deployment: docker swarm

Additional context

Good	Not good

Server log:

***redacted***_woodpeckerci_woodpecker-server.1.y8k5453nahp3@***redacted***    | {"level":"debug","ip":"***redacted***","latency":594.416323,"method":"POST","path":"/api/repos","status":500,"time":"2023-12-08T08:57:53Z","user-agent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/119.0","time":"2023-12-08T08:57:53Z","caller":"/woodpecker/src/github.com/woodpecker-ci/woodpecker/server/router/middleware/logger.go:59"}

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Checked that the bug isn't fixed in the next version already [https://woodpecker-ci.org/faq#which-version-of-woodpecker-should-i-use]
• Check that this is a concrete bug. For Q&A join our Discord Chat Server or the Matrix room.

[qwerty287]

Can you check the content of your DB? What's the data in the orgs.org_name column?

[simmstein]

It's sysadmin/sub-group. By renaming to sysadmin, it seems to work back but it's strange.

[anbraten]

That sounds link an issue with sub-groups. I will try to give it a look

[simmstein]

I don't if another commit fix this error but after upgrading to the latest version, It seems to work now.

[Lindenk]

I'm currently having this issue (I'm on the latest in the nixpkgs, v2.5.0). I also noticed that:

• Clicking on the Org in the header bar of a repo brings me to the wrong Org (ie. If I'm in the project org-name/project-name and click on the org-name in the header, it sends me to org-name/different-sub-group instead of org-name. The subgroup does exist in the repo but the project isn't in it)
• Secrets set on the above org are not accessible in the project, presumably because they're actually stored for org-name/subgroup and not org-name
• The only org name in the sqlite orgs table is set to org-name/subgroup even though the only repo added is in org-name not org-name/subgroup

[Lindenk]

Update: It appears that manually changing the database row does solve the issue. After modifying the row and adding the second repo, it appears the new repo is using the correct org as well

[pat-s]

likely fixed by #4255

[qwerty287]

Can someone verify this so we can close it?

[simmstein]

We moved to forgejo so I can't check it via a gitlab instance.

[pat-s]

Haven't encountered it in my instance since the fix -> closing.

[pat-s]

I just encountered this again adding repos on a new GL instance on most recent WP next :/

1. I added about 5 subgroup repos, all fine
2. Wanted to add a non-subgroup repo. This failed with

 duplicate key value violates unique constraint \"UQE_orgs_name

What is strange as the orgs table doesn't have the org in question listed.

Even when assuming that the owner parsing would result in a hickup WRT to subgroups, there would not be a match with an existing entry.

The only possibility is WP thinks the owner is my personal user (for which an org already exists) and not the GL organization which is the actual owner.

It would be good if WP could actually output the conflicting org for better debugging.

[zc-devs]

WP could output the conflicting org for better debugging

Meantime you can try to log queries.

[pat-s]

Ah nice, that helped indeed. In the INSERT INTO query I can see that it tries to use an org that already exists. This is super strange, as the org used in that query has nothing to do with the org from the repo in question which should be added.
So the org/owner parsing must be broken somewhere.

E.g. I am trying to add repo owner/repo1 (no subgroup) but the XORM INSERT statement tells me it is trying to insert owner/subgroup1 as the owner (which already exists and doesn't match with the actual owner).

Maybe it is querying the wrong repo ID and hence is trying to do a wrong insert.
Given that subgroups worked fine, it might be that there is a general issue with non-subgroup repos.

[pat-s]

• repo ID query works correctly
• There's an issue with querying existing orgs in the DB, i.e. I think partial matching is going on

[SQL] SELECT "id", "forge_id", "name", "is_user", "private" FROM "orgs" WHERE (name = $1) LIMIT 1 [$ORG] - 2.764476ms component=xorm                                                                                                                                                             
[SQL] INSERT INTO "orgs" ("forge_id","name","is_user","private") VALUES ($1,$2,$3,$4) RETURNING "id" [1 $ORG/$SUBGROUP false false]

In the above, the first query tries to look for a match with $ORG[] and returns the DB entry matching $ORG/$SUBGROUP. This is wrong (and causes the subsequent non-unique error), it should detect no match and create a new org in the DB instead.