Database issues, migrations not run

[ctron]

After updating my demo system, I noticed that the advisories table no longer works. Fetching the data results in (500):

{
  "error": "Database error",
  "message": "Query Error: error returned from database: column \"score\" does not exist"
}

I didn't further investigate. So I don't know if this is a UI issue or a backend issue (or both).

[ctron]

cc @carlosthe19916

[ctron]

I noticed other views failing as well:

• SBOMs:

  Query Error: error returned from database: column sbom_package.version does not exist
  

• Vulnerabilities:

  Query Error: error occurred while decoding column "advisory_id": mismatched types; Rust type `core::option::Option<uuid::Uuid>` (as SQL type `UUID`) is not compatible with SQL type `INT4`
  

[carlosthe19916]

I have the latest version locally working and I do not see those issues.

• Taking the risk of being wrong I bet those errors will be gone if you delete the .trustify directory and start the backend.

[ctron]

That might be the case. The instance is using an external database. I would expect that to "migrate" to the newest schema. I'll try check if that's indeed the case.

[ctron]

Resetting the database partially solved this. The advisories still fail with the same error.

[ctron]

However it also looks like as if the importers fail with similar errors now.

[ctron]

Ok, it might be a mix of "did not run migrations" and PEBKAC: I ran the migrations with the old container.

The real issue seems to me that we did not properly use migrations. So the migrations were assumed to be applied, while in fact that are not.

[ctron]

Ok, so having worked around all the issues (like full disk, wrong migration) I still had to wipe the database for the upgrade.

So @carlosthe19916 you had been right, it was a stale database issue. I guess we need to work on migrations.