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

Discussion in 'B4J Questions' started by jmon, Jul 7, 2014.

  1. jmon

    jmon Well-Known Member Licensed User

    Hi,

    Following this thread : http://www.basic4ppc.com/android/fo...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:
    Code:
    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

    Code:
    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Are you calling SQL.Close after you read the result?
     
  3. jmon

    jmon Well-Known Member Licensed 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.basic4ppc.com/android/fo...ion-not-allowed-after-resultset-closed.42516/, but how about the non async queries is this ok:
    Code:
    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
     
  4. jmon

    jmon Well-Known Member Licensed User

    o_O NO! Arghhh, I messed up on one of them, instead of calling
    Code:
    Dim sq As SQL = sender
    in the QueryComplete event I was calling:
    Code:
    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
     
  5. jmon

    jmon Well-Known Member Licensed 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:
    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.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    You can set it with:
    Code:
    Dim jo As JavaObject = pool
    jo.RunMethod(
    "setMaxPoolSize"Array(x))
     
    jmon likes this.
  7. alienhunter

    alienhunter Active Member Licensed 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
     
  8. jmon

    jmon Well-Known Member Licensed User

    Check that all your connections and cursors are closed properly each time you call them. You can try increasing the connection limit in Mysql: https://major.io/2007/01/24/increase-mysql-connection-limit/

    In my case, with 40 users, I had to increase it to 800, because I have many concurrent connections opened at the same time.
     
    alienhunter likes this.
  9. alienhunter

    alienhunter Active Member Licensed 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:eek: ... reading old code ....
     
  10. jmon

    jmon Well-Known Member Licensed User

  11. balarii

    balarii New Member Licensed 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).


    Code:
    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"sqlNull)
        
    ' ------>
        '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: Oct 11, 2016
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice