Migration error

Describe the problem/error/question

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
  • Operating system: Linux

Hey @physx911,

That error looks like it is a permission issue when trying to update the database schema, Is the account you are using the database owner?

The error from the database is…

permission denied to create extension “uuid-ossp”
Must have CREATE privilege on current database to create this extension.

Hello, thank you for your reply!

Ok, I was able to solve and eliminate uuid-ossp error by:

  1. First spawning bash inside the postgres container using
    docker exec -it <postgres_container_name> bash
  2. Connecting the n8n db as a superuser
    psql -U <postgres_root_user> -d <database_name>
  3. Grant CREATE Privilege using
    GRANT CREATE ON DATABASE <database_name> TO <n8n_database_non_root_user>;
  4. 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"

Hey @physx911,

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.

Unfortunately, I don’t have any backup. Is there any other way to recover the instance in such a case?

Also, I’m unable to run n8n db:revert as the container immediately crashes as soon as it starts migrating.

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.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.