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

New Issue

GiteaMirror · 2025-11-02T10:32:33-06:00

GiteaMirror commented

2025-11-02 10:32:33 -06:00

Originally created by @lunny on GitHub (Jun 12, 2024).

          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 https://github.com/go-gitea/gitea/issues/30153#issuecomment-2162865858

Originally created by @lunny on GitHub (Jun 12, 2024). 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:** ```sql 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:** ```sql 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 https://github.com/go-gitea/gitea/issues/30153#issuecomment-2162865858_

GiteaMirror added the issue/workaround label 2025-11-02 10:32:33 -06:00

GiteaMirror commented

2025-11-02 10:32:34 -06:00

@lunny commented on GitHub (Jun 12, 2024):

default should be boolean column type.

@lunny commented on GitHub (Jun 12, 2024): `default` should be `boolean` column type.

GiteaMirror commented

2025-11-02 10:32:34 -06:00

@melroy89 commented on GitHub (Jun 12, 2024):

The issue title is a bit long ;P

@melroy89 commented on GitHub (Jun 12, 2024): The issue title is a bit long ;P

GiteaMirror commented

2025-11-02 10:32:34 -06:00

@lunny commented on GitHub (Jun 12, 2024):

Have you recreated your database?

@lunny commented on GitHub (Jun 12, 2024): Have you recreated your database?

GiteaMirror commented

2025-11-02 10:32:35 -06:00

@melroy89 commented on GitHub (Jun 12, 2024):

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 commented on GitHub (Jun 12, 2024): > 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.

GiteaMirror commented

2025-11-02 10:32:35 -06:00

@melroy89 commented on GitHub (Jun 12, 2024):

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

@melroy89 commented on GitHub (Jun 12, 2024): I fixed the problem using the following 3 queries, now the migration went successful again. And Gitea is up and running! ```sql 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

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#13143

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

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