Postgres initialize SQL not up to date

[holgerstolzenberg]

Describe the bug Yesterday our auto-dep update pipeline upgraded all dependency check Gradle plugin versions to 7.1.1. As of administrative problems we had to re-init the external postgres database by using the provided external init file. After that all dependencyUpdateCheck tasks failed.

The problematic issues we had:

• It turns out the the postgres init schema is not up to date
• It took some time to figure out that the update script can cure this (https://github.com/jeremylong/DependencyCheck/blob/main/core/src/main/resources/data/upgrade_5.2.sql)
• The update script itself is not compatible with Postgres (value problem)
• After fixing and applying the update script it generally worked

Version of dependency-check used

• Gradle Plugin 7.1.1
• Gradle 7.4.2

Log file Is attached to issue.

To Reproduce Steps to reproduce the behavior:

1. Upgrade to Gradle Plugin 7.1.1
2. Re-init the external database with the current Postgres init sql (https://github.com/jeremylong/DependencyCheck/blob/main/core/src/main/resources/data/initialize_postgres.sql)
3. Run dependencyCheckAnalyze Gradle Task

Expected behavior

• The Postgres init SQL is fully functional/valid. This also might apply to all other external database init scripts.
• The update script is compatible with Postgres
• Optional: Have a mechanism (e.g. CI build) the ensures updating external database scripts is not forgotten

[mprins]

please provide the correct versions, there is no such release of ODC, the latest being 7.1.1, also you have failed to provide your previous version...

Some upgrades require additional actions (such as re-init or purge of the database) which are described in the release notes

[holgerstolzenberg]

Sorry - version have been messed up - I corrected them

[holgerstolzenberg]

Some upgrades require additional actions (such as re-init or purge of the database) which are described in the release notes

That is not relevant here as already said, it has been a full re-init of the db that then does not work with the latest plugin version. I do not see why the fully init script should not create the db to the latest state. Especially if the provided update script is not compatible to postgres OOTB.

[NV-AndreasHahn]

ran into the same issue today. Fix would be nice

[fmarot]

Hello, I faced this issue today. It would be ideal if the change of DB Schema would be mentioned in the release notes. In 7.1.1 release, nothing shows we have to upgrade the schema. Best regards !

[jeremylong]

Fixed with #4736.