B4J Question [SOLVED] Hikari usage with MySQL: Connection Leaks / Connection not available

Mashiane

Expert
Licensed User
Longtime User
Hi there

I am trying out the Hikari connection pool and still learning its usage.

There are two warnings I see often on my app.

Connection Leaks

B4X:
[HikariPool-1 housekeeper] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for com.mysql.cj.jdbc.ConnectionImpl@609e8838 on thread main, stack trace follows

Connection is not available

B4X:
java.lang.RuntimeException: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30006ms.

The MaxPoolSize for me is set to 100 as I am testing scaling issues.

I am concerned about the last warning as it might be happening just about to run a query and then that fails as a connection is not available.

Whilst not really revelant / or perhaps is, my use case is on jetty using the pre-configured BANanoServer.
 
Solution
Could I be doing something wrongly perhaps?
SQLExecuteMaps
you dont need to open transactions when executing queries only when executing non queries. also you are opening a resultset after opening a transaction and closing it after closing the transaction. better to just remoe the transaction part at all there.

Sub SQLGet()

also, i am not a fan of using the connections as global variables, its better to create the variable locally assign pool and close it locally too. if 2 processes access the same varaible at once the first one will lose reference and you will have a connection leak. that doesnt happen with local variables, each time a process access a sub it will diferentiate between each

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
There are two warnings I see often on my app.
this are actually of concern, you should really look where you are not closing the connections.

The MaxPoolSize for me is set to 100 as I am testing scaling issues.
100 is too much, you will see 100 idle connections doing nothing, 50 or 30 could serve thousands of customers without stressing the db.

I am concerned about the last warning as it might be happening just about to run a query and then that fails as a connection is not available.
this will happen unless you fix the issue, i could say to just restart it from time to time but you can be for sure that you are just shooting yourself on the foot with this solution.
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
this are actually of concern,
A great one indeed. I have extracted the code that is applicable to MySQL from my class.

OpenMySQL is called on AppStart

B4X:
Sub OpenMySQL(serverIP As String,  serverPort As String, serverDB As String, login As String, password As String) As Boolean
    'Log("init mysql")
    UsePool = True
    If IsOpen Then
        SQLite = pool.GetConnection
        Return True
    End If
    Try
        Dim jdbcUrl As String = $"jdbc:mysql://${serverIP}:${serverPort}/${serverDB}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowLoadLocalInfile=True"$
        pool.Initialize("com.mysql.cj.jdbc.Driver", jdbcUrl, login, password)
        Dim jocp As JavaObject = pool
        jocp.RunMethod("setMaximumPoolSize", Array(PoolSize))
        jocp.RunMethod("addDataSourceProperty", Array("cachePrepStmts", True))
        jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSize", 250))
        jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSqlLimit", 2048))
        jocp.RunMethod("addDataSourceProperty", Array("useServerPrepStmts", True))
        jocp.RunMethod("addDataSourceProperty", Array("useLocalSessionState", True))
        jocp.RunMethod("addDataSourceProperty", Array("useLocalTransactionState", True))
        jocp.RunMethod("addDataSourceProperty", Array("rewriteBatchedStatements", True))
        jocp.RunMethod("addDataSourceProperty", Array("cacheResultSetMetadata", True))
        jocp.RunMethod("addDataSourceProperty", Array("cacheServerConfiguration", True))
        jocp.RunMethod("addDataSourceProperty", Array("elideSetAutoCommits", True))
        jocp.RunMethod("addDataSourceProperty", Array("maintainTimeStats", True))
        Dim thres As Long = 60000
        jocp.RunMethod("setLeakDetectionThreshold", Array(thres))
        SQLite = pool.GetConnection
        IsOpen = True
        Return True
    Catch
        IsOpen = False
        Log("OpenMySQL: " & LastException.Message)
        Return False
    End Try
End Sub

At anytime, I execute 1 of these methods for CRUDs and selects

B4X:
Sub SQLRunQuery(qry As String, args As List) As ResumableSub
    SQLGet
    SQLite.BeginTransaction
    Try
        If args <> Null Then
            SQLite.ExecNonQuery2(qry, args)
        Else   
            SQLite.ExecNonQuery(qry)
        End If
        SQLite.TransactionSuccessful
        OK = True
        Error = ""
    Catch
        SQLite.rollback
        OK = False
        Error = LastException.Message
        Log($"SQLRunQuery: ${Error}"$)
    End Try
    SQLClose
    Return True
End Sub

The other one

B4X:
Sub SQLExecuteMaps(Query As String, args As List) As ResumableSub
    SQLGet
    'Log("SQLExecuteMaps: " & Query)
    Dim l As List
    l.Initialize
    SQLite.BeginTransaction
    Try
        Dim cur As ResultSet
        If args <> Null Then
            cur = SQLite.ExecQuery2(Query, args)
        Else
            cur = SQLite.ExecQuery(Query)
        End If
        Do While cur.NextRow
            Dim res As Map
            res.Initialize
            For i = 0 To cur.ColumnCount - 1
                res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
            Next
            l.Add(res)
        Loop
        SQLite.TransactionSuccessful
        cur.Close
        OK = True
        Error = ""
    Catch
        SQLite.Rollback
        OK = False
        Error = LastException.message
        Log($"SQLExecuteMaps: ${Error}"$)
    End Try
    SQLClose
    Return l
End Sub

My SQLGet and SQLClose are

B4X:
Sub SQLGet()
    If UsePool Then
        SQLite = pool.GetConnection
    End If
End Sub

'Close the database connection
Sub SQLClose
    If UsePool Then
        SQLite.Close
    End If
End Sub

Could I be doing something wrongly perhaps?

Thanks in advance.
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
Could I be doing something wrongly perhaps?
SQLExecuteMaps
you dont need to open transactions when executing queries only when executing non queries. also you are opening a resultset after opening a transaction and closing it after closing the transaction. better to just remoe the transaction part at all there.

Sub SQLGet()

also, i am not a fan of using the connections as global variables, its better to create the variable locally assign pool and close it locally too. if 2 processes access the same varaible at once the first one will lose reference and you will have a connection leak. that doesnt happen with local variables, each time a process access a sub it will diferentiate between each
 
Upvote 1
Solution

Mashiane

Expert
Licensed User
Longtime User
Why do these methods return a ResumableSub? I
Actually, Im also wondering whether thats crazy or not in this question.

 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
So far I have updated the RunQuery to...

B4X:
Sub SQLRunQuery(qry As String, args As List) As ResumableSub
    Dim SQLite As SQL = pool.GetConnection
    Try
        SQLite.AddNonQueryToBatch(qry, args)
        Dim SenderFilter As Object = SQLite.ExecNonQueryBatch("SQL")
        Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
        OK = True
        Error = ""
    Catch
        OK = False
        Error = LastException.Message
        Log($"SQLRunQuery: ${Error}"$)
    End Try
    SQLite.Close
    Return True
End Sub

And will run some tests...
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
The ExecuteMaps has also been updated.

B4X:
'Execute a query and return a list of Mapped records
Sub SQLExecuteMaps(Query As String, args As List) As ResumableSub
    Dim SQLite As SQL = pool.GetConnection
    'Log("SQLExecuteMaps: " & Query)
    Dim l As List
    l.Initialize
    'SQLite.BeginTransaction
    Try
        Dim SenderFilter As Object = SQLite.ExecQueryAsync("SQL", Query, args)
        Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, cur As ResultSet)
        If Success Then
            Do While cur.NextRow
                Dim res As Map
                res.Initialize
                For i = 0 To cur.ColumnCount - 1
                    res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
                Next
                l.Add(res)
            Loop
        End If   
        'SQLite.TransactionSuccessful
        cur.Close
        OK = True
        Error = ""
    Catch
        'SQLite.Rollback
        OK = False
        Error = LastException.message
        Log($"SQLExecuteMaps: ${Error}"$)
    End Try
    SQLite.Close
    Return l
End Sub

Will do some tests...
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
100 is too much,
I have set this to 25. Thank you @EnriqueGonzalez
you dont need to open transactions when executing queries only when executing non queries.
This was fixed. Thank you @EnriqueGonzalez
its better to create the variable locally assign pool and close it locally too.
This was fixed. Thank you @EnriqueGonzalez
I don't see anything async in them.
This was fixed. Thank you @alwaysbusy

I have ran my BVAD3 BANanoServer WebApp, I can confirm that the warnings/errors are no more.

1646375023312.png


🙏 🙏 🙏 🙏 🙏
 
Upvote 0
Top