Error after migration from 3.8.0 to 4.0.1 on postgresql

[duzztie]

Hello,

@aksagrimada, could you help please? After executing migration, when starting dtrack 4.0.1 we have the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "DEPENDENCYMETRICS" violates foreign key constraint "DEPENDENCYMETRICS_FK1"
  Detail: Key (COMPONENT_ID)=(1) is not present in table "COMPONENT".

How can we fix it?

Thank you

[aksagrimada]

Component ids in DEPENDENCYMETRICS should have been updated to the the new component id or the rows removed if the referenced component no longer exists. I will check my test database, but I did not experience this issue when I ran the migration.

[wargamez]

I'm getting the following errors when trying to apply the postgres migration file:

CREATE FUNCTION
cleanup_components

t
(1 row)

psql:3.8.0_to_4.0.0_postgres.sql:482: ERROR: syntax error at or near ";"
LINE 1: DROP FUNCTION "cleanup_components";

...

CREATE FUNCTION
convert_components

t
(1 row)

psql:3.8.0_to_4.0.0_postgres.sql:695: ERROR: syntax error at or near ";"
LINE 1: DROP FUNCTION "convert_components";

...

CREATE FUNCTION
process_findings

t
(1 row)

psql:3.8.0_to_4.0.0_postgres.sql:869: ERROR: syntax error at or near ";"
LINE 1: DROP FUNCTION "process_findings";

[aksagrimada]

@wargamez are you using postgres 9? The script was created using 10 where you can simply use the function name if it is unique. Try: DROP function cleanup_components();

[wargamez]

correct, I was using postgres 9

[aksagrimada]

@wargamez did the suggested change work?

[coolserge]

Hello, I am having a somewhat similar issue upgrading the postgres 11 database from 3.7 to 4.0 format using the upgrade file. Here are the two errors that I am observing:

psql:/tmp/3.8.0_to_4.0.0_postgres.sql:791: ERROR:  insert or update on table "ANALYSIS" violates foreign key constraint "ANALYSIS_FK1"
[output_tables.txt](https://github.com/DependencyTrack/v3.8.0-v4.0.0-migration/files/6678284/output_tables.txt)

DETAIL:  Key (COMPONENT_ID)=(112) is not present in table "COMPONENT".

psql:/tmp/3.8.0_to_4.0.0_postgres.sql:806: ERROR:  insert or update on table "DEPENDENCYMETRICS" violates foreign key constraint "DEPENDENCYMETRICS_FK1"
DETAIL:  Key (COMPONENT_ID)=(23) is not present in table "COMPONENT".

---

Here are the lines from the sql file corresponding to these errors:

ALTER TABLE "ANALYSIS"
    ADD CONSTRAINT "ANALYSIS_FK1" FOREIGN KEY ("COMPONENT_ID") REFERENCES "COMPONENT" ("ID");

ALTER TABLE "DEPENDENCYMETRICS"
    ADD CONSTRAINT "DEPENDENCYMETRICS_FK1" FOREIGN KEY ("COMPONENT_ID") REFERENCES "COMPONENT" ("ID");

---

output_tables.txtThe OS is RHEL 7.7