0 Replies Latest reply on Nov 5, 2014 3:47 AM by ryyppy

    FSDBUtil: Timeout in Import Process

    ryyppy

      I got some very weird behavior of our faultline database, so hopefully someone here knows what could cause havoc on our database, whenever I try to import FASL scripts. I tried 2 methods to reach my goal (inserting more than ten custom FASL scripts) and came to a point where I think that the database is the problem.


      System specification:

      OS: Windows 2008 R2 Standard 64-bit

      RAM: 4 GB

      CPU:  2x3.00 GHz

       

      MSSQL:

      SQL Server 2008 R2, ServicePack 2

      Version: 10.50.6000.34

      .NET-Version: 2.0.50727.5485

       

      MVM Preset:

      Installed Version 7.5

      Installed FASL-Support with the FSUpdate tool

       

      Goal:

      Import and publish around 400 FASL-Scripts to the MVM database and assign them to a vulnerability by web-interface.

       

      Method 1:

      Import well-formed FASL-Files with the utility program FSDBUtil.

      FSDBUtil tended to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds.

       

      Method 2:

      Write SQL-Scripts using the predefined stored procedures “Content.Script_Save”, “Content.Vuln_Save” and “Content.Script_Publish”.

       

      Problems:

      FSDBUtil (Method1 ) tends to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds. Usually this behavior occurs after successfully importing around 10 FASL-scripts. Restarting the MSSQL database service did not fix the problem. I thought maybe FSDBUtil is the problem, which led me to write my own SQL-Scripts (Method 2).

       

      I attached the SQL-scripts for better understanding. During testing and debugging the code, I stumbled upon very strange behavior of the faultline-database, which probably cause problems for FSDBUtil in the first place.

       

      In save_test.sql, I followed following workflow:

      1. 1) Collect predefined values as Admin-ID, VulnCategoryID etc.
      2. 2) Generate a Vulnerability-dataset (Content.Vuln) for the metadata by using the procedure Content.Vuln_Save (and remember the generated faultlineID)
      3. 3) Create a ScriptInDev-Dataset (Content.ScriptInDev) by using the procedure Content.Script_Save (using the generated faultlineID from step 2)
      4. 4) Publish the script (and ultimately write the dataset to Content.Script) by executing the procedure Content.Script_Publish with the mentioned faultineID and the predefined Admin-ID
      5. 5) Select the newly generated data in Content.ScriptInDev, Content.Script and Content.Vuln

       

      As far as I know, launching this SQL-Script for around 10 times works fine. All datasets were generated from faultlineID 6000001 to 6000012 until the execution time skyrocketed to around 40 – 50 minutes for future executions. Debugging showed that Content.Script_Publish caused high latencies by executing following SQL-Statement (Line 76 in Content.Script_Publish):

       

      INSERT INTO Content.Script

      ( ScriptID

      ,     ScriptName

      ,     Script

      ,     ModuleID

      ,     CreatedDate

      ,     ModifiedDate

      )

      SELECT

      FaultlineID

      ,     ScriptName

      ,     Script

      ,     ModuleID

      , @Now        --CreatedDate

      , @Now        --ModifiedDate

      FROM Content.ScriptInDev

      WHERE FaultlineID = @FaultlineID

       

      It seems that an INSERT statement targeting the Content.Script table cause high execution times. Also I realized that a DELETE statement took the same amount of time as soon as there are more than 10 custom fasl scripts in this table (DELETE FROM Content.Script WHERE ScriptID >= 6000000; ). Content.Script contains around 50019 datasets.

       

      I assume that there is some internal mechanism (index, etc.) preventing the database to efficiently inserting and deleting data from Content.Script... SELECT statements work perfectly fine. What would be a workaround to get the FSDBUtil / SQL-Scripts working in a reasonable execution time?