B4J Question [RESOLVED] java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

yfleury

Active Member
Licensed User
Longtime User
I have web b4j web server and this connection timed timed arrive after 10-20 time showing web page.
B4X: 
java.sql.SQLException: An attempt by a client to checkout a Connection 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.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:62)
    at b4j.example.bf_hors_service._initialize(bf_hors_service.java:62)
    at b4j.example.map._bf_hors(map.java:50)
    at b4j.example.map._handle(map.java:224)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:146)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at b4j.example.main.main(main.java:29)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@c5dc4a2 -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)

I connect to mysql database with this
Dim sql1 As SQL=Main.Pool.GetConnection
and close it with sql1.Close

This error begin when I use
resp.SendRedirect("/")
like
B4X: 
Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim sql1 As SQL=Main.Pool.GetConnection
    Dim html As StringBuilder
    html.Initialize
    resp.CharacterEncoding = "UTF-8"resp.Write(html)
    if a=a then
        ....
        resp.Write(html)
    Else
        resp.SendRedirect("/")
    End If
    sql1.Close
End Sub

I always run server in debug
Mysql server have innodb_buffer_pool_size = 256M
How to fix it?
 
Solution
Finally, fund my problem.

I fund a
Dim sql1 As SQL=Main.Pool.GetConnection
without sql1.Close somewhere in 600 lines of code
Resolved
Sort by date Sort by votes

yfleury

Active Member
Licensed User
Longtime User
I find that only one web page causing the time out.
I have a web page with only a google map and I put on it many markers and polylines (javascript). The source code of this web page is about 7500 lines code.

I get markers and polylines from database ( 7 tables for a total of 5859 rows)

The google map is showing all very well and do perfectly the job. But it show map for 17 times and bj4 web server crash.

So I have many connection to db (select)
I have one pool connection and one close in my server handler file like
B4X: 
Sub Handle(req As ServletRequest, resp As ServletResponse)
    Log("---------------> map")
    Dim sql1 As SQL=Main.Pool.GetConnection

    ' make many select * table
    
    sql1.Close
End Sub

Is it a better pratice to make a connection pool and close it each time I connect to a table?
B4X: 
Sub Handle(req As ServletRequest, resp As ServletResponse)
    Log("---------------> map")
    
Dim sql1 As SQL=Main.Pool.GetConnection
    ' make only one select * table1
     sql1.Close

    Dim sql1 As SQL=Main.Pool.GetConnection
    ' make only one select * table2
     sql1.Close

    Dim sql1 As SQL=Main.Pool.GetConnection
    ' make only one select * table3
     sql1.Close

End Sub
 
Upvote 0

yfleury

Active Member
Licensed User
Longtime User
I try this and nochange
 
Upvote 0

yfleury

Active Member
Licensed User
Longtime User
Finally, fund my problem.

I fund a
Dim sql1 As SQL=Main.Pool.GetConnection
without sql1.Close somewhere in 600 lines of code
Resolved
 
Upvote 0
Solution
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…