Cannot enable new repos after upgrading from v3.1.0 to v3.2.0. Duplicate entry for key 'UQE_orgs_name'

[DHandspikerWade]

Component

server

Describe the bug

After upgrading to 3.2.0, I am no longer able to enable new repos in the server dashboard. Attempting results in a error notification and the message "Error 1062 (23000): Duplicate entry 'devin' for key 'UQE_orgs_name'" being written to the server logs with "devin" being admin and only user. Also happens in the current next version.

Rolling back to 3.1.0, repos can be enabled without issue.

Steps to reproduce

1. Navigate to the /repos page.
2. Click the "Add repository" button.
3. Click "Enable" on any listed repository not currently enabled.
4. Receive error message

Expected behavior

I should be able to enable a repo without error.

System Info

{
  "source": "https://github.com/woodpecker-ci/woodpecker",
  "version": "next-1232ceb794"
}

Additional context

• Reproducible with woodpeckerci/woodpecker-server:v3.2.0 and woodpeckerci/woodpecker-server:next-1232ceb794 container images.
• Running in a K3S cluster
• Gitea forge version 1.23.3
• MariaDB database

Server debug log: server.debug.log

Kubernetes Manifest:

---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: woodpecker-server
  namespace: woodpecker
  labels:
    app.kubernetes.io/name: woodpecker
    app.kubernetes.io/component: server
spec:
  serviceName: woodpecker-server-headless
  revisionHistoryLimit: 2
  replicas: 1
  minReadySeconds: 20
  selector:
    matchLabels:
      app.kubernetes.io/name: woodpecker
      app.kubernetes.io/component: server
  template:
    metadata:
      labels:
        app.kubernetes.io/name: woodpecker
        app.kubernetes.io/component: server
    spec:
      priorityClassName: please-keep-running
      restartPolicy: Always
      terminationGracePeriodSeconds: 100
      containers:
      - name: server
        image: woodpeckerci/woodpecker-server:v3.2.0
        imagePullPolicy: IfNotPresent
        env:
          - name: WOODPECKER_DATABASE_DRIVER
            value: mysql
          - name: WOODPECKER_DATABASE_DATASOURCE
            valueFrom:
              secretKeyRef:
                key: dsn
                name: woodpecker-server
          - name: WOODPECKER_HOST
            valueFrom:
              secretKeyRef:
                key: host
                name: woodpecker-server
          - name: WOODPECKER_ADMIN
            value: devin
          - name: WOODPECKER_GITEA
            value: "true"
          - name: WOODPECKER_GITEA_URL
            valueFrom:
              secretKeyRef:
                key: gitea_url
                name: woodpecker-server
          - name: WOODPECKER_GITEA_CLIENT
            valueFrom:
              secretKeyRef:
                key: gitea_client
                name: woodpecker-server
          - name: WOODPECKER_GITEA_SECRET
            valueFrom:
              secretKeyRef:
                key: gittea_key
                name: woodpecker-server
          - name: WOODPECKER_PLUGINS_PRIVILEGED
            value: woodpeckerci/plugin-docker-buildx
          - name: WOODPECKER_LOG_LEVEL
            value: debug
        resources:
          requests:
            cpu: "50m"
            memory: 400Mi
          limits:
            cpu: "1"
            memory: 1Gi
        ports:
          - name: http
            containerPort: 8000
            protocol: TCP
          - name: grpc
            containerPort: 9000
            protocol: TCP
        volumeMounts:
          - mountPath: /var/lib/woodpecker/
            name: config
        livenessProbe:
            httpGet:
              path: /healthz
              port: 8000
            initialDelaySeconds: 30
            periodSeconds: 30
      volumes:
        - name: config
          persistentVolumeClaim:
            claimName: woodpecker-config-pvc

Validations

• 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/versions]

[greenaar]

Can confirm the same, with gitea on a remote server (latest version) and woodpecker 3.2.0 via helm.

Here's a bit from the gitea log, 3.2.0 vs 3.1.0

3.2.0

gitea             | 2025/03/01 14:22:28 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/version for 172.20.0.10:47686, 200 OK in 6.9ms @ misc/version.go:15(misc.Version)
gitea             | 2025/03/01 14:22:28 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/repositories/977 for 172.20.0.10:47686, 200 OK in 15.3ms @ repo/repo.go:559(repo.GetByID)
gitea             | 2025/03/01 14:22:28 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/version for 172.20.0.10:47686, 200 OK in 5.0ms @ misc/version.go:15(misc.Version)
gitea             | 2025/03/01 14:22:28 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/orgs/MYORG for 172.20.0.10:47686, 404 Not Found in 7.6ms @ v1/api.go:594(v1.Routes.func2.orgAssignment.47)
gitea             | 2025/03/01 14:22:28 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/users/MYORG for 172.20.0.10:47686, 200 OK in 5.5ms @ user/user.go:104(user.GetInfo)

3.1.0
gitea             | 2025/03/01 14:25:25 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/version for 172.20.0.10:60196, 200 OK in 5.0ms @ misc/version.go:15(misc.Version)
gitea             | 2025/03/01 14:25:25 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/repositories/977 for 172.20.0.10:60196, 200 OK in 7.0ms @ repo/repo.go:559(repo.GetByID)
gitea             | 2025/03/01 14:25:25 ...eb/routing/logger.go:102:func1() [I] router: completed GET /api/v1/version for 172.20.0.10:60196, 200 OK in 2.3ms @ misc/version.go:15(misc.Version)
gitea             | 2025/03/01 14:25:25 ...eb/routing/logger.go:102:func1() [I] router: completed POST /api/v1/repos/MYORG/MY-tools/hooks for 172.20.0.10:60196, 201 Created in 5.4ms @ repo/hook.go:200(repo.CreateHook)

[xoxys]

Introduced in #4817

@qwerty287 @anbraten Do you have time to look into it?

[xoxys]

@greenaar @DHandspikerWade can you check your orgs table and show us the conflicting org record that already exists? It would also be helpful to see your foges table select id,type from forges;. There must be record in the orgs table that has a different forge ID, at least that the only case that I am able to reproduce.

[greenaar]

@xoxys As noted above, the gitea side shows a different access pattern on 3.2.0 vs 3.1.0, including a completed GET /api/v1/orgs/MYORG and a call to users/

Downgraded, the exact same set of steps does neither of those, and does a get to hooks instead - i'm assuming if the first two gets in 3.2 had worked, they would've eventually ended on the same post.

Here's the output from the SQL above:

mysql> select id,type from forges
    -> ;
+----+-------+
| id | type  |
+----+-------+
|  1 | gitea |
+----+-------+
1 row in set (0.01 sec)

mysql> select * from orgs;
+----+----------+-------------+---------+---------+
| id | forge_id | name        | is_user | private |
+----+----------+-------------+---------+---------+
|  1 |        0 | myusername |       1 |       0 |
|  2 |        1 | tools       |       0 |       1 |
|  3 |        1 | ci-cd       |       0 |       1 |
|  4 |        1 | packaging   |       0 |       1 |
+----+----------+-------------+---------+---------+
4 rows in set (0.00 sec)

[greenaar]

Ok. So while i THOUGHT i was clicking different orgs.. I wasn't. Lots of repos in my personal org, and under 3.2, that fails. Orgs not associated with a user DO work.

[xoxys]

Yeah, this is the same issue Im seeing on my test instance. The forge_id of the user org is 0, while activating new repos in that org tries to add the org again with the correct ID. This is fixed in the linked PR, although the underlying issue is unclear (why are there entries in the orgs table with forge_id 0).

@qwerty287 looks like this state in the DB isnt that weird 😆

[qwerty287]

OK, so the actual issue is that there are orgs with forge id 0. We just need a migration I think to update all forge id 0 => 1?

[xoxys]

Not sure if this is save. It would be better to lookup the forge_id from the forges table and update it properly.

[qwerty287]

For now, that shouldn't be necessary because you can't have more than one forge currently if you haven't directly edited the DB. And if you did, you're responsible for the consequences of this. This is not supported by us I'd say.

[xoxys]

Agree but it also wouldnt too hard to lookup the Id from the forges table as well :)

[qwerty287]

How would you do that? If there would be more than one forge, how do you want to know which is the right one?

[xoxys]

🤦‍♂️ yeah you are right, nevermind

[DHandspikerWade]

I'm seeing the same thing on my end as well

MariaDB [woodpecker]> select id,type from forges; select * from orgs;
+----+-------+
| id | type  |
+----+-------+
|  1 | gitea |
+----+-------+
1 row in set (0.001 sec)

+----+----------+-------+---------+---------+
| id | forge_id | name  | is_user | private |
+----+----------+-------+---------+---------+
|  1 |        0 | devin |       1 |       0 |
+----+----------+-------+---------+---------+
1 row in set (0.001 sec)

[DHandspikerWade]

Manually applying the suggested migration on 3.2.0 seems to resolve the issue. Worth noting that I needed to delete and enable the repo I had tried to enable in 3.2.0 before rolling 3.1.0.

I don't have an other examples, but I can roll back to a backup if needed, a migration may be needed to correct repos enabled during 3.2.0.