Documentation: Databases

[greenaar]

Clear and concise description of the problem

The sqlite database woodpecker uses by default (especially with helm installs) does not handle multiple incoming webhooks well.

Case in point, if I made multiple commits to different repos within a minute, I'd get some tasks failing to start with 'Database is locked' in the server log. They would not show up as pending jobs.

Running fewer, or re-running them would work without issues, provided concurrency was low.

Suggested solution

It would be valuable to include a note in the documentation encouraging setup with an external database for anything beyond an initial prototype - I would even say that starting with a external database saves the efforts required in later migration and the associated hassles.

Alternative

Alternatively, the helm chart/docker examples could come with mysql/postgresql configured out of the box, but that puts the onus on your side, as well as increasing the installation requirements.

Additional context

No response

Validations

• [X] Checked that the feature isn't part of the next version already [https://woodpecker-ci.org/faq#which-version-of-woodpecker-should-i-use]
• [X] Read the docs.
• [X] Check that there isn't already an issue that request the same feature to avoid creating a duplicate.

[anbraten]

That might also be sth we can try to solve in code 😅 (Just a short brainstorm from what I had in mind) I guess this is related to the way we create pipeline entries. To be able to have an increasing pipeline number we first do sth like select count(id) from pipelines where repo=... and probably lock the table til we created that entry.

I would even say that starting with a real database saves the efforts required in later migration and the associated hassles.

Sqlite should be treated as full database 😉 To be fair I hated a lot on sqlite before as well, however I noticed how powerful it actually is when having a deeper look into it. With WAL it often is faster than the others, its super easy to backup and administer (one db per tool,, no user, ...). Only real disadvantage is the case when multiple servers are trying to access it (not the case with Woodpecker not having HA etc).

[zc-devs]

SQLite evolves. Now it's better than before. Grafana works well with SQLite. So, problem in Woodpecker.

note in the documentation encouraging setup with a real database for anything beyond an initial prototype

This should be done, until problem is fixed, I think.

starting with a real database saves the efforts required in later migration and the associated hassles

Still a valid point. BTW, how to migrate from SQLite to Postgres, for example? 😉 😃

Database is locked

1905, #2827