Global vulnerability audit view broken for MSSQL #3692
Comments
|
Seems like DTrack uses TEXT datatype, but MSSQL doesn't support DISTINCT on it. |
|
@rkg-mm Limitation of the ORM we use. It maps the It's not a decision we actively made to choose |
|
Which fields would be affected by this? Since you drop support for MSSQL anyway with 5.x, maybe as quick fix those fields can be excluded... |
|
Based on the query in the exception, it seems to be these: dependency-track/src/main/java/org/dependencytrack/model/Vulnerability.java Lines 175 to 179 in cf4f2d4 dependency-track/src/main/java/org/dependencytrack/model/Vulnerability.java Lines 187 to 191 in cf4f2d4 |
|
For the moment those are not used in the UI, but could be in future once we add expanding of those table rows. Could also cast these 2 in the query |
|
I'm wondering if we need
I'd expect this to slow down the query, but not sure by how much. Also this would need to be a specific cast for SQL Server, since the same will not work in H2, MySQL, or PostgreSQL. |
AFAIK Maybe there's a fix to this problem that does not include |
|
Perhaps there is a way to solve this in a "two-stage" CTE, where we:
Separately, are you seeing the same issue in your instance @rbt-mm @rkg-mm? I'm 99% sure I tested this feature with the MSSQL Compose setup, so I am a bit baffled why it may have worked before but then somehow broke. Does the MSSQL version play a role? The Compose setup uses the 2022 version. |
|
I haven't found the time to update our instance yet. But I also think @rbt-mm tested this with MSSQL... |
|
I currently don't have MSSQL installed so I can't try to reproduce the issue but I'm 100% sure that I successfully tested this with MSSQL 2022 when I created the PR. |
|
I'll try to reproduce later today. |
|
Thank you for your immediate reactions. I would like to share some additional logs with you, which shows the database migration done by DT API server. Maybe it will help you. There are no error messages beside this issue. |
nscuro
added
p1
|
Confirmed to be broken for MSSQL. |
nscuro
changed the title
MSSQL does not support `DISTINCT` for columns of type `TEXT`, which `DESCRIPTION` and `RECOMMENDATION` are. Because the database schema is controlled by DataNucleus, and DataNucleus doesn't allow us to customize column types for specific RDBMSes, changing the respective columns to `VARCHAR(MAX)` is not possible. `DISTINCT` was needed because finding rows are joined with the `PROJECT_ACCESS_TEAMS` table, to support portfolio ACLs. If a user is member of multiple teams, the query would yield a duplicate row for each permitted team the user is a member of. The need for `DISTINCT` is eliminated by converting the ACL check from a `LEFT JOIN` to an `EXISTS` subquery. Fixes DependencyTrack#3692 Signed-off-by: nscuro <nscuro@protonmail.com>
|
Working around this now by using a subquery instead of a |
MSSQL does not support `DISTINCT` for columns of type `TEXT`, which `DESCRIPTION` and `RECOMMENDATION` are. Because the database schema is controlled by DataNucleus, and DataNucleus doesn't allow us to customize column types for specific RDBMSes, changing the respective columns to `VARCHAR(MAX)` is not possible. `DISTINCT` was needed because finding rows are joined with the `PROJECT_ACCESS_TEAMS` table, to support portfolio ACLs. If a user is member of multiple teams, the query would yield a duplicate row for each permitted team the user is a member of. The need for `DISTINCT` is eliminated by converting the ACL check from a `LEFT JOIN` to an `EXISTS` subquery. Fixes DependencyTrack#3692 Signed-off-by: nscuro <nscuro@protonmail.com>
|
This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. |
MSSQL does not support `DISTINCT` for columns of type `TEXT`, which `DESCRIPTION` and `RECOMMENDATION` are. Because the database schema is controlled by DataNucleus, and DataNucleus doesn't allow us to customize column types for specific RDBMSes, changing the respective columns to `VARCHAR(MAX)` is not possible. `DISTINCT` was needed because finding rows are joined with the `PROJECT_ACCESS_TEAMS` table, to support portfolio ACLs. If a user is member of multiple teams, the query would yield a duplicate row for each permitted team the user is a member of. The need for `DISTINCT` is eliminated by converting the ACL check from a `LEFT JOIN` to an `EXISTS` subquery. Fixes DependencyTrack#3692 Signed-off-by: nscuro <nscuro@protonmail.com>
Current Behavior
After updating to 4.11, the new global audit view (see #2472) shows no vulnerabilities on the "Vulnerabilities By Occurrence" tab due to a server side database exception. Vulnerabilities on the tab "Grouped Vulnerabilities" are shown correctly.
Steps to Reproduce
Expected Behavior
There is no database exception and the vulnerabilities on the tab "Vulnerabilities By Occurrence" are shown correctly
Dependency-Track Version
4.11.0
Dependency-Track Distribution
Container Image
Database Server
Microsoft SQL Server
Database Server Version
15.0.4365.2
Browser
Google Chrome
Checklist
The text was updated successfully, but these errors were encountered: