B4J Question PoolConnection Problem : Connection has timed out

Waldemar Lima

Active Member
Licensed User
hi everyone !
I'm using MySql Connector , but when the server stays running for some time, it gets this error :

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

how can i fix it ?

this is the code [DB_Class_Module]:

B4X:
Sub Process_Globals
    Public pool As ConnectionPool
End Sub


Public Sub Initialize
    pool.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://localhost/kadyi?characterEncoding=utf8","root","")
End Sub

Public Sub Sync
    Dim sql1 As SQL = pool.GetConnection
    Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM usuario")
End Sub


Public Sub CheckLogin(user As String,pass As String) As String
    Dim sql1 As SQL = pool.GetConnection
    Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM usuario WHERE email = '"&user&"' AND senha = '"&pass&"'")
    If rs.NextRow Then
        Log(rs.GetString2(0))
        If (rs.GetString2(0) <> "") Then
            Log("Usuario Logado !")
            Return "Success:"&rs.GetString2(0)&":"&rs.GetString2(1)&":"&rs.GetString2(5)
        Else
            Log("Detalhes incorretos ...")
            Return "Error"
        End If
    End If
    rs.Close
    Return "Error"
End Sub

Public Sub ListCidades() As String

    Dim sql1 As SQL = pool.GetConnection
    Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM cidades")
    Do While rs.NextRow Then
        Log(rs.GetString2(0))
        If (rs.GetString2(0) <> "") Then
            Log("Usuario Logado !")
            Return "Success:"&rs.GetString2(0)&":"&rs.GetString2(1)&":"&rs.GetString2(5)
        Else
            Log("Detalhes incorretos ...")
            Return "Error"
        End If
    Loop
    rs.Close
    Return "Error"
End Sub

Public Sub ListpClinicas(cidade As String,estado As String) As String
    Dim sql1 As SQL = pool.GetConnection
    Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM clinicas WHERE premium = 1 AND cidade = '"&cidade&"' AND estado = '"&estado&"' ORDER BY RAND() LIMIT 50;")
    Dim RowCount As Int = 0
    Dim Clista As String = ""
    Dim Resto As Int = 0
   
    Do While rs.NextRow Then
        RowCount = RowCount + 1
        If (rs.GetString2(0) <> "") Then
            Clista = Clista & rs.GetString2(0)&":"&rs.GetString2(1)&":"&rs.GetString2(5)&","
        End If
    Loop
   
    If (RowCount < 50) Then
        Resto = 50 - RowCount
        Dim rs2 As ResultSet = sql1.ExecQuery("SELECT * FROM clinicas WHERE premium = 0 AND cidade = '"&cidade&"' AND estado = '"&estado&"' ORDER BY RAND() LIMIT "&Resto&";")
       
        Do While rs2.NextRow Then
            RowCount = RowCount + 1
            If (rs2.GetString2(0) <> "") Then
                Clista = Clista & rs2.GetString2(0)&":"&rs2.GetString2(1)&":"&rs2.GetString2(3)&":"&rs2.GetString2(4)&":"&rs2.GetString2(5)&","
            End If
        Loop
       
    End If
   
    If (RowCount > 0) Then
        Return "Success:"&RowCount&":"&Clista
    Else  
        Return "NoClinicas"  
    End If
   
    rs.Close
    Return "Error"
End Sub
 

Waldemar Lima

Active Member
Licensed User
hi Erel ! Sure .

here :
B4X:
java.lang.RuntimeException: java.sql.SQLException: An attempt by a client to che                                                                                        ckout a Connection has timed out.
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:119)
        at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
        at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:130)
        at anywheresoftware.b4j.object.JServlet.Handle(JServlet.java:110)
        at anywheresoftware.b4j.object.JServlet.doPost(JServlet.java:80)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865                                                                                        )
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java                                                                                        :535)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandl                                                                                        er.java:255)
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandl                                                                                        er.java:1595)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandl                                                                                        er.java:255)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandl                                                                                        er.java:1253)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandle                                                                                        r.java:203)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:                                                                                        473)
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandle                                                                                        r.java:1564)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandle                                                                                        r.java:201)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandle                                                                                        r.java:1155)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.j                                                                                        ava:144)
        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerColl                                                                                        ection.java:126)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper                                                                                        .java:132)
        at org.eclipse.jetty.server.Server.handle(Server.java:531)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:352)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.jav                                                                                        a:260)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(Abstra                                                                                        ctConnection.java:281)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)
        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhat                                                                                        YouKill.java:333)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWh                                                                                        atYouKill.java:310)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatW                                                                                        hatYouKill.java:168)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouK                                                                                        ill.java:126)
        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.r                                                                                        un(ReservedThreadExecutor.java:366)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPoo                                                                                        l.java:760)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool                                                                                        .java:678)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connectio                                                                                        n has timed out.
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne                                                                                        ction(C3P0PooledConnectionPool.java:690)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A                                                                                        bstractPoolBackedDataSource.java:140)
        at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPo                                                                                        ol.java:45)
        at b4j.example.db._checklogin(db.java:33)
        at b4j.example.loginhandle._handle(loginhandle.java:65)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.                                                                                        java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces                                                                                        sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
        ... 34 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil                                                                                        e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo                                                                                        ol@14acaea5 -- timeout at awaitAvailable()
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes                                                                                        ourcePool.java:1467)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(                                                                                        BasicResourcePool.java:644)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR                                                                                        esourcePool.java:554)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn                                                                                        ectionInUse(C3P0PooledConnectionPool.java:758)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne                                                                                        ction(C3P0PooledConnectionPool.java:685)
        ... 43 more

thanks by attention :)
 
Upvote 0

Harris

Expert
Licensed User
Where did you set the pool size? What size is it? I think the default is 10 - but could be smaller...

My init to MySQL - I set poolSize to 100

B4X:
Sub InitializeMySQL(jdbcUrl As String ,login As String, password As String, poolSize As Int) 'ignore
    Log("init mysql")
    UsePool = True
    Try
        pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
    Catch
        Log("Last Pool Init Except: "&LastException.Message)
    End Try

    ' change pool size...
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(poolSize))
End Sub
 
Upvote 0

Waldemar Lima

Active Member
Licensed User
Where did you set the pool size? What size is it? I think the default is 10 - but could be smaller...

My init to MySQL - I set poolSize to 100

B4X:
Sub InitializeMySQL(jdbcUrl As String ,login As String, password As String, poolSize As Int) 'ignore
    Log("init mysql")
    UsePool = True
    Try
        pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
    Catch
        Log("Last Pool Init Except: "&LastException.Message)
    End Try

    ' change pool size...
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(poolSize))
End Sub


Thanks by you answer :D
a question about "setMaxPoolSize" , can i cleaning up this Buffer inside "Pool" ?
 
Upvote 0

Waldemar Lima

Active Member
Licensed User
when I use the command: setMaxPoolSize, I'll be setting the memory buffer size of the Pool, right?
If I can set the size of this buffer, can I also "clean it"?
Or does this "buffer" only store Query output?
 
Upvote 0

Harris

Expert
Licensed User
Good question.... I don't know.
Being max pool size, it may grow dynamically - or - reserve the space statically???

Set it 100 - and then setting it to 10 should "clear" it - if that is important... I set it to 100 and never worry about it.
 
Upvote 0
Top