mysql backend

[Bob Rich]

Hi folks,

I'm playing around with the mysql backend option and have two quick questions.  These could be and probably are just issues on my end due to endless tinkering:

- On the first startup with an empty database, hibernate initializes the schema correctly, but the ScannerMappingUpdaterService quickly starts inserting records imported from /mapping/*.csv, before it's possible to import the 'import.sql'.  This throws the data out of whack and you have to start over.  My workaround was to build an import file that initializes the schema and loads import.sql so that hibernate doesn't have to deal with any of that.  

- I'm getting a lot of errors related to methods being marked '@Transactional(readOnly = true)' in the code.  One example is viewing the vulnerability list after an import.  According to the log, it's trying to update some GenericVulnerability objects, but it's wrapped with a readOnly = true transaction and fails.  I just went through and marked everything 'readonly = false', which has the desired effect, but at some cost in security.

Lastly, just a note, disabling autocommit in the import.sql file improves import time substantially.

I'm up and running now, so i guess i'm just sharing this info.  Let me know if you would like me to try anything.

Thanks!

[Dan Cornell]

One quick question – were you running through the steps laid out in this wiki doc:

https://github.com/denimgroup/threadfix/wiki/Using-MySQL

Or doing something else to get your MySQL instance up and running?

Knowing that we can get some more specific thoughts on your other points.

Thanks,

Dan

[Bob Rich]

I'm sorry Dan, i had that in there at some point but apparently deleted it. 

Yes, that guide was very helpful.  I modified the jdbc.properties as suggested (i also modified jdbc.properties.deploy just in case it was used).  Mysql is running on a remote machine in this instance, which hasn't seemed to cause any problems.

The problem i run into is right at the very end.  Within a few seconds I see messages getting kicked out of the tomcat log indicating that records are being inserted by the application.  It's the ScannerMappingUpdaterService trying to add new VulnerabilityMap entries.  But since none of the rows its expecting to see in the database are there, it throws errors.  However, it does add some things, which then bump the autoincrement counters for a few tables so that when i try to import the 'import.sql', a few tables get thrown off.  This then cascades into invalid FK references and the thing just kind of falls apart from there.

I haven't tried moving the csv files that are in that mapping folder to see if that does it.  My guess is it will.  I just did an end run around it by merging the schema and import files and importing all of it prior to starting threadfix for the first time.  It seems to work.  

In short, if anything was the culprit on my setup, it's the mapping updater service.

[Dan Cornell]

Yes, that guide was very helpful.  I modified the jdbc.properties as suggested (i also modified jdbc.properties.deploy just in case it was used).  Mysql is running on a remote machine in this instance, which hasn't seemed to cause any problems.

The problem i run into is right at the very end.  Within a few seconds I see messages getting kicked out of the tomcat log indicating that records are being inserted by the application.  It's the ScannerMappingUpdaterService trying to add new VulnerabilityMap entries.  But since none of the rows its expecting to see in the database are there, it throws errors.  However, it does add some things, which then bump the autoincrement counters for a few tables so that when i try to import the 'import.sql', a few tables get thrown off.  This then cascades into invalid FK references and the thing just kind of falls apart from there.

I haven't tried moving the csv files that are in that mapping folder to see if that does it.  My guess is it will.  I just did an end run around it by merging the schema and import files and importing all of it prior to starting threadfix for the first time.  It seems to work.  

In short, if anything was the culprit on my setup, it's the mapping updater service.

It looks like this is the result of some updates we've made to how ThreadFix handles scanner mappings. We've been working to make our scanner import support more "plugin-y" to accommodate frequent updates to formats, available vuln types, etc and it looks like some of the changes we made in support of that are causing troubles.

We created a GitHub bug for this so we can track and get it resolved:

https://github.com/denimgroup/threadfix/issues/275

Thanks for sending this along!

Dan