Repeated SQL Selects make RAM usage go up

[Radiance]

Hi,

I'm stuck here and would appreciate any help you could possible give.
I've got this program that repeatedly does the same SQL select on a MySQL EDI database to see if there are any active problems.
The select occurs every 30 seconds.

The script is running a few days until suddenly the memory is full and the machine is fully frozen.

I've identified the cause and dumbed the script down to the one I'm showing below.

 

Basically it's just the first two lines in the While Loop.
Hitting F7 makes the script reset the connection every 3 seconds.

Both ways the memory usage of the script increases for like 20 kb every few seconds (seen in task manager).

 

I have no idea what to change.

 

Edit: The SQL table I'm using for testing is empty.

 

expandcollapse popup

HotKeySet("{F7}", "_ToggleConnectionReset")

Global $Driver = "{MySQL ODBC 3.51 Driver}"
Global $Server = ""
Global $DB = ""
GLobal $User = ""
Global $Pwd = ""
Global $Port = ""
Global $Select = "select * from table"


Global $RecordSet, $Data, $Timer = TimerInit()
Global $ResetConnection = False
Global $Connection = ObjCreate("ADODB.Connection")

$Connection.open ("DRIVER=" & $Driver & ";SERVER=" & $Server & ";DATABASE=" & $DB & ";UID=" & $User & ";PWD=" & $Pwd & ";PORT=" & $Port)
If @error Then
    MsgBox(16, "Fail", "Couldn't connect to database")
    Exit
EndIf

While 1
    $RecordSet = $Connection.Execute($Select)
    $Data = $RecordSet.GetRows

    If $ResetConnection = True Then
        If TimerDiff($Timer) > 3000 Then
            $Timer = TimerInit()
            $Connection.Close
            $Connection = ""

            $Connection = ObjCreate("ADODB.Connection")
            $Connection.open ("DRIVER=" & $Driver & ";SERVER=" & $Server & ";DATABASE=" & $DB & ";UID=" & $User & ";PWD=" & $Pwd & ";PORT=" & $Port)
            ConsoleWrite(@CRLF & @SEC & " - Reconnecting." & @CRLF)
        EndIf
    EndIf

    SLeep(100)
WEnd

Func _ToggleConnectionReset()
    $ResetConnection = NOT $ResetConnection
EndFunc

 

Edited September 16, 2015 by Radiance

[JLogan3o13]

I notice outside of your While loop you're creating your Connection object, and then you're doing it again and again each pass through the While loop. Have you tried just creating the object once and not every single time?

[ViciousXUSMC]

I use the MySQL.udf so not really familiar with how your doing it, but I know it was important to have _MySQLEnd() for my scripts.  I do not see an equivalent end/close connection in your script.  Perhaps thats the issue? (in addition to what JLogan has posted about multiple objects being created)

[Radiance]

Thanks for the replies, guys.

The recreation / connection close of the object only happens after switching on the reconnecting via F7 and only after the variable gets destroyed ($Connection = "").
From my understanding this should free any memory that has been assigned to it.

The whole thing after the first two lines in the While Loop was only for additional troubleshooting and never gets used if you don't hit F7.
Yet the problem is the same.
 

The "Global" in the While Loop was a typo, sorry for that.

I've taken a look at the MySQL UDF, the _MySQLEnd() does nothing different then what I'm doing in the "extended" loop once F7 is pressed.
It's basically just a $oConnection.Close

[ViciousXUSMC]

Yeah I missed that, was looking at the function itself not your variable if statement.. 

[mLipok]

try this:

expandcollapse popup

HotKeySet("{F7}", "_ToggleConnectionReset")



Global $Driver = ""
Global $Server = ""
Global $DB = ""
Global $User = ""
Global $Pwd = ""
Global $Port = ""
Global $Select = ""


; Error monitoring. This will trap all COM errors while alive.
; This particular object is declared as local, meaning after the function returns it will not exist.
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")


Global $RecordSet, $Data, $Timer = TimerInit()
Global $ResetConnection = False
Global $Connection = ObjCreate("ADODB.Connection")


$Connection.open("DRIVER=" & $Driver & ";SERVER=" & $Server & ";DATABASE=" & $DB & ";UID=" & $User & ";PWD=" & $Pwd & ";PORT=" & $Port & ";")
If @error Then
    MsgBox(16, "Fail", "Couldn't connect to database")
    Exit
Else
    ConsoleWrite('Connection OK' & @CRLF)
EndIf

While 1
    $RecordSet = $Connection.Execute($Select)
    $Data = $RecordSet.GetRows
    If @error Then ConsoleWrite(@error & ' >GetRows' & @CRLF)

    If $ResetConnection = True Then
        If TimerDiff($Timer) > 3000 Then
            $Timer = TimerInit()
            $Connection.Close
            $Connection = ""

            $Connection = ObjCreate("ADODB.Connection")
            $Connection.open("DRIVER=" & $Driver & ";SERVER=" & $Server & ";DATABASE=" & $DB & ";UID=" & $User & ";PWD=" & $Pwd & ";PORT=" & $Port & ";")
            ConsoleWrite(@CRLF & @SEC & " - Reconnecting." & @CRLF)
        EndIf
    EndIf

    Sleep(100)
WEnd

Func _ToggleConnectionReset()
    $ResetConnection = Not $ResetConnection
EndFunc   ;==>_ToggleConnectionReset



; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
        ConsoleWrite(@CRLF & _
                "! ==> COM Error intercepted ==> NOT ADO related Error " & @CRLF & _
                "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
                "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
                "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                "! ==> COM End of Error dump." & @CRLF & _
                @CRLF)


EndFunc   ;==>_ErrFunc

 

What you see in console ?

 

EIDT:
of course set up your own:

Global $Driver = ""
Global $Server = ""
Global $DB = ""
Global $User = ""
Global $Pwd = ""
Global $Port = ""
Global $Select = ""

 

Edited September 16, 2015 by mLipok

[Radiance]

What you see in console ?

Losely translated since the error message was in German.
As stated above, the table I query is completely empty.

Connection OK

! ==> COM Error intercepted ==> NOT ADO related Error
-    $oError.description is:     Either BOF or EOF is True, or the current data set has been deleted. The requested operation needs an up to date data set.
-    $oError.number is:         -2147352567   in HEX is  0x80020009
-    $oError.source is:         ADODB.Recordset
-    $oError.windescription:        Exception occured.
-    $oError.helpfile is:     C:\Windows\HELP\ADO270.CHM
-    $oError.helpcontext is:     1240645
-    $oError.lastdllerror is:     0
-    $oError.scriptline is:         32
-    $oError.retcode is:         0x800A0BCD
! ==> COM End of Error dump.

-2147352567 >GetRows

 

[jchd]

Some ADO (ODBC) drivers error out on .GetRows when the resultset is empty (your case). Bracket this part with test:

...
    $RecordSet = $Connection.Execute($Select)
    If Not $RecordSet.EOF Then
        $Data = $RecordSet.GetRows
...
    Else
        ; recordset is empty
        Dim $Data[0][0]    ; or some other suitable code
    EndIf

 

[Radiance]

 

If Not $RecordSet.EOF Then

 

Absolutely fantastic, this is it.

Just stumbled over the fact that the memory increase only happens when the table is empty.
Probably would have searched for this solution for another few weeks.

Thank you.

[mLipok]

Can you also check something for me ?

...
    $RecordSet = $Connection.Execute($Select)
    ConsoleWrite('IsObj($RecordSet)' & IsObj($RecordSet) & @CRLF)
    ConsoleWrite('VarGetType($RecordSet)' & VarGetType($RecordSet) & @CRLF)
    If Not $RecordSet.EOF Then
        $Data = $RecordSet.GetRows
...
    Else
        ; recordset is empty
        Dim $Data[0][0]    ; or some other suitable code
    EndIf

 

I'm curious about what will return this two following line in your case:

ConsoleWrite('IsObj($RecordSet)' & IsObj($RecordSet) & @CRLF)
    ConsoleWrite('VarGetType($RecordSet)' & VarGetType($RecordSet) & @CRLF)

 

[Radiance]

IsObj($RecordSet)1
VarGetType($RecordSet)Object

[jchd]

mLipok,

Don't forget that you can learn/check more about an object using various options of ObjName().

[mLipok]

@jchd I know.

But my intention was to check the case with relation to this:

 

[mLipok]

IsObj($RecordSet)1

VarGetType($RecordSet)Object

Thanks.

 

Edited September 17, 2015 by mLipok