Dependency analyzer stuck at "Begin database maintenance"

[hemanthacharya]

Hi @jeremylong ,

I am running the depedency analyzer by configuring a local Oracle DB. I modified the code to run the "initialize_oracle.sql" to create all the tables , which is getting generated correctly, but it is stuck at "Begin database maintenance" and simply does not execute further at all. The logs show no error or exception. It is there more to be done to configure a local DB?

Attaching the logs for your reference. dc_sample.log

[hemanthacharya]

I replaced :
final PreparedStatement ps = getPreparedStatement(CLEANUP_ORPHANS); in cleanupDatabase() method in CveDB with final PreparedStatement ps = connection.prepareStatement("DELETE FROM cpeEntry WHERE NOT EXISTS (SELECT * FROM software WHERE cpeEntry.id = software.CPEEntryId)");

and it worked. Not sure how that can work.

[jeremylong]

On an oracle database the cleanup orphans should be executing:

DELETE FROM cpeEntry WHERE id not in (SELECT CPEEntryId FROM software)

Try the following (and the oracle database parameters should not go o the first line - only the second):

dependency-check.sh --purge
dependency-check.sh --updateonly [Add all the parameters you use to connect to your oracle db]

Then look in /opt/dependency-check/data and see if an H2 database exists?

[hemanthacharya]

I tried your suggestion.

I purged. I initialized the Oracle DB with initialize_oracle.sql and then i ran the dependency-check with the below command

dependency-check.sh --updateonly [all Oracle params]

All the downloads and records creation works fine until it reaches the "Begin database maintenance." It is stuck there for over 1 hr now.

Does the query take so long to clean up the records?

Also, there is no /opt/dependency-check/data created yet

There are no errors in the logs as well.

I am running the dependency-check 3.1.2 version.

[hemanthacharya]

So the explain plan report for this query shows | 0 | DELETE STATEMENT | | 8888 | 3454K| 41M (2)|138:35: 25 |

I think the query

DELETE FROM cpeEntry WHERE NOT EXISTS (SELECT * FROM software WHERE cpeEntry.id = software.CPEEntryId)

is the right thing to do?

[jeremylong]

From a performance standpoint I've always been under the impression NOT IN was actually a faster performing query (see https://stackoverflow.com/questions/15959061/delete-records-which-do-not-have-a-match-in-another-table).

What does the explain call out for the NOT IN version of the query?

Also - were you just calling the update routine or were you also trying to analyze a directory? For testing purposes just use dependency-check.sh --updateonly.

[hemanthacharya]

I read that thread too. But it seems like NOT IN does not work at all. I tried to run it in the sqldeveloper, even there it just keeps on running.

The Explain call that I mentioned -

| 0 | DELETE STATEMENT | | 8888 | 3454K| 41M (2)|138:35: 25 |

was for the NOT IN query that is there in the script.

But the query that I used -

DELETE FROM cpeEntry WHERE NOT EXISTS (SELECT * FROM software WHERE cpeEntry.id = software.CPEEntryId)

completes in 427 ms . It shows 0 rows deleted.

Also, I just tried with --updateonly and did not perform the full scan.

Is it possible from your end to try the NOT IN query and see if the query hangs?

Thanks

[jeremylong]

I do not actually have an oracle database setup to test the integration. This code was added by a different contributor.

[hemanthacharya]

Is there any other way you suggest to resolve this?

I think I can use the query -

DELETE FROM cpeEntry WHERE NOT EXISTS (SELECT * FROM software WHERE cpeEntry.id = software.CPEEntryId)

to clear all the orphans which works correctly and is fast. Do you agree?

[TorstenKruse]

Hi @jeremylong We are struggeling with the same problem after we migrated from mysql to postgres. Now the database maintenance for dependency-check version 1.x (which we still need for backwards compatibility of some older projects) lasts for ten to twenty hourse. Is there any solution for this?

Current query plan for procpid 15896:

 Statement:

DELETE FROM cpeEntry WHERE id not in (SELECT CPEEntryId FROM software)

Query Plan:

Delete on cpeentry  (cost=12.00..22.12 rows=5 width=6)
  ->  Seq Scan on cpeentry  (cost=12.00..22.12 rows=5 width=6)
        Filter: (NOT (hashed SubPlan 1))
        SubPlan 1
          ->  Seq Scan on software  (cost=0.00..11.60 rows=160 width=4)

[jeremylong]

@hemanthacharya I apologize that I'm just getting back to this. Yes, the query you created looks correct. I will update the Oracle properties file.

@TorstenKruse I do not currently have a postgres DB setup. Would the suggested query from @hemanthacharya work in postgres as well?

DELETE FROM cpeEntry WHERE NOT EXISTS (SELECT * FROM software WHERE cpeEntry.id = software.CPEEntryId)

Also, note that we are no longer supporting any of the old versions of dependency-check. In fact, the data source used by the pre-5.0 release will be going away soon. The NVD will no longer publish the XML data feed I believe sometime this fall. 5.0+ uses the JSON data feed.

[TorstenKruse]

@jeremylong yes, the other DELETE statement is much better. How can I switch to this in version 1.x?

[jeremylong]

We no longer support the 1.x version of dependency-check. You would have to download the source, switch to the appropriate tag, update the properties file for you database, and recompile/deploy within your org. Note that the data feed used by all version previous to 5.0.0 will be going away. Please consider upgrading that environment.

[TorstenKruse]

Ok, so I'll try to upgrade the projects to a more recent version.