Attempt to enable a second Bitbucket repo results in UNIQUE constraint failed: orgs.name

[aastaneh]

Component

server

Describe the bug

Hi! I am attempting to build and run a new installation of woodpecker and encountered the following issue when adding repositories:

Steps to reproduce

Launch a woodpecker server (v1.0.5) and agent instance using the Bitbucket repo client.
From the repositories tab, choose a repo and click Enable.
Again, from the repositories tab, choose a second repo and click Enable.

Expected behavior

The repository is enabled successfully.

Actual behavior

The bottom right of the screen contains the following error highlighted in red:

UNIQUE constraint failed: orgs.name, which is a SQLite error.

System Info

/version returns:
`{"source":"https://github.com/woodpecker-ci/woodpecker","version":"1.0.5"}`

I'm not using docker-compose, but here are the sanitized ansible tasks that provision the server and agent containers:


- name: Run Woodpecker Docker Container
  community.docker.docker_container:
    name: woodpecker
    image: "{{ woodpecker_version }}"
    env:
      WOODPECKER_HOST: "https://{{ woodpecker_server_host }}"
      WOODPECKER_LETS_ENCRYPT: "true"
      WOODPECKER_LETS_ENCRYPT_EMAIL: "[email protected]"
      WOODPECKER_BITBUCKET: "true"
      WOODPECKER_BITBUCKET_CLIENT: "{{ woodpecker_bitbucket_client }}"
      WOODPECKER_BITBUCKET_SECRET: "{{ woodpecker_bitbucket_secret }}"
      WOODPECKER_AGENT_SECRET: "{{ woodpecker_rpc_secret }}"
      WOODPECKER_LOG_LEVEL: "trace"
      WOODPECKER_ADMIN: "myuser,anotheruser"
      WOODPECKER_OPEN: "false"
      WOODPECKER_REPO_OWNERS: "companyname"
    ports:
      - "80:80"
      - "443:443"
      - "9000:9000"
    restart_policy: "always"
    volumes: "/var/lib/woodpecker:/var/lib/woodpecker"

- name: Run Woodpecker Runner Container
  community.docker.docker_container:
    name: runner
    image: "{{ woodpecker_agent_version }}"
    env:
      WOODPECKER_SERVER: "server.domain.tld:9000"
      WOODPECKER_AGENT_SECRET: "{{ woodpecker_rpc_secret }}"
      WOODPECKER_HOSTNAME: "localhost"
      WOODPECKER_MAX_WORKFLOWS: "5"
      WOODPECKER_LOG_LEVEL: "info"
    ports:
      - "3000:3000"
    restart_policy: "always"
    volumes:
      - "/var/run/docker.sock:/var/run/docker.sock"
      - "/var/lib/woodpecker-agent-conf:/etc/woodpecker"

### Additional context

With trace level logging enabled, I see:

{"level":"trace","time":"2023-11-09T20:53:07Z","caller":"/woodpecker/src/github.com/woodpecker-ci/woodpecker/server/router/router.go:43","message":"[POST] /api/repos?forge_remote_id={37418eb3-cdb7-4bf5-a8a0-7953042cd586}"}

I speculate this bug is being triggered at or around https://github.com/woodpecker-ci/woodpecker/blob/main/server/api/repo.go#L129

### Validations

- [X] Read the [Contributing Guidelines](https://github.com/woodpecker-ci/woodpecker/blob/main/CONTRIBUTING.md).
- [X] Read the [docs](https://woodpecker-ci.org/docs/intro).
- [X] Check that there isn't [already an issue](https://github.com/woodpecker-ci/woodpecker/issues) that reports the same bug to avoid creating a duplicate.
- [X] Checked that the bug isn't fixed in the `next` version already [https://woodpecker-ci.org/faq#which-version-of-woodpecker-should-i-use]
- [X] Check that this is a concrete bug. For Q&A join our [Discord Chat Server](https://discord.gg/fcMQqSMXJy) or the [Matrix room](https://matrix.to/#/#woodpecker:matrix.org).

[aastaneh]

Also, here's the sqlite dump of the orgs table:

sqlite> .schema orgs
CREATE TABLE `orgs` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NULL, `is_user` INTEGER NULL, `private` INTEGER NULL);
CREATE UNIQUE INDEX `UQE_orgs_name` ON `orgs` (`name`);

sqlite> .dump orgs
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `orgs` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NULL, `is_user` INTEGER NULL, `private` INTEGER NULL);
INSERT INTO orgs VALUES(1,'',0,0);
COMMIT;

sqlite> select * from orgs;
1||0|0

As you can see, the name field in the above record is the empty string.

[aastaneh]

Found a workaround: drop the index.

sqlite> drop index UQE_orgs_name;

I now end up with these two rows:

select * from orgs;
1||0|0
2||0|0

[anbraten]

So it complains as the names of your orgs are somehow or null. Related code should be https://github.com/woodpecker-ci/woodpecker/blob/main/server/forge/bitbucket/bitbucket.go#L402

[aastaneh]

Hi, @anbraten!

Confirming that both the workspace name and the workspace ID in Bitbucket are populated with strings in the pattern [A-Za-z]+.

[aastaneh]

Workaround not helpful- if you restart the service, it will be caught in a crashloop with:

{"level":"fatal","error":"msg: Sync error '*model.Org': UNIQUE constraint failed: orgs.name","time":"2023-11-09T22:11:02Z","message":"could not migrate datastore"}