SQL Lite DB file locked

[danrche]

Hello all,

I've created a "Log parser" for Trend Micro 7 and have come up with an error where the database is locked. Any help or recommendations?

History:

We manage a large amount of customers with Trend Micro 7 installed for Antivirus. The're is a Management console available but there are too many customers to check each one. We'd rather have the agents create tickets directly to our ticketing system.

Solution in place:

1. created a db parser using the sqllite udf.

2. it writes the results to the event logs (which we then pull into our ticketing system)

3. set exclusions for all the Trend Micro stuff to allow the LogParser to run unaffected by Trend

Problem:

1. the parser works like a champ, I have it running and working on several sites and machines with no trouble, HOWEVER; several machines (including a whole site) continues to report back "Database Locked".

I'm posting my code below, please feel free to comment, criticize, etc....

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#comments-start
Action_Result_for_UI   = the action taken by SA 
1. Cleaned
2. Deleted
3. Quarantined
4. Access denied
5. Passed
6. Action required

Scan_Type
1. Real-time scan
2. manual scan
3. scheduled scan
4. scan now
#comments-end
Local $hQuery, $aRow, $aNames, $data, $tag
_SQLite_Startup ()
;ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open ("C:\Program Files\Trend Micro\UniClient\plugins\EventLog.db") ; open :memory: Database
_SQlite_Query (-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If @error Then
    RunWait("eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D " & '"' & "Database file is locked" & '"' ,"", @SW_HIDE)
    Exit
EndIf
;_SQlite_Query (-1, "Select * From LOG_WOFIE_spyware_L2;", $hQuery)
;_SQLite_Query (-1, "Select * From Log_WOFIE_WTP_MAIN;", $hQuery)
_SQLite_FetchNames ($hQuery, $aNames) ; Read out Column Names
    ;ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s %-10s", $aNames[11], $aNames[13], $aNames[9], $aNames[8]) & @CRLF)
    $label = StringFormat(" %-10s  %-10s  %-10s %-10s", $aNames[11], $aNames[13], $aNames[9], $aNames[8])
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    ;ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s %-10s", $aRow[11], $aRow[13], $aRow[9], _GetDateFromUnix($aRow[8])) & @CRLF)
    $date = @MON & "/" & @MDAY & "/" & @YEAR
    If Int(_GetDateFromUnix($aRow[8])) = $date Then
        _eventlog_Create(StringFormat(" %-10s  %-10s  %-10s %-10s", $aRow[11], $aRow[13], $aRow[9], _GetDateFromUnix($aRow[8])))
    EndIf
WEnd
_SQLite_Close ()
_SQLite_Shutdown ()

Func _eventlog_Create($data)
    $tag = ""
    Select
        Case StringInStr($data, "1  ", 0, 1)
            $tag = "Virus Cleaned"
            $Eid = 1
        Case StringInStr($data, "2  ", 0, 1)
            $tag = "Virus Deleted"
            $Eid = 2
        Case StringInStr($data, "3  ", 0,1)
            $tag = "Virus Quarantined"
            $Eid = 3
        Case StringInStr($data, "4  ", 0,1)
            $tag = "Virus Access Denied, couldn't clean the virus"
            $Eid = 4
        Case StringInStr($data, "5  ", 0,1)
            $tag = "Virus Passed"
            $Eid = 5
        Case StringInStr($data, "6  ", 0,1)
            $tag = "Action Required, Virus left uncleaned"
            $Eid = 6
    EndSelect
        RunWait("eventcreate /T Warning /ID " & $Eid & " /L ManagedIRON /SO TrendMicro /D " & '"' & $tag & @CRLF & $label & @CRLF & $data & '"' ,"", @SW_HIDE)  
EndFunc

Func _GetDateFromUnix ($nPosix)
   Local $nYear = 1970, $nMon = 1, $nDay = 1, $nHour = 00, $nMin = 00, $nSec = 00, $aNumDays = StringSplit ("31,28,31,30,31,30,31,31,30,31,30,31", ",")
   While 1
      If (Mod ($nYear + 1, 400) = 0) Or (Mod ($nYear + 1, 4) = 0 And Mod ($nYear + 1, 100) <> 0) Then; is leap year
         If $nPosix < 31536000 + 86400 Then ExitLoop
         $nPosix -= 31536000 + 86400
         $nYear += 1
      Else
         If $nPosix < 31536000 Then ExitLoop
         $nPosix -= 31536000
         $nYear += 1
      EndIf
   WEnd
   While $nPosix > 86400
      $nPosix -= 86400
      $nDay += 1
   WEnd
   While $nPosix > 3600
      $nPosix -= 3600
      $nHour += 1
   WEnd
   While $nPosix > 60
      $nPosix -= 60
      $nMin += 1
   WEnd
   $nSec = $nPosix
   For $i = 1 to 12
      If $nDay < $aNumDays[$i] Then ExitLoop
      $nDay -= $aNumDays[$i]
      $nMon += 1
   Next
   Return $nMon & "/" & $nDay & "/" & $nYear & " " & $nHour & ":" & $nMin & ":" & $nSec
EndFunc; ==> _GetDateFromUnix

[PsaltyDS]

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

P.S. There is a locking method available that could be checked as part of your query process. Look for jchd's posts on the topic as an example.

Edited August 12, 2011 by PsaltyDS

[danrche]

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

huh, I didn't think of that, thanks. I'm still learning about error handling and "throwing Exceptions" and such. I'll modify this and see what I get, thanks for the quick response.

[danrche]

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

P.S. There is a locking method available that could be checked as part of your query process. Look for jchd's posts on the topic as an example.

gave it a go, the result is $iRET = 5 (the database file is locked). now I have to figure out why the db is always locked on those computers. Thanks for the quick error reporting code. it's good to see methods on dealing with errors, I didn't even think to search in the sqlite udf for those options/variables

[jchd]

Yes error checking is necessary.

What I don't get is what is your setup: which process is writing to the DB? Is the DB shared over a network? Can you use the (recent) WAL mode to avoid blocking readers while one writer works?

Why don't you use _SQLite_GetTable2d in place of your individual calls? You could see some performance improvement and code simplification.

As a sidenote, you probably should use an explicit list of columns instead of * in your select. Then you should also use SQLite strftime() and friends to provide you with ready to use date/time format instead of having to run your own (much slower) Autoit interpreted function.

[danrche]

Yes error checking is necessary.

What I don't get is what is your setup: which process is writing to the DB? Is the DB shared over a network? Can you use the (recent) WAL mode to avoid blocking readers while one writer works?

Why don't you use _SQLite_GetTable2d in place of your individual calls? You could see some performance improvement and code simplification.

As a sidenote, you probably should use an explicit list of columns instead of * in your select. Then you should also use SQLite strftime() and friends to provide you with ready to use date/time format instead of having to run your own (much slower) Autoit interpreted function.

The database is local to each machine and is created and maintained by the Trend Micro Agent on each machine. I wrote the app posted to pull the info from it into the event logs (Kaseya is the RMM tool we use and monitors the event logs already) for ticket creation on Virus's not cleaned. I'm targeting the db file b/c the log files Trend supplies don't update until the Trend Agent is opened by the user and the logs are viewed by the user. I also found the error reporting provied (which rocks thank you) showed me on the one site that all of them had a 1 (db file is not there). I'm troubleshooting that with Trend Micro now. As for the code you suggested with the _SQLLite_GetTable2d and such, I do want it to be lite weight on the boxes and quick, but this is my 1st attempt at this and I needed something quick as we're pushing out Trend 7 to all our clients (with Trend) totaling 2600 nodes total across multiple companies. I like the suggestions you had posted and will work them into the next version of the parser for Trend. I'm not a developer by Trade (more of a sys admin) but enjoy coding and hope to become better with time.

Thank you for your help, please continue to post any suggestions as I learn a lot from going though these forums. I will work on implementing the code changes you suggested to see if I can make this process better and will post my coded results to here when done.

Again, thanks so much for your help guys, this is a LIFE SAVER!

[jchd]

Give _SQLLite_GetTable2d a try to keep the code short, efficient and lightweight.

As for the timestamps, use something like:

select columnA, columnB, ..., strftime('%Y/%m/%d %H:%M:%S', columnNameOfTimestamp, 'unixepoch', 'localtime'), columnX, columnY, ...

Depending on your use case you may want to leave or drop the 'localtime' modifier. Read this page for details.

The reason behind is that invoking directly low-level highly-optimized C function(s) inside SQLite is much, much faster than coding the same in AutoIt.

[danrche]

Give _SQLLite_GetTable2d a try to keep the code short, efficient and lightweight.

As for the timestamps, use something like:

select columnA, columnB, ..., strftime('%Y/%m/%d %H:%M:%S', columnNameOfTimestamp, 'unixepoch', 'localtime'), columnX, columnY, ...

Depending on your use case you may want to leave or drop the 'localtime' modifier. Read this page for details.

The reason behind is that invoking directly low-level highly-optimized C function(s) inside SQLite is much, much faster than coding the same in AutoIt.

Thanks, Will have a go at this and post my result. Thanks again for your guidance.

[jchd]

I hate web when the page needs reloading and you don't have a copy of what you wrote just before...

I also was telling you that you should try using _SQLite-SetTimeout(-1, 60000) right after your _SQLite_Open to allow for a one minute time-out (for instance), expectedly ample time for the other app to release the write lock it has set. If Trend poses a write lock for an extensive duration (bad design) you can still extend the timeout value.

This function is per-connection and saves you complex looping/error checking repeatedly in your code.

[danrche]

#include <SQLite.au3>
#include <SQLite.dll.au3>
#comments-start
Action_Result_for_UI   = the action taken by SA 
1. Cleaned
2. Deleted
3. Quarantined
4. Access denied
5. Passed
6. Action required

Scan_Type
1. Real-time scan
2. manual scan
3. scheduled scan
4. scan now
#comments-end
Local $hQuery, $aRow, $aNames, $data, $tag, $aResult, $iColumns, $iRow
_SQLite_Startup ()
;ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open ("C:\Program Files\Trend Micro\UniClient\plugins\EventLog.db") ; open :memory: Database


$iRET = _SQLite_GetTable2d(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $aResult, $iRow, $iColumns)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
Else
    RunWait('eventcreate /T Warrning /ID 1 /L ManagedIRON /SO TrendMicro /D "' &  _SQLite_Display2DResult($aResult) & '"',"", @SW_HIDE)
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()

Ok, so here's the code I modified to include your suggestions so far, it is much smaller and a bit quicker too.

[jchd]

Fine. Just post again if you encounter issues.