Migration fails in latest version.

[rubenelshof]

Component

server

Describe the bug

A migration fails in the latest version of Woodpecker.

The server wouldn't startup and crashes while attempting to finish the migration.

Last working version is next-2fa9432ef8

Steps to reproduce

1. Set up Woodpecker server with MariaDB server as database.
2. Run docker compose and see logs.

Expected behavior

No response

System Info

Woodpecker version: next-e118f8d980
MariaDB 10.11.6

Additional context

{"level":"info","time":"2024-07-03T21:30:09Z","message":"log level: info"}
{"level":"fatal","error":"can't setup store: could not migrate datastore: migration alter-table-registries-fix-required-fields failed: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLUMN `repo_id` SET NOT NULL' at line 1","time":"2024-07-03T21:30:09Z","message":"error running server"}

Validations

• [X] Read the docs.
• [X] Check that there isn't already an issue 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]

[rubenelshof]

Please reopen this issue. The fix applied also fails with the same error.

See below the table structure after Woodpecker has started and fails.

+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH |
+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+
| registries | id          |                1 | NULL           | NO          | bigint    |                     NULL |
| registries | repo_id     |                2 | 0              | YES         | bigint    |                     NULL |
| registries | address     |                3 | NULL           | YES         | varchar   |                      255 |
| registries | username    |                4 | NULL           | YES         | varchar   |                     2000 |
| registries | password    |                5 | NULL           | YES         | text      |                    65535 |
+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+

I have also setup a new Woodpecker instance and new database on a different machine but with the same database version. This creates the following structure.

+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH |
+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+
| registries | id          |                1 | NULL           | NO          | bigint    |                     NULL |
| registries | org_id      |                2 | 0              | NO          | bigint    |                     NULL |
| registries | repo_id     |                3 | 0              | NO          | bigint    |                     NULL |
| registries | address     |                4 | NULL           | NO          | varchar   |                      255 |
| registries | username    |                5 | NULL           | YES         | varchar   |                     2000 |
| registries | password    |                6 | NULL           | YES         | text      |                    65535 |
+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+

[lafriks]

I think problem is in invalid mysql query in common migration helper: https://github.com/woodpecker-ci/woodpecker/blob/b23fdaa6dc30fcfa862e5d855404fe0bb8108dea/server/store/datastore/migration/common.go#L207