B4J Question sql.SQLException: "Too many connections" with ConnectionPool library

jmon

Well-Known Member
Licensed User
Longtime User
Hi,

Following this thread : http://www.b4x.com/android/forum/th...ion-not-allowed-after-resultset-closed.42516/

I have changed all my SQL queries and SQL connection to the Pool from ConnectionPool method. I now run into this problem:

My application runs with about 30 users. As you can see from the log, there are 2 errors:
B4X: 
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1186
11a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to
acquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection,  message from server: "Too many connecti
ons"

And

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

Is it caused by the pool library? Am I not closing the connections properly?

Thanks
Jmon
 
Sort by date Sort by votes

jmon

Well-Known Member
Licensed User
Longtime User
Yes, I am for each query.

But I have a question. For the async queries, I close the connections the way you shown me in this thread : http://www.b4x.com/android/forum/th...ion-not-allowed-after-resultset-closed.42516/, but how about the non async queries is this ok:
B4X: 
Dim sq As SQL = Pool.GetConnection
MYUNIQUEID = sq.ExecQuerySingleResult("SELECT id FROM user WHERE nick = '" & MYUSERNAME & "';")
sq.Close

[EDIT]
Do I need to close the sq.close before the crsr.close or after?

Thanks
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
jmon said:
Yes, I am for each query.
Click to expand...

NO! Arghhh, I messed up on one of them, instead of calling
B4X: 
Dim sq As SQL = sender
in the QueryComplete event I was calling:
B4X: 
Dim sq As SQL = Main.Pool.GetConnection

I found the error after reviewing my code. Thanks a lot, and sorry for wasting your time!

Regards
jmon
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
[UPDATE] After implementing correctly the Pool Library, my application was running fine. Sometimes I would get a crash "Too Many Connections".


My application runs on 28 computers and some android phones. Each client will on startup query ~10 times the MySQL 5.5 Database.

This page recommends to set the max_connections to a value higher than the max connection value from the pool library:
If you are using connection pool on the application side, max_connections should be bigger than max connections on the pool side. Connection pooling is also a good alternative if you are expecting a high number of connections.
Click to expand...

How to know the max connection from the pool library? I dont't see it in the error report? I have set it to 300 in MySQL, and that seems to fix the problem for now.

Thanks
Jmon.
 
Upvote 0

alienhunter

Active Member
Licensed User
Longtime User

Hi ,
now I got the same error but with about 80 computers and tablets "too many connections" , did erel's recommended code helped ?
thanks

AH
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0

alienhunter

Active Member
Licensed User
Longtime User

thanks I increased the connections to 300 , checked all cursors , still there are "sleeping" ones from the tablet's ( i might missed a couple of cursors or something else )
for too long so I decreased the the sleep value to 14400 in mysql so it kicks it out if still open ,
not pretty but it works due to advanced laziness ... reading old code ....
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0
B

balarii

New Member
Licensed User
Longtime User
For MySQL / MariaDB you can kill the connections in sleep state, like this:

Attention!!! Connect with an user which have the permission to KILL CONNECTION (like root).

B4X: 
Sub Process_Globals
  '-----------------------------------
  ' MariaDB Connection
  Private poolRemote As ConnectionPool
  Private db_server As String = "localhost"
  Private db_name As String = "test"
  Private db_user As String = "root"
  Private db_password As String = "test"
  Private SleepMaxValue As Int = 90

  Private Timer1 As Timer
End Sub

Sub AppStart (Args() As String)
  Dim jSQL As String = "jdbc:mysql://{server}/{db}?characterEncoding=utf8"
  jSQL = jSQL.Replace("{server}", db_server)
  jSQL = jSQL.Replace("{db}", db_name)
  poolRemote.Initialize("com.mysql.jdbc.Driver", jSQL, db_user, db_password)

  '!!! Very important, choose a big value for the timer, otherwise you will be banned by the MySQL server
  Timer1.Initialize("Timer1", SleepMaxValue * 1000)
  Timer1.Enabled = True

  ' . . .
End Sub

Private Sub Timer1_Tick
  KillConnections(SleepMaxValue, db_name)
End Sub

'-----------------------------------------------------
Sub KillConnections(timeout As Int, dbName As String)
  Try
    Dim sql As String = ""
    sql = "SELECT GROUP_CONCAT(CONCAT('KILL CONNECTION ',ID,';') SEPARATOR ' ') KillConnections " & CRLF
    sql = sql & "FROM information_schema.processlist " & CRLF
    sql = sql & "WHERE `Time`>{0} " & CRLF
    sql = sql & "AND `DB`='{1}' " & CRLF
    sql = sql & "AND `Command`='Sleep' " & CRLF
    sql = sql & "AND `User`<>'system user' " & CRLF
    sql = sql.Replace("{0}", timeout)
    sql = sql.Replace("{1}", dbName)

    Log("KillConnections: " & "Try to kill Sleep connections")
    Private sqlTmp As SQL = poolRemote.GetConnection()
    sqlTmp.ExecQueryAsync("SQLKillConnections", sql, Null)
    ' ------>
    'continue in SQLKillConnections_QueryComplete
  Catch
    sqlTmp.Close()
    Log("KillConnections Error: " & LastException)
  End Try
End Sub

Sub SQLKillConnections_QueryComplete(Success As Boolean, Crsr As ResultSet)
  Try
    'get the connection
    Dim sqlTmp As SQL = Sender

    If Success Then
      'read server response
      Dim sqlCmd_ As String = ""
      Do While Crsr.NextRow()
        sqlCmd_ = Crsr.GetString2(0)
        Log("SQLKillConnections_QueryComplete: " & sqlCmd_)
      Loop
      Crsr.Close()
      sqlTmp.Close()

      '-------------------------------------
      'execute kill connections, one by one
      Dim sql As String = sqlCmd_
      If sql <> Null Then
        Private sqlTmp1 As SQL = poolRemote.GetConnection()

        Dim strSplit() As String = Regex.Split(";", sql)
        For Each s_ As String In strSplit
          If s_.Trim() <> "" Then
            sqlTmp1.AddNonQueryToBatch(s_, Null)
          End If
        Next
        sqlTmp1.ExecNonQueryBatch("SQLKillConnectionsResult")
        ' ------>
        'continue in SQLKillConnectionsResult_NonQueryComplete
      End If
    Else
      sqlTmp1.Close()
      sqlTmp.Close()
    End If
  Catch
    sqlTmp1.Close()
    sqlTmp.Close()
    Log("SQLKillConnections_QueryComplete Error: " & LastException)
  End Try
End Sub

Sub SQLKillConnectionsResult_NonQueryComplete(Success As Boolean)
  Try
    'get the connection
    Dim sqlTmp As SQL = Sender

    Log("SQLKillConnectionsResult_NonQueryComplete: " & Success)
    If Success Then
      Log("SQLKillConnectionsResult_NonQueryComplete: " & "All sleep connections are closed")
    Else
      Log("SQLKillConnectionsResult_NonQueryComplete: " & LastException)
    End If

    sqlTmp.Close()
  Catch
    sqlTmp.Close()
    Log("SQLSendToServer_NonQueryComplete Error: " & LastException)
  End Try
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question PoolConnection Problem : Connection has timed out
Replies
9
Views
3K
Waldemar Lima
  • Question
B4J Question Connectionpool Mysql
Replies
2
Views
1K
jayel
  • Question
B4J Question < SOLVED> ConnectionPool Issue
Replies
0
Views
1K
Harris
  • Article
B4J Tutorial [WebApp] Concurrent access to SQLite databases
Replies
7
Views
24K
Erel
  • Locked
  • Article
B4J Tutorial [Server] Building web servers with B4J
2 3 4
Replies
75
Views
90K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…