Migration failed while upgrade from 0.227.1 -> 0.236.1

[Mikhail_Solovev]

Hello, everyone!
While upgrading from version 0.227.1 to 0.236.1 received error:

2023-07-12T16:54:33.861Z | warn     | Migrations in progress, please do NOT stop the process. "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
2023-07-12T16:54:33.861Z | debug    | Starting migration MigrateIntegerKeysToString1690000000000 "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
2023-07-12T16:54:34.006Z | error    | Error: There was an error running database migrations "{ file: 'ErrorReporterProxy.js', function: 'report' }"
2023-07-12T16:54:34.006Z | error    | QueryFailedError: insert or update on table "webhook_entity" violates foreign key constraint "fk_webhook_entity_workflow_id" "{ file: 'ErrorReporterProxy.js', function: 'report' }"

In version 0.234.0 announced:

This version contains a database migration that changes credential and workflow IDs to use nanoId strings, This migration may take a while to complete in some environments. This change doesn’t break anything using the older numeric IDs.

Can this changing be cause of this error? Maybe somebody else faced with the same issue. I will be appreciated if you will help me. Thank you for your time!

n8n setup

• n8n version: 0.227.1 → 0.236.1
• Database (default: SQLite): Postgres
• Running n8n via (Docker, npm, n8n cloud, desktop app): k8s

[Mikhail_Solovev]

I tried to upgrade to version 0.233.1, same error occurred. What’s can be wrong with my instance?

[MutedJam]

Hi @Mikhail_Solovev, welcome to the community!

I am very sorry you are having trouble. It is unfortunately not possible to downgrade to an n8n version before 0.234 like you have tried here.

I didn’t run into this with my own n8n instance using PostgesSQL though and I wonder if your database might have been in an unexpected state. Did you encounter migration trouble before or have interacted with the instance database directly rather than through n8n?

To resolve this I’d suggest you do the following:

1. Restore a backup of the last known state
2. Export all workflows and credentials using the CLI
3. Shut down your existing n8n instance and start a new n8n instance using a fresh database
4. Import all workflows and credentials using the CLI

The one downside of this is that the CLI does not account for users and permissions if you are using user management. So the admin/owner account would have to re-create user accounts and share the imported workflows and credentials as needed.

Alternatively you could try resolving the conflict manually. The fk_webhook_entity_workflow_id constraint would ensure the workflowId columnd of the webhook_entity table references a valid id from the workflow_entity table:

[Mikhail_Solovev]

Thank you for your answer!
I never change data in db manually. I’m lucky to say that my problem is resolved. I found all webhook entities, which break constraint and delete it.

DELETE
FROM webhook_entity
WHERE "workflowId" = ANY(
    SELECT ARRAY(SELECT he."workflowId"
    FROM webhook_entity he LEFT JOIN workflow_entity we ON he."workflowId" = we.id
    WHERE we.id IS NULL));