Updated n8n docker from 1.42.1 to 1.44.1 and suddenly the containers won’t start.
n8n container throws the following error:
User settings loaded from: /root/.n8n/config
Last session crashed
Migrations in progress, please do NOT stop the process.
Migration "CreateProject1714133768519" failed, error: insert or update on table "shared_workflow_2" violates foreign key constraint "FK_daa206a04983d47d0a9c34649ce"
Error: There was an error running database migrations
QueryFailedError: insert or update on table "shared_workflow_2" violates foreign key constraint "FK_daa206a04983d47d0a9c34649ce"
while the Postgres container throws the following error:
2024-06-07 15:50:01.764 UTC [1399] ERROR: permission denied to create extension "uuid-ossp"
2024-06-07 15:50:01.764 UTC [1399] HINT: Must have CREATE privilege on current database to create this extension.
2024-06-07 15:50:01.764 UTC [1399] STATEMENT: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2024-06-07 15:50:02.931 UTC [1399] ERROR: insert or update on table "shared_workflow_2" violates foreign key constraint "FK_daa206a04983d47d0a9c34649ce"
2024-06-07 15:50:02.931 UTC [1399] DETAIL: Key (workflowId)=(n9XrfdGxLZoOkOHH) is not present in table "workflow_entity".
2024-06-07 15:50:02.931 UTC [1399] STATEMENT:
INSERT INTO "shared_workflow_2" ("createdAt", "updatedAt", "workflowId", "projectId", role)
SELECT "createdAt", "updatedAt", "workflowId", "projectId", role FROM "shared_workflow";
I’m not sure what the error means. Any help will be greatly appreciated.
Information on your n8n setup
n8n version: 1.44.1
Database (default: SQLite): Postgres
n8n EXECUTIONS_PROCESS setting (default: own, main): main
Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
Ok, I was able to solve and eliminate uuid-ossp error by:
First spawning bash inside the postgres container using docker exec -it <postgres_container_name> bash
Connecting the n8n db as a superuser psql -U <postgres_root_user> -d <database_name>
Grant CREATE Privilege using GRANT CREATE ON DATABASE <database_name> TO <n8n_database_non_root_user>;
Create the uuid-ossp extension using CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
However, I still continue to receive migrations error.
Migration "CreateProject1714133768519" failed, error: insert or update on table "shared_workflow_2" violates foreign key constraint "FK_daa206a04983d47d0a9c34649ce"
Error: There was an error running database migrations
QueryFailedError: insert or update on table "shared_workflow_2" violates foreign key constraint "FK_daa206a04983d47d0a9c34649ce"
That could be because it failed once, Can you try running n8n db:revert in the container and start it again to see if that helps. Failing that I would restore a database backup if you have one then set the permissions again and see if it upgrades.
Documenting, my steps here. I was able to eliminate Key (workflowId)=(n9XrfdGxLZoOkOHH) is not present in table "workflow_entity". error by manually deleting row having problamatic workflowid (in my case complete row that had workflowId=n9XrfdGxLZoOkOHH) inside the shared_workflow table. I used pgAdmin4 to do this.
I believe, I’ve moved forward and now having the following error
2024-06-07 18:05:00.958 UTC [1161] ERROR: duplicate key value violates unique constraint "PK_b21ace2e13596ccd87dc9bf4ea6"
2024-06-07 18:05:00.958 UTC [1161] DETAIL: Key ("webhookPath", method)=(erpv4, GET) already exists.
2024-06-07 18:05:00.958 UTC [1161] STATEMENT: INSERT INTO "public"."webhook_entity"("workflowId", "webhookPath", "method", "node", "webhookId", "pathLength") VALUES ($1, $2, $3, $4, DEFAULT, DEFAULT)
2024-06-07 18:05:02.248 UTC [1161] ERROR: duplicate key value violates unique constraint "PK_b21ace2e13596ccd87dc9bf4ea6"
2024-06-07 18:05:02.248 UTC [1161] DETAIL: Key ("webhookPath", method)=(b374400a-b504-470d-a9db-7cc69ea31a7a, POST) already exists.
2024-06-07 18:05:02.248 UTC [1161] STATEMENT: INSERT INTO "public"."webhook_entity"("workflowId", "webhookPath", "method", "node", "webhookId", "pathLength") VALUES ($1, $2, $3, $4, DEFAULT, DEFAULT)
Thankfully, I was able to recover the instance, however, the above error still shows inside the postgres db. I’ll continue to investigate this further in the weekend.