Drone server uses MySQL queries to initialize PostgreSQL database

kumashiro · April 13, 2020, 11:46pm

Drone server cloned from GitHub, ref ab93fd2deff49fbf9e8261322f0ffb2a8e428af0

Database configuration:

DRONE_DATABASE_DRIVER=postgres
DRONE_DATABASE_SECRET=<some random string>
DRONE_DATABASE_DATASOURCE=postgres://drone:<password>@127.0.0.1:5432/drone?sslmode=disable

Server starts and connects to database, but fails to initialize it and stops.

Log:

{"error":"pq: syntax error at or near \"AUTOINCREMENT\"","level":"fatal","msg":"main: cannot initialize server","time":"2020-04-14T01:43:38+02:00"}

PostgreSQL uses SERIAL for automatic incrementation. AUTOINCREMENT is used by MySQL.

kumashiro · April 14, 2020, 12:02am

SQLite also uses AUTOINCREMENT, which may mean DRONE_DATABASE_DRIVER is ignored when creating tables.

bradrydzewski · April 14, 2020, 2:50am

hmm, we use drone + postgres at cloud.drone.io without issue. If there were issues with postgres integration or database configuration in general, I would expect these errors at cloud.drone.io and I would expect numerous reports from our community which has not been the case.

An audit of the postgres ddl code does not reveal AUTOINCREMENT being used. All columns that automatically increment use SERIAL

github.com

drone/drone/blob/master/store/shared/migrate/postgres/ddl_gen.go

package postgres

import (
	"database/sql"
)

var migrations = []struct {
	name string
	stmt string
}{
	{
		name: "create-table-users",
		stmt: createTableUsers,
	},
	{
		name: "create-table-repos",
		stmt: createTableRepos,
	},
	{
		name: "alter-table-repos-add-column-no-fork",

This file has been truncated. show original

kumashiro · April 15, 2020, 12:46am

It must be something on my end then. Even when I changed the default value for DRONE_DATABASE_DRIVER from sqlite3 to postgres in code, it still tried to use AUTOINCREMENT. If everything is OK, we can close this issue.

smariot · August 30, 2021, 10:44am

I ran into this issue today, the problem occurs when building with the oss tag in this file:

It calls sql.Open with the requested driver, but then calls sqlite.Migrate on the non-sqlite database.

The fix is probably either to return an error if driver is set to anything other than "sqlite", or call sql.Open with "sqlite" instead of driver.

pinpox · October 31, 2022, 2:24pm

Running into a very similar error:

{"error":"pq: syntax error at or near \")\"","level":"fatal","msg":"main: cannot initialize server","time":"2022-10-31T15:19:18+01:00"}

Did anyone find a solution?