Radiance

Repeated SQL Selects make RAM usage go up

14 posts in this topic

#1 ·  Posted (edited)

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.

 

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 by Radiance

Share this post


Link to post
Share on other sites



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?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

try this:

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 by mLipok

Wondering who uses AutoIT and what it can be used for ?


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2017-03-27
Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

2 people like this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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)

 


Wondering who uses AutoIT and what it can be used for ?


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2017-03-27
Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

mLipok,

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

@jchd I know.

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

 


Wondering who uses AutoIT and what it can be used for ?


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2017-03-27
Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

IsObj($RecordSet)1


VarGetType($RecordSet)Object

Thanks.

 

Edited by mLipok

Wondering who uses AutoIT and what it can be used for ?


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2017-03-27
Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now