I’m having the same issue. The same foreign key constraint fails when updating from 1.121.3 to 1.122.x
It’s this migration that fails, with the same message as above:
Migration "AddActiveVersionIdColumn1763047800000" failed, error: insert or update on table "workflow_entity" violates foreign key constraint "FK_08d6c67b7f722b0039d9d5ed620"
DETAIL: Key (activeVersionId)=(925b481f-c4b0-4bf9-b73e-ee1f82a2de59) is not present in table "workflow_history".
STATEMENT: UPDATE "workflow_entity"
SET "activeVersionId" = "versionId"
WHERE "active" = true
I went into the database and struggle to find any foreign key constraint by this name.
This should give a list of all constraints in the database, but doesn’t list the one above.
SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace;
This is what the schema of my `workflow_entity` table looks like:
Table "public.workflow_entity"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+-----------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------
name | character varying(128) | | not null | | extended | | |
active | boolean | | not null | | plain | | |
nodes | json | | not null | | extended | | |
connections | json | | not null | | extended | | |
createdAt | timestamp(3) with time zone | | not null | CURRENT_TIMESTAMP(3) | plain | | |
updatedAt | timestamp(3) with time zone | | not null | CURRENT_TIMESTAMP(3) | plain | | |
settings | json | | | | extended | | |
staticData | json | | | | extended | | |
pinData | json | | | | extended | | |
versionId | character(36) | | not null | | extended | | |
triggerCount | integer | | not null | 0 | plain | | |
id | character varying(36) | | not null | | extended | | |
meta | json | | | | extended | | |
parentFolderId | character varying(36) | | | NULL::character varying | extended | | |
isArchived | boolean | | not null | false | plain | | |
versionCounter | integer | | not null | 1 | plain | | |
description | text | | | | extended | | |
Indexes:
"workflow_entity_pkey" PRIMARY KEY, btree (id)
"IDX_workflow_entity_name" btree (name)
"pk_workflow_entity_id" UNIQUE, btree (id)
Foreign-key constraints:
"fk_workflow_parent_folder" FOREIGN KEY ("parentFolderId") REFERENCES folder(id) ON DELETE CASCADE
Referenced by:
TABLE "processed_data" CONSTRAINT "FK_06a69a7032c97a763c2c7599464" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "insights_metadata" CONSTRAINT "FK_1d8ab99d5861c9388d2dc1cf733" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE SET NULL
TABLE "workflow_history" CONSTRAINT "FK_1e31657f5fe46816c34be7c1b4b" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "chat_hub_sessions" CONSTRAINT "FK_9f9293d9f552496c40e0d1a8f80" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE SET NULL
TABLE "workflow_dependency" CONSTRAINT "FK_a4ff2d9b9628ea988fa9e7d0bf8" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "chat_hub_messages" CONSTRAINT "FK_acf8926098f063cdbbad8497fd1" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE SET NULL
TABLE "test_run" CONSTRAINT "FK_d6870d3b6e4c185d33926f423c8" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "shared_workflow" CONSTRAINT "FK_daa206a04983d47d0a9c34649ce" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "execution_entity" CONSTRAINT "fk_execution_entity_workflow_id" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "webhook_entity" CONSTRAINT "fk_webhook_entity_workflow_id" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "workflow_statistics" CONSTRAINT "fk_workflow_statistics_workflow_id" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
TABLE "workflows_tags" CONSTRAINT "fk_workflows_tags_workflow_id" FOREIGN KEY ("workflowId") REFERENCES workflow_entity(id) ON DELETE CASCADE
Triggers:
workflow_version_increment BEFORE UPDATE ON workflow_entity FOR EACH ROW EXECUTE FUNCTION increment_workflow_version()
This only references the foreign key constraint to `fk_workflow_parent_folder`.
For reference, I run the Community edition + Registered license. So I do have access to and use folders.
Since the error message also mentions the workflow_history table, I wonder if it’s a combination with a data issue. Some orphaned data from 2years of running maybe.
Further help is appreciated.
EDIT 10:20UTC
I followed up on the failed constraint to the `workflow_history` table. The error message basically says, that it tries to update a workflow_entity, but this entity has no corresponding versions in the `workflow_entity`. From what it looks like, because the community edition deletes workflow versions after a day, you end up having workflows with no history records in the `workflow_history” table, thus failing this constraint. If you go to a previous version, make an edit, save, update to 1.122.5, then it runs the migration successfully. However with 50+ workflows, that is a very impractical workaround and the migration should be patched to account for this. // @mohamed3nan