B4J Question Jserver & Connection pool not responsive after some time.

[tufanv]

Hello,

I am having a problem with jserver and connection pool. After some time server is not responding and logs show timeout errors. I am closing the pool connection after the work done and close the resultset also. here is the code:

main:

'Non-UI application (console application)
#Region  Project Attributes
    #CommandLineArgs:
    #MergeLibraries: true
    #AdditionalJar: mysql-connector-java-5.1.43-bin.jar
#End Region

Sub Process_Globals
    Private srvr As Server
    Public pool As ConnectionPool
End Sub

Sub AppStart (Args() As String)
    srvr.Initialize("srvr")
    srvr.Port = 8080
    
    srvr.StaticFilesFolder = File.Combine(File.DirApp, "www")
    srvr.LogsFileFolder = File.Combine(File.DirApp, "logs")
    srvr.AddHandler("/v1/coinlist", "coinlist", False)
    srvr.AddHandler("/v1/pairlist", "pairlist", False)
    srvr.AddHandler("/v1/coinlive", "coinlive", False)
    
    pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/kripto?useSSL=false", _
        "root", "xxx")
    Log("Testing the database connection")

    srvr.Start
    Log("Server started")
    StartMessageLoop
End Sub

and the coinlist module for example ( because all other modules are same)

'Class module
Sub Class_Globals
    Private mreq As ServletRequest 'ignore
    Private mresp As ServletResponse 'ignore


End Sub

Public Sub Initialize

End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    mreq = req
    mresp = resp
 
    
    Dim connection As SQL = Main.Pool.GetConnection
    Try
      
        Dim cursor As ResultSet

        Dim n As Map
        Dim l As List
        l.Initialize
        n.Initialize
        cursor=Main.pool.GetConnection.ExecQuery("SELECT sembol,name,table_name from genel")
        Do While cursor.NextRow
    
'            n = CreateMap("symbol" :cursor.GetString("sembol"),"value" :cursor.GetString("last"),"changebynumber": cursor.GetString("changenumber"),"changebypercent": cursor.GetString("changepercent"),"updated": cursor.GetString("updated"))
            n=  CreateMap("symbol" :cursor.GetString("sembol"),"name":cursor.GetString("name"),"source":cursor.GetString("table_name"))
            
            l.Add(n)

        Loop
        Dim jsong As JSONGenerator
        jsong.Initialize2(l)
        'Log(jsong.ToString)
    Catch
        Log(LastException)
    End Try
    connection.Close 'return to pool
    cursor.Close

    resp.ContentType = "application/json"
    resp.Write(jsong.ToString)

End Sub

 

[Erel]

You should close the cursor before closing the connection (probably not the cause of this issue but fix it).
Other than that your code looks correct. The online server example (https://b4x.com:51041/) runs for months with MySQL and the same connection pool.

 

[MichalK73]

You can try HicaryCp.
I used it at home and it works perfectly.
https://www.b4x.com/android/forum/threads/hikaricp-high-performance-connection-pool.88430/#content

 

[tufanv]

You should close the cursor before closing the connection (probably not the cause of this issue but fix it).
Other than that your code looks correct. The online server example (https://b4x.com:51041/) runs for months with MySQL and the same connection pool.

currently vps has 1 cpu and the load is around 1.3 will try upgrading to 2 and correcting the resultset and try again.

 

[tufanv]

Nope, it didn't fix this.

 

[Pendrush]

You can check number of connections in mysql, number probably go high to the roof for some reason.
You get timeout as mysql server wait for released connection and that is never happened in your case.
Also you may check do you close all connections.
You can do simple test in localhost by querying all your scrips and in the same time watch on mysql connections.

 

[tufanv]

You should close the cursor before closing the connection (probably not the cause of this issue but fix it).
Other than that your code looks correct. The online server example (https://b4x.com:51041/) runs for months with MySQL and the same connection pool.

I realized stg. Aren't we doing a mistake by calling this in the module :

   cursor=Main.pool.GetConnection.ExecQuery("SELECT sembol,name,table_name from genel")

maybe it should be :

 cursor=Connection.ExecQuery("SELECT sembol,name,table_name from genel")

can it be the mistake. We are calling the pool instead of the connection because we already set the

Dim connection As SQL = Main.Pool.GetConnection

and still calling from the pool.getconnection instead of connection

 

[Erel]

This is indeed the mistake. You should never get a connection like this as you are getting a new connection each time.