B4J Question MySQL prepared statements and memory leaks

[vfafou]

Hello!
I have the very often INFO from C3P0 pool connection:

Sep 11, 2016 12:44:44 PM com.mchange.v2.c3p0.stmt.GooGooStatementCache assimilateNewCheckedOutStatement
INFO: Multiply prepared statement! Select A.Z_ZONE As ZONE, A.Z_CD_STATION As CD_STATION, A.Z_U_DTTM, count(*) As SEQ From zones A Join zones B ON A.Z_ZONE = B.Z_ZONE And A.Z_U_DTTM >= B.Z_U_DTTM Where A.Z_CD_STATION = ? Group By A.Z_ZONE, A.Z_CD_STATION Order By 1,3

I'm closing all connections and statements properly!
Memory analyzer of Eclipse, gives me relative to this INFO, memory leaks suspect!

What could I do to overcome this?

Thank you in advance!

 

[Erel]

Are you closing all the ResultSets?

 

[vfafou]

Hello Erel! Thank you for your response...
Yes, I'm closing all the ResultSets!
The code executed after this statement is:

Try
        rs = ASQL.ExecQuery2(SQLQuery,Array As String(aZone,aSeqDtTm))
        Do While rs.NextRow
           ZMesg = "Z;" & rs.GetInt("ZONE") & ";" & rs.GetInt("SEQ") & ";NA;NA"
           CallSubDelayed3(PushShared, "SendWebClientMessage",ZMesg,rs.GetString("CD_STATION"))
        Loop
        rs.Close
    Catch
        If rs.IsInitialized Then rs.Close
        Log("Error AnnZone: " & LastException)
    End Try

Is there any unpredictable behavior of the Try-Catch block?

 

[LucaMs]

As I wrote and deleted I got the same warning but I found that I had not closed a ResultSet in another part of the code. It is probably the same for you too.

 

[Erel]

This code is correct. Make sure that there is no other leaked ResultSets (or connections) in your program.

How are you getting the ASQL object?

 

[vfafou]

Hi Erel,

I'm getting the ASQL as below:

Public oPool As ConnectionPool  '(Created in the main module)
oPool.Initialize(DriverClass, JdbcUrl, DBUser, DBPassword)

I'm calling the sub with ASQL from another sub:

Public Sub GetZoneAnnounce
     Dim OSQL As SQL = oPool.GetConnection

     AnnounceZone(OSQL,SomeVar)  'This Sub has the ASQL object

     OSQL.Close
End Sub

 

[Erel]

Looks good.

I usually add a Try / Catch block:

Dim OSQL As SQL = opool.GetConnection
Try
 ...
Catch
 Log("Error: " & LastException)
End Try
OSQL.Close

I've checked the ResultSet implementation. It closes the prepared statement when it is closed. It is most probably a bug in your code. Monitor the process with a profiler and see whether there is a real leak. Maybe the statements are closed but not immediately.

 

[vfafou]

OK! I'll give it a try!
Thank you very much!

 

[vfafou]

Hello Erel,
After a full Garbage Collection performed, I ran profiler for a few minutes and I saw that the displayed objects are growing very fast! The two images are taken within 10 seconds!

What do all they mean?
Is it actually a memory leak?
I have been watching last 3 days and after a full GC (once a day) I have seen that every day the heap size increases by ~35MB.
Is it reasonable?
Do you see any leak suspected object at the images?

Thank you in advance!

 

[Erel]

Hard to say. I never saw the message you posted in the logs so it is possible that you are not closing a ResultSet somewhere.

Keep it running and see whether it keeps growing.

 

[LucaMs]

From another thread:

simply run you db commands (I think it should be better to create a code module with all your db commands)

I think if we use this "method" (way? practice? Well, you understand what I mean ) find this error would be easier.