B4J Question JBDC pool connections: how to use correctly ?

[peacemaker]

HI, All

If a B4J server app (works on a Host1 of a LAN) uses a big MySQL database on a dedicated Host0 (of this LAN), and also planned to make other apps on different hostsX (and connecting to the same Host0 db) - how correctly to use the connections pool to avoid errors ?

There are tons of subs inside the app where the DB is used - is a new connection to be opened at each sub and closed at sub finish ?

 

[amykonio]

Hi.
From what I know, connection pooling "offers" one advantage: a used connection is returned to the pool when closed. It's then available to be reused whenever someone needs to connect to the database.
Normal connections, when closed, they are not reused by someone else. They are finally destroyed. Whenever someone needs to connect, a connection is created in the server. But creating a new connection is a procedure that takes some processing time and resources... Here someone may prefer to choose connection pooling.

What you need is to calculate the max number of concurrent connections your application may need. You increase that by lets say 10% (you have to decide what the right percentage will be). You set your database...

Connection pooling may do things faster than typical connections. But is it better? Is it needed? In my opinion it depends. But I usually see them used in web applications, where you can have lot of concurrently connected users. This doesn't mean it should not be used in a client server application.

Now, in connection pooling, when you will call Close Pool method, the connection will return to the pool and will be available for the next client needing to open a connection.

Andreas.

 

[peacemaker]

I have an error

java.lang.RuntimeException: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

or

java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: You can't operate on a closed Connection!!!

in a timer sub where some background calculations are being made. After some seconds of loop working.

 

[amykonio]

How many seconds? What is the connection timeout in pool? Are you sure there is no some exception in your sub?
Andreas.

 

[amykonio]

Also is there any possibility the timer executes again the sub before previous execution completed?
Andreas.

 

[peacemaker]

Timer fires each 2 seconds, but it's disabled until finished. During timer several Internet API calls and updating the db, no other errors.
Pool is inited just single time, no any closing.

DBM.InitializeMySQL($"jdbc:mysql://${DBLocation}/${DBname}?characterEncoding=utf8&allowPublicKeyRetrieval=false&autoReconnect=true&requireSSL=false&useSSL=false&pooling=true"$, DBUsername, DBPassword, 100)
SQL = DBM.GetSQL

 

[amykonio]

It's hard to say without seeing any code. What does GetSQL do?
Also, I believe that when your sub ends you have to call close to return the connection to the pool.
Andreas.

 

[peacemaker]

Sub GetSQL() As SQL 'ignore
    Er=False
    Error = "No Error"

Select nrbase
    Case 1:
        Return SQLite
    Case 2:
        Return pool.GetConnection
    Case 3:
        Return sql

End Select
End Sub

Now i only single time to open connection. Without closing.
Here is related my topic

 

[amykonio]

A connection pool doesn't have unlimited connections... So: yes you have to close the connection when you finished your task. How many times the timer initiates before you get the error? Or the error happens the first time you timer call the sub?
Andreas.

 

[peacemaker]

Around 20-40 times

 

[amykonio]

By default mysql must have 200 connections available in the pool. You mentined:

There are tons of subs inside the app where the DB is used - is a new connection to be opened at each sub and closed at sub finish ?

Are calling many subs from the timer? Does all of them share the same connection you got win getsql()?
If each sub calls getsql() then you should issue SQL.close at the end of the sub (or at every exit point of your sub).
Andreas.

 

[peacemaker]

Does all of them share the same connection you got win getsql()?

Yes, now just single connection, but see the log here, periodical "init mysql", but i do not do it in code (InitializeMySQL is used only once).

Initialization is a part of ABMaterial framework, attached, DBM module.

 

[amykonio]

This

Yes, now just single connection, but see the log here, periodical "init mysql", but i do not do it in code (InitializeMySQL is used only once).

Initialization is a part of ABMaterial framework, attached, DBM module.

This is a different situation. There you are using initialize. Also you have some settings that may be reviewed. Exampel maxIdleTime=300. Are you sure you don't have a timeout?
Andreas.

 

[peacemaker]

Before i tried such settings:

$"jdbc:mariadb://${DBLocation}/${DBname}?characterEncoding=utf8&allowPublicKeyRetrieval=false&autoReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=true"$

now removed, no difference

 

[amykonio]

Try to remove autoReconnect=true or set it to false.
Andreas.

 

[Magma]

Hi there to all...

Here we see the only side of problem... or only the side effects...

Yes ... we know that we have only one connection... sometimes only one connection, not means easy or lite use...

What I mean... if for example you have a table with billion records... and wanna read or search it with one query without limits - it is a problem.... actually it is a big problem...

So when Execute SQL... you must have something like that (if we are talking about mysql/mariadb):

example:

SELECT * FROM MYTABLE WHERE a=100 LIMIT x;

Where x = is a number... preferable 1 - if you want to return only one result... if more set the number you want... but remember many result... many problems...

ofcourse when return the result you want.... is prefereable to close the the Resultset... if it is possible..

 

[peacemaker]

LIMIT x

Now all debugging of the app is via the big "INIT" button that clears and truncates all tables, now no trouble with limits.
Whole app works with SQLite (if to change the db init) fully OK. But migration to MySQL ... is failed yet...

 

[Magma]

@peacemaker how you are calling the queries... give us an example ?

 

[peacemaker]

THe timer sub, where the error is:

Sub timSystem_Tick
    timSystem.Enabled = False

    
    If machine_id = 0 Then
        Dim filter As Map: filter.Initialize
        filter.Put("scanner_name", others.Get_DeviceID)
        webapi1.API_LOAD("scanners", filter)
        Wait For API_LOADED (L As List)
        If L.Size > 0 Then
            Dim machine_id As Int = L.Get(0).As(Map).Get("machine_id")
            Log("machine_id = " & machine_id)
            timFTPupload_Tick
        Else
            Log("machine_id trouble !")
        End If   

    Else
        Dim names As ResultCursor
        names.Initialize(db.SQL, "SELECT * FROM " & db.DBT_NAMES & " WHERE sent = '0'", Null, 1)
        For i = 0 To names.RowCount - 1
            names.Position = i
            Dim name As String = names.GetString("name")
            Log("Sending ... " & name)
            
            db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'")    'sending status
            Dim Sending As ResumableSub = api.Send_Vag(name, "")
            Do Until Sending.Completed
                Sleep(50)
            Loop
            Dim filter As Map: filter.Initialize
            filter.Put("machine_id", machine_id)
            filter.Put("name", name)
            webapi1.API_LOAD("objs", filter)
            Wait For API_LOADED (L As List)
            If L.Size > 0 Then
                Dim obj_id As Int = L.Get(0).As(Map).Get("id")
                Log("obj_id = " & obj_id)
                Dim obj_img As String = L.Get(0).As(Map).Get("img")
                Log("obj_img = " & obj_img)
                
                
                'img name for server: sample_4_85.jpg = sample_ machine_id _ obj_id .jpg
                Dim img_name As String = "sample_" & machine_id & "_" & obj_id & ".jpg"
                Log("img_name = " & img_name)
                
                Log("Sending2 ... " & name)
                db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '2' WHERE name = '" & name & "'")    'sending status
                Dim Sending As ResumableSub = api.Send_Vag(name, img_name)
                Do Until Sending.Completed
                    Sleep(50)
                Loop
                Dim local_file As String = db.SQL.ExecQuerySingleResult("SELECT x.img FROM (SELECT img, id FROM " & db.DBT_OBJS & " WHERE name = '" & name & "' ORDER BY id DESC, rate DESC LIMIT 10) AS x LIMIT 1")
                Log("local_file = " & local_file)
                local_file = fs.getPath(local_file)
                Log("local_file2 = " & local_file)
                If local_file <> "" Then
                    If File.Exists(File.DirApp, "by_ftp") = False Then
                        File.MakeDir(File.DirApp, "by_ftp")
                    End If
                    Dim local_folder As String = File.Combine(File.Combine(File.DirApp, "by_ftp"), machine_id)
                    
                    If File.Exists(local_folder, "") = False Then
                        File.MakeDir(local_folder, "")
                    End If
                    File.Copy("", local_file, local_folder, img_name)    'save file for next uploading by FTP
                End If
            Else
                Log("no obj_id yet")
            End If
            
            
            Log("Sent: " & name)
            db.SQL.ExecNonQuery("UPDATE " & db.DBT_NAMES & " SET sent = '1' WHERE name = '" & name & "'")    'sent status finally
        Next
    End If

    timSystem.Enabled = True
End Sub

 

[Magma]

not all having limits...

also... how many administrators of db exist >?<

on which line you get error ?