B4J Question [WebApp] MySQL connection time out

[Fabrice La]

I have couple of ResultSet in my WebServer appli after some time to play with it I have :
(all my ResultSet are closed)

Error occurred on line: 73 (dbutils).
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:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:41)
at b4j.example.timesheet._filltablefull(timesheet.java:200)
at b4j.example.timesheet._websocket_connected(timesheet.java:578)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:563)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:221)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:156)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:82)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run(WebSocketModule.java:192)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
at anywheresoftware.b4a.ShellBA.startMessageLoop(ShellBA.java:103)
at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:131)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:292)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:82)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:28)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@17fc0cb0 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
... 32 more

 

[Erel]

Are you calling SQL.Close when you are done with a connection?

 

[Fabrice La]

yes but not for all

 

[Erel]

This is probably the cause of this issue. The connections will only return to the pool when you call SQL.Close.

 

[Fabrice La]

For each ResultSet.Close do I have to do SQL.Close ?

 

[Erel]

Yes. This is how you return a connection to the pool.

 

[Fabrice La]

The probleme of time out come from this :

Sub btchange_Click (Params As Map)
    Main.AppLog("btchange :: Start")
    Dim request As HttpRequest
    Dim m As ResultSet
    Dim tmpfrom As String = "r"
    Dim tmpto As String = "r"
    m = DB.pool.GetConnection.ExecQuery("SELECT devises FROM devise WHERE id = " & DefCurr)
    Do While m.NextRow
        tmpfrom = m.GetString("devises")
    Loop
    m.Close
    Log("Premier")
    If tmpfrom <> "r" Then
        m = DB.pool.GetConnection.ExecQuery("SELECT devises FROM devise WHERE id = " & Curr) ' Time out here
        Log("Deuxieme")
        Do While m.NextRow
            tmpto = m.GetString("devises")
        Loop
        m.Close
        DB.pool.GetConnection.Close
        Main.AppLog(URL & tmpfrom.SubString2(0,3) & "&ToCurrency=" & tmpto.SubString2(0,3))
        request.InitializeGet(URL & tmpfrom.SubString2(0,3) & "&ToCurrency=" & tmpto.SubString2(0,3))
        request.Timeout = 10000 
        If HttpClient1.Execute(request, 1) = False Then Return 
    End If
    Main.AppLog("btchange :: End")
End Sub

Why ?

 

[Erel]

Why don't you use jHttpUtils2?

Each time you call Pool.GetConnection you are getting a different connection from the pool.

The code above leaks one connection each time.

Dim connection As SQL = DB.Pool.GetConnection
....
connection.Close

 

[Fabrice La]

Thanks I fix the probleme.

Why am I using jHttpUtils2 because it come from my appli in B4J (no WebAppli). Do I have an other solution ?

 

[Erel]

You can use jHttpUtils with web applications as well. You can take the modules from the server example.