SQL Error: SQLSTATE[23502] after database migration

[TomH-BAH]

Checklist

• I verified that Kanboard is correctly installed
• I verified that the problem does not come from a plugin
• I verified that the problem is not already reported
• I understand that Kanboard is in maintenance mode. It doesn't mean it's abandoned, but there is no significant feature development

Actual behaviour

After having migrated from sqlite to postgres with pg_loader to convert the contents to the new database, all the old contents and user information are visible, but I am getting SQL errors when trying to modify/create boards or tasks.

Below are some errors as reported from the webpage

Internal Error: SQL Error: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation "comments" violates not-null constraint DETAIL: Failing row contains (null, 126, 3, 1666898368, test, null, 1666898368).

Internal Error: SQL Error: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation "tasks" violates not-null constraint DETAIL: Failing row contains (null, test, test, 1666899938, yellow, 14, 69, 3, 2, 1, null, 0, 0, 0, 3, 1666899938, , 0, 0, 0, 15, 1666899938, 0, 0, 0, 0, 0, null, null, 0, null, null).

The SQL queries from kanboard appear to be jumbled or have too many values for the number of columns.

Expected behaviour

I should be able to modify/create boards and tasks as if nothing had changed.

Steps to reproduce

Migrate from sqlite to postgres backends using pg_loader to convert databases.
Attempt to create/modify board contents.

Logs

log information from the postgres server:

2022-10-27 15:19:28.321 EDT [3567641] ERROR:  null value in column "id" of relation "comments" violates not-null constraint
2022-10-27 15:19:28.321 EDT [3567641] DETAIL:  Failing row contains (null, 126, 3, 1666898368, test, null, 1666898368).
2022-10-27 15:19:28.321 EDT [3567641] STATEMENT:  INSERT INTO "comments" ("task_id", "user_id", "comment", "date_creation", "date_modification") VALUES ($1, $2, $3, $4, $5)

2022-10-27 15:45:38.604 EDT [3597153] ERROR:  null value in column "id" of relation "tasks" violates not-null constraint
2022-10-27 15:45:38.604 EDT [3597153] DETAIL:  Failing row contains (null, test, test, 1666899938, yellow, 14, 69, 3, 2, 1, null, 0, 0, 0, 3, 1666899938, , 0, 0, 0, 15, 1666899938, 0, 0, 0, 0, 0, null, null, 0, null, null).
2022-10-27 15:45:38.604 EDT [3597153] STATEMENT:  INSERT INTO "tasks" ("title", "description", "color_id", "owner_id", "column_id", "priority", "date_due", "date_started", "time_estimated", "time_spent", "score", "reference", "project_id", "creator_id", "swimlane_id", "date_creation", "date_modification", "date_moved", "position") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)

Configuration

• Kanboard version: 1.2.21 hosted on Apache 2.4.37
• Database type and version: Postgres 13.5
• PHP version: 7.2.24
• OS: Rocky Linux 8
• Browser: Edge

[TomH-BAH]

I can understand that I misinterpreted my issue as a bug, but could I get some clarification as to why? What of my configuration is the real issue here?

[fguillot]

I can understand that I misinterpreted my issue as a bug, but could I get some clarification as to why? What of my configuration is the real issue here?

Converting your database from Sqlite to Postgres is not officially supported. This is your responsibility to make sure the database schema and the data are correct after the migration.

There is currently no guarantee that the Sqlite schema is the same as Postgresql schema expected by Kanboard. The implementation might differ slightly.

[TomH-BAH]

Got it, that's actionable. Thank you.

[colttt]

I've the same issue, did u ever get the migration thing to work?