B4J Question [SOLVED] Hostgator - too many requests to database

Discussion in 'B4J Questions' started by jroriz, Aug 20, 2018.

  1. jroriz

    jroriz Active Member Licensed User

    Hi everyone.
    I developed an application that accesses MySql remotely.
    The provider (Hostgator) sent me an alert saying that I am sending too many requests to the database.
    The log of requests they sent me does not help much:

    I'm guessing that may be the way I'm accessing the database.

    Is everything correct with my code?

    Code:
    Sub Process_Globals
        
    Dim pool As ConnectionPool, Hostgator As SQL
    end sub

    Sub AppStart (Form1 As Form, Args() As String)

        
    server = "jdbc:mysql://mydomain.com.br/myfolder"
        
        
    Try
            pool.Initialize(
    "com.mysql.cj.jdbc.Driver"server"user""password")
        
    Catch
            
    log("Connection error")
            
    ExitApplication       
        
    End Try
    end sub

    Sub Write(query As StringAs Boolean

        
    Try
            Hostgator = pool.GetConnection

            Hostgator.BeginTransaction
            Hostgator.ExecNonQuery(query)
            Hostgator.TransactionSuccessful
            Hostgator.Close
            
    Return True
        
    Catch
            
    Log(LastException)

            
    '''''''''''''''''''''''''
            ' hostgator.close here? '
            '''''''''''''''''''''''''

            
    Return False
            
        
    End Try
        
    End Sub

    Sub Read(q As StringAs Object

        
    Try
            Hostgator = pool.GetConnection
            
    Dim tmp As Object
            tmp = Hostgator.ExecQuerySingleResult(q)
            Hostgator.Close
        
    Catch
            
    Log(LastException)

            
    '''''''''''''''''''''''''
            ' hostgator.close here? '
            '''''''''''''''''''''''''

            
    Return Null
        
    End Try

        
    Return tmp
    End Sub
     
  2. KMatle

    KMatle Expert Licensed User

    So: How many requests do you send in which time? How many clients? Is your plan limited? Did you check if your queries must be optimized/running too long?
     
  3. jroriz

    jroriz Active Member Licensed User

    Thanks for the reply.

    Requests are random.
    Few customers.
    The plan has limits, but they are far from being hit.
    The queries are EXTREMELY simple.

    What about my code? Is he correct?
     
  4. Erel

    Erel Administrator Staff Member Licensed User

  5. jroriz

    jroriz Active Member Licensed User

    Thank you.
    I will try pool maximum size.
     
  6. jroriz

    jroriz Active Member Licensed User

    In the appstart sub I initialize the pool object, which is global.

    The user will terminate the application by closing the form.

    Will the session remain open? Because the pool.closepool command was not executed.
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    It is better to close it in MainForm_Closed event.
     
  8. Enrique Gonzalez R

    Enrique Gonzalez R Well-Known Member Licensed User

    Depending on the quality of the internet connection you may want to keep it open for a long time (close when form is closed) or if connection is poor. Open... Do stuff ... Close it. Then you will not have to check every time if the connection is already closed.
     
  9. jroriz

    jroriz Active Member Licensed User

    "It is better" means it is not mandatory?

    What happens to the connection when an application closes for an unplanned error?

    What if I open the connection EVERY time, run the command and close it, as Enrique Gonzalez suggested?

    Code:
    Sub RunQuery(query As String)
       
        
    Dim pool As ConnectionPool
        
    Dim hostgator as sql

        
    Try
            pool.Initialize(
    "com.mysql.cj.jdbc.Driver""jdbc:mysql://mydomain.com.br/mydatabase""user""password")
            
    Dim jo As JavaObject = pool
            jo.RunMethod(
    "setMaxPoolSize"Array As Object(3))
        
    Catch
            
    Log(LastException)
            pool.ClosePool
            
    ExitApplication
        
    End Try

        
    Try
            Hostgator = pool.GetConnection

            Hostgator.BeginTransaction
            Hostgator.ExecNonQuery(query)
            Hostgator.TransactionSuccessful
        
    Catch
            
    Log(LastException)
        
    End Try
       
        Hostgator.Close
        pool.ClosePool
    End Sub
     
    Last edited: Aug 23, 2018
  10. OliverA

    OliverA Expert Licensed User

    You are now on the wrong path. The connection pool is global, just as you had it. The SQL object is local to the method/sub-routine, just as @Erel shows in his sample code. You asked about closing the pool and you were given the option to do that upon closing the Main form (ending the application). That's a one time thing.
     
  11. OliverA

    OliverA Expert Licensed User

    BTW, if you application accesses the DB infrequently, you could just do away with the connection pool and just use the SQL object. It all depends on what the application does and how much you access the DB and how many clients will do this at the same time.

    Code:
    Sub RunQuery(query As String)
        
        
    Dim Hostgator As SQL
        
        
    Try
            Hostgator.Initialize2(
    "com.mysql.cj.jdbc.Driver""jdbc:mysql://mydomain.com.br/mydatabase""user""password")
        
    Catch
            
    Log(LastException)
            Hostgator.Close
            
    ExitApplication
        
    End Try

        
    Try
            Hostgator.BeginTransaction
            Hostgator.ExecNonQuery(query)
            Hostgator.TransactionSuccessful
        
    Catch
            
    Log(LastException)
        
    End Try
        
        Hostgator.Close

    End Sub
    Note: untested code, just yours w/slight modifications.
     
    jroriz likes this.
  12. jroriz

    jroriz Active Member Licensed User

    In the above case, both hostgator and connectionpool would be local instead of global.
     
  13. jroriz

    jroriz Active Member Licensed User

    It is a distributed application.

    Several restaurants use the application, which is waiting for an order to be confirmed by other application, and only then goes into the database and records 1 or 2 records.

    But the database is remote.

    The database does not have to be open all the time.

    In this scenario is the connectionpool dispensable?
     
    Last edited: Aug 23, 2018
  14. OliverA

    OliverA Expert Licensed User

    Most likely. Connectionpool is mainly useful if your local application does many remote calls to the server. It can also be used for retrying when connection failure happens. But in your case it may be the wrong tool. One way to find out is to use the other/non-pooling approach.
     
    jroriz likes this.
  15. Enrique Gonzalez R

    Enrique Gonzalez R Well-Known Member Licensed User

    Non pooling approach for remote connections can cause problems, because if you lose connection with the server for one milisecond, you lose the conection completly, that is because Erel recommends the pool connection.
     
  16. OliverA

    OliverA Expert Licensed User

    Note: I hope someone chimes in on the correctness of this (my signature may apply in this case)

    I'm pretty sure that the disconnect happens anyways unless you have a JDBC driver that supports reconnecting an existing connection to a DB that also supports that. Otherwise, once a connection is broken, it is broken. Pooling can re-establish another connection for you without having to manage that part. Pooling also allows the creation of a number of connections that are kept alive across which SQL statements/DB functions can be executed. This can help in high traffic environments by alleviating the SQL server with a bunch of create connection/drop connection requests.

    In this one special case here (this thread), the pooling and multiple open connections to the DB may be causing issues. Therefore, open and closing connections allows the reuse of the server resources (sort of multiplexing a limited number of connections available on the server against a greater number of clients). I'm not recommending this solution for every case, just this one particular case. Plus if I'm going to open and close the connections with each call anyways, wrapping them in Catch/Try, then I might as well get rid of the complexity of pooling.
     
    jroriz likes this.
  17. Harris

    Harris Well-Known Member Licensed User

    I use pool (as recommended by Erel) . Without it - had many issues... With it - no probs...

    Here is how I do it in ABM (supplied by alwaysbusy). Never a problem - yet this is my own server - NOT Hostgator (who knows what wrenches they toss into the cogs).


    Init MySQL - set driver and pool size ONCE.

    Sub AppStart (Args() As String) ' in main sub
    ' change if you want to connect to a mySQL server using connection pool
    DBM.InitializeMySQL("jdbc:mysql://localhost/harris?characterEncoding=utf8&useSSL=false", "mhwxxxxx", "b12xxxxx", 150) ' 150 is pool size

    Code:
    '  DBM code module

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

        
    ' change pool size...  default is 10 (or maybe less)
        Dim jo As JavaObject = pool
        jo.RunMethod(
    "setMaxPoolSize"Array(poolSize))

    '  The get and close methods for MySQL pooling

    Sub GetSQL() As SQL
            
    Return pool.GetConnection      
    End Sub

    Sub CloseSQL(mySQL As SQL)
            mySQL.Close
    End Sub


    ' ABM update method...

    Sub SQLUpdate(SQL As SQL, Query As StringAs Int
        
    Dim res As Int
        
    Try
            
    SQL.ExecNonQuery(Query)              
            res = 
    0
        
    Catch
            
    Log(" UPD ERR: "&LastException)      
            res = -
    99999999
        
    End Try          
        
    Return res
    End Sub


    End Sub

    Typical call for a query (update and select)....

    Code:
    ' update query....

    Sub SetShift(pk As String , sid As Long)  

      
    Dim sql As SQL = DBM.GetSQL
      DBM.SQLUpdate(
    sql,"UPDATE loadmast SET sourceid = "&sid &" WHERE pk = "&pk)
    '  Log(" setting shift id: "&sid&"  pk: "&pk)
      DBM.CloseSQL(sql)
       
    End Sub

    '  Simple Select query using ResultSet...

    Sub GetDriverName(drv As Int) As String
      
        
    Dim SQL As SQL = DBM.GetSQL
        
    Dim res As ResultSet
        
    Dim nam As String = "Not Found"
        res = 
    SQL.ExecQuery("Select * from emp where PK = "&drv)
          
    Do While res.NextRow
          nam = res.GetString(
    "Last_name")&", "&res.GetString("First_name")
        
    Loop  
        res.Close
        DBM.CloseSQL(
    SQL)
        
    Return nam
    End Sub
    You can Try and Catch to your hearts content... which is always a good idea.
    The point is - init once - open and close with every query - and get your own server and screw Hostgator - seems there lies the issue.

    Thanks
     
    Last edited: Aug 24, 2018
    jroriz likes this.
  18. jroriz

    jroriz Active Member Licensed User

    That was what hostgator sent me, and BLOCKED MY SITE with password!

    Like this: Empty STATE and empty INFO.

    Apparently the problem is not happening anymore, but at the beginning I did a lot of testing, and I think the problem was due to something I did wrong related to the connection to the database. There may have been some connection without closing.

    Thank you all for the attention, and I conclude that in my particular case, I do not need to use the connectionpool because I make few accesses to the database, with simple queries and with few records and few users.
     
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