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

[Mashiane]

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

[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

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.

 

[EnriqueGonzalez]

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.

 

[Mashiane]

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

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

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

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

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.

 

[EnriqueGonzalez]

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

 

[Mashiane]

you dont need to open transactions when executing queries only when executing non queries

Thank you, implementing changes.

 

[Mashiane]

its better to create the variable locally assign pool and close it locally too.

Awesome, implementing changes.

 

[alwaysbusy]

Why do these methods return a ResumableSub? I don't see anything async in them.

 

[Mashiane]

Why do these methods return a ResumableSub? I

Actually, Im also wondering whether thats crazy or not in this question.

[BANanoServer] Executing ws.b4jSend

Hi there Following on the BANano Essentials Ebook, my BrowserIndex and ServerIndex code has been updated to use ws.B4JSend. For example, in my BrowserIndex, I have ... ws.B4JSend("DirectMySQLOnServer_BAN", payload) ... and inside the ServerIndex.DirectMySQLOnServer_BAN sub, I am calling db...

www.b4x.com

 

[alwaysbusy]

in this question

I read that topic and indeed I wondered why this would be needed. But I had not seen this topic yet where you used ResumableSub in SQLRunQuery() so it may have been possible that method did do something async.

 

[Mashiane]

I understand... im just throwing ideas too in terms of how I can make such this work like a well oiled machine. Thanks for pitching in.

 

[Mashiane]

So far I have updated the RunQuery to...

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...

 

[Mashiane]

The ExecuteMaps has also been updated.

'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...

 

[Mashiane]

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.