Migration seems to fail with error: `pq: invalid input syntax for type bigint: "true"`. Help?? I'm stuck on this 293 migration.

[lunny]

          I believe I still have an issue with the new migration. It says: "Ensure every project has exactly one default column". It tries to do some SQL queriy on the `project` and `project_board` tables. Migration seems to fail with error: `pq: invalid input syntax for type bigint: "true"`. Help?? I'm stuck on this 293 migration. My docker container keeps restating in a loop now. This happens after I updated to v1.22. On version 1.21 everything worked fine. Migration 293 is failing for me!

I'm using Docker gitea/gitea:latest-rootless image with PostgreSQL v14.

2024/06/12 13:41:30 ...ations/migrations.go:642:Migrate() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [public] - 371.738µs
2024/06/12 13:41:30 .../xorm@v1.3.8/sync.go:30:Sync() [I] [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, description,
    CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
    CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
FROM pg_attribute f
    JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
    LEFT JOIN pg_description de ON f.attrelid=de.objoid AND f.attnum=de.objsubid
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
WHERE n.nspname= s.table_schema AND c.relkind = 'r' AND c.relname = $1 AND s.table_schema = $2 AND f.attnum > 0 ORDER BY f.attnum; [version public] - 2.886482ms
2024/06/12 13:41:30 .../xorm@v1.3.8/sync.go:30:Sync() [I] [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [version public] - 431.557µs
2024/06/12 13:41:30 routers/common/db.go:31:InitDBEngine() [W] Table version column id db type is BIGINT, struct type is BIGSERIAL
2024/06/12 13:41:30 ...orm@v1.3.8/engine.go:1252:Get() [I] [SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 [1] - 223.978µs
2024/06/12 13:41:30 ...ations/migrations.go:688:Migrate() [I] Migration[293]: Ensure every project has exactly one default column
2024/06/12 13:41:30 routers/common/db.go:46:migrateWithSetting() [I] [SQL] BEGIN TRANSACTION [] - 53.6µs
2024/06/12 13:41:30 ...ations/v1_22/v293.go:55:CheckProjectColumnsConsistency() [I] [SQL] SELECT project.id as id, project.creator_id, project_board.id as board_id FROM "project" LEFT JOIN "project_board" ON project_board.project_id = project.id AND project_board."default"=$1 WHERE (project_board.id is NULL OR project_board.id = 0) LIMIT 50 [true] - 210.268µs
2024/06/12 13:41:30 ...ations/migrations.go:691:Migrate() [I] [SQL] ROLLBACK [] - 54.09µs
2024/06/12 13:41:30 routers/common/db.go:36:InitDBEngine() [E] ORM engine initialization attempt #8/10 failed. Error: migrate: migration[293]: Ensure every project has exactly one default column failed: pq: invalid input syntax for type bigint: "true"
2024/06/12 13:41:30 routers/common/db.go:37:InitDBEngine() [I] Backing off for 3 seconds
2024/06/12 13:41:31 ...eful/manager_unix.go:144:handleSignals() [W] PID 7. Received SIGTERM. Shutting down...

When I currently dump the project & project_board schema, it looks like this (hope this helps):

Project table:

CREATE TABLE "public"."project" (
    "id" bigint DEFAULT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "title" text NOT NULL,
    "description" text,
    "repo_id" bigint,
    "creator_id" bigint NOT NULL,
    "is_closed" boolean,
    "board_type" bigint,
    "type" bigint,
    "created_unix" bigint,
    "updated_unix" bigint,
    "closed_date_unix" bigint,
    "card_type" integer DEFAULT '0' NOT NULL,
    "owner_id" bigint,
    CONSTRAINT "project_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

Project board table:

CREATE TABLE "public"."project_board" (
    "id" bigint DEFAULT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "title" text,
    "default" bigint DEFAULT '0' NOT NULL,
    "project_id" bigint NOT NULL,
    "creator_id" bigint NOT NULL,
    "created_unix" bigint,
    "updated_unix" bigint,
    "sorting" integer DEFAULT '0' NOT NULL,
    "color" character varying(7),
    CONSTRAINT "project_board_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

Originally posted by @melroy89 in #30153 (comment)

[lunny]

default should be boolean column type.

[melroy89]

The issue title is a bit long ;P

[lunny]

Have you recreated your database?

[melroy89]

Have you recreated your database?

No, I didn't. I remember something similar from the past (3 years ago) regarding bigint with boolean type issue as well. Maybe due to a PostgreSQL upgrade from v13 to v14. Where I indeed manually needed to fix these issues. Maybe I missed this column (type). When I migrate to a new major PostgreSQL, your database might gets recreated, that could be the case. Anyhow, I was running Gitea fine for at least 2 or 3 years now. Updates & migrations were running fine, until this one.

That being said, default is also a reserved word in PostgreSQL/SQL. Meaning if something (eg. a migration) went wrong in the past, it might show up now. Because the name of the column is called 'default', be sure to always but the column name between double quotes.

[melroy89]

I fixed the problem using the following 3 queries, now the migration went successful again. And Gitea is up and running!

ALTER TABLE project_board ALTER COLUMN "default" DROP DEFAULT;
ALTER TABLE project_board ALTER "default" TYPE bool USING CASE WHEN "default"=0 THEN FALSE ELSE TRUE END;
ALTER TABLE project_board ALTER COLUMN "default" SET DEFAULT FALSE;

Thank you! <3