v4.13.1 Upgrade - Cannot define PRIMARY KEY constraint on nullable column in table 'POLICY_TAGS'.

[martibr147]

I'm receiving the following when upgrading to v4.13.1. Is this an issue with the update or does the DB need to be manually modified?

Error in executing upgrade class: org.dependencytrack.upgrade.v4131.v4131Updater
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot define PRIMARY KEY constraint on nullable column in table 'POLICY_TAGS'.

[nscuro]

How old is your DT instance?

I booted up a new instance of 4.12.0 and back then it already created the columns on those tables as NOT NULL. I wonder if a much older version did not do that, which would explain why we didn't catch it when testing the migration.

[nscuro]

I checked back to 4.5.0 and those join tables should always have had NOT NULL on their columns. Really odd.

[nscuro]

As a workaround until the fix is released, run the following statements in your database:

DROP INDEX "POLICY_TAGS"."POLICY_TAGS_N50";
ALTER TABLE "POLICY_TAGS" ALTER COLUMN "TAG_ID" BIGINT NOT NULL;
CREATE INDEX "POLICY_TAGS_N50" ON "POLICY_TAGS" ("TAG_ID");

Then restart Dependency-Track. The migration should go through now.

Edit: Modified comment to reflect the confirmed workaround.

[rkg-mm]

I am not a SQL Server expert, but FWIW you might be able to get away with running this, and then restarting DT:

ALTER TABLE "POLICY_TAGS" ALTER COLUMN "POLICY_ID" BIGINT NOT NULL;
ALTER TABLE "POLICY_TAGS" ALTER COLUMN "TAG_ID" BIGINT NOT NULL;

Second statement fails with
[S0001][5074] Line 1: The index 'POLICY_TAGS_N50' is dependent on column 'TAG_ID'. [S0009][4922] Line 1: ALTER TABLE ALTER COLUMN TAG_ID failed because one or more objects access this column.

edit: OK dropped index _N50 first, then run statements, then recreated this index. This ran through. trying to update now.

[rkg-mm]

ok that worked. thanks!

[martibr147]

Thanks for the reply. Going back in the codebase, it appears that we started on 4.8.0.

[martibr147]

Resolved by dropping index, altering table, and re-creating index.

ALTER TABLE "POLICY_TAGS" ALTER COLUMN "TAG_ID" BIGINT NOT NULL;

[nscuro]

We released 4.13.2 with a fix for this issue.

[Mat-Reassured]

I realise this is closed, but I'm having the same issue - I'm running just a local docker version - and upgraded to v4.13.0 - if I try and bump it anymore (4.13.1 or 4.13.2) I get
org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TAG_ID" must not be nullable; SQL statement: ALTER TABLE "POLICY_TAGS" ADD CONSTRAINT "POLICY_TAGS_PK" PRIMARY KEY ("POLICY_ID", "TAG_ID") [90023-232]
Do you want me to ping this as a new issue?