B4J Question MySQL timeout

[udg]

Hi all,

I'm experiencing a timeout problem due to some prolonged inactivity. My system is made up of an B4A app that sends command codes to a B4J webserver which in turn builds appropriate SQL commands for a MySQL DBMS residing on the same machine as the webserver.

What seems the reason for the error showed in the attached png, is that at some time the SQL object initialized in the webserver looses contact with MySQL so, when it receive its next command code fron my app, it replies with an error 500 and the attached message.

I tried the following without success:

sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/mytestdb?characterEncoding=utf8&autoReconnect=true","user","pwd")

Although a note for Connector/J advices not to rely on the autoReconnect option, I tried it in the hope it could re-establish the connection. In a way it does, since sending again the same command from my app it succedes, but I'd like to intercept the initial disconnection at the webserver and immediately try again the command instead of returning at the mobile app and waiting for the user to repeat it.

So, probably my question should be about an eventual SQL object status to check before and after its use.

udg

 

[keirS]

MySql can terminate connections due to inactivity. What is the wait_timeout and interactive time out set to in the my.cnf file?

 

[udg]

Hi KeirS,
thank you for your reply. My.cnf is not under my control, anyway those values are not present in it so I presume that defaults apply.
Anyway, my problem is not how long MySql keeps open an idle connection (I'll shorten that rather than the opposite) but that it seems I don't have a mean to know beforehand whether a connection is open/established or not when I'm ready to send a command.

A good enhancement could be th ExecQuery statements to return a status code.

As an alternative, I could do with an "automatic" retry mechanism based on a feature like the ping command. On my webserver I could precede any MySQL command with something like:
1. MaxRetries = 3
2. Ping MySql connection
3. If connection Ok the return ConnOk else increment Retries
4. MaxRetries reached and still No connevtion. return ConnFail

That way my webserver will know whether to procede issuing its command to MySQL or signal the problem to the user.

Another option could be a timer sending out a command every xx seconds/minutes.

udg

 

[udg]

Hi all,
I read about a "magic" command that may solve my problem. I've still to test it but I'll share it here.
For Connector/j you could use:

/* ping */select 1

Or, in general, send the "select 1" command which has a very low overhead and will succed or fail based on the opennes of you DB connection.

Let me experiment a bit and I'll let you know.

 

[keirS]

You could do that. However I would do this:

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Public MySQLConnection As SQL
  
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.SetFormStyle("UNIFIED")
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
    MySQLConnection.Initialize2("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/classicmodels","keirs","******i")
    Log(IsSQLConnectionValid(MySQLConnection,5))
  
  
  
End Sub

Sub IsSQLConnectionValid(Connection As SQL,TimeOutSecs As Int) As Boolean
    Dim J As JavaObject = Connection
    J = J.GetFieldJO("connection")
    Return J.RunMethod("isValid",Array As Object(TimeOutSecs))
  
  
End Sub

The sub calls the isValid method of the JDBC connection interface.

 

[udg]

Hi keirS,
thank you very much. I too prefer your solution.
Should I use it like:

Sub TrySQLCommand as boolean
sql1.BeginTransaction
Try
  sql1.ExecQuery(..)
  sql1.TransactionSuccessful
Catch
     Log(LastException)
 End Try
dim vc as boolean=IsSQLConnectionValid(sql1,3)
sql1.EndTransaction
return vc
end sub

TIA

 

[keirS]

I would do something like this.

If IsSQLConnectionValid(sql1,3) Then
     Return TrySQLCommand()
Else
  sql1 = Null
  Dim sql1 AS SQL
  SQL.Initalize2(myConnection,username,password)
  Return TrySQLCommand()

End If

Sub TrySQLCommand As Boolean
      sql1.BeginTransaction
  Try
       sql1.BeginTransaction
       sql1.ExecQuery(..)
       sql1.TransactionSuccessful
       Return True
   Catch
        Log(LastException)
        Return False
    End Try
End Sub

 

[Erel]

The correct solution is to use ConnectionPool (jServer library). It is simple and takes care of such issues.

 

[udg]

Hi @Erel,
in your thread Building webserver with b4J, you state: "An alternative for a connections pool is a process global SQL variable. However for it to work properly all the handlers should be single threaded handlers."

1. So my current setup should be considered wrong since I have multi-threaded handlers and a single process global SQL var. Can we consider it a bit less wrong because that SQL var is used exclusively in a code module which performs all the DB related actions (including calling subs from DBUtils) or concurrent activations of handlers will end up in a shared, intermixed and dangerous use of the var?

2. Adopting the pool strategy, should I simply substitute the SQL var with its pool counterpart adding the pool object release after the already done cursor release?

3. Looking at your DBUtils module, I see that you use transactions only in some subs while others don't not embed code in an explicit transaction. Do you advice to always start an explicit transaction in the code calling DB-related functions?
I mean something like this:

a. acquire connection from the pool
b. start transaction
c. call DB related sub (this could have its own explicit transaction and alway open/close its own cursor, if needed)
d. stop transaction
e. release pool object

TIA

 

[Erel]

There is no reason to use a global SQL object instead of connection pool. Remote connections will break at some point. A connection pool takes care of creating a new connection when needed.

2. Yes.

3. Only code that writes to the database needs to be in a transaction. The important point is to always close the connection.

Dim sql As SQL = Main.Pool.GetConnection
Try
 '...
Catch
 '...
End Try
sql.Close

 

[udg]

Ok, thanks.
So my a-e scheme is valid for DB writing occurences while could be simplified eliminating the explicit transaction for readings?

Dim sql As SQL = Main.Pool.GetConnection
sql.BeginTransaction
Try
  dbutils.UpdateRecord(sql,..)
  sql.TransactionSuccessful
Catch
  SetError(-12, LastException) 'using this to differenziate between failures
  sql.Rollback
EndTry
sql.Close

Reading data (e.g. SELECT) would get rid off the transactions stuff while retaining the try..catch for errors and proper closing of the cursor, right?

BTW, is there a way to know the exact error code produced by MySQL? I mean xxx for "date in wrong format", yyy for "syntax error", etc ?

udg

 

[Erel]

So my a-e scheme is valid for DB writing occurences while could be simplified eliminating the explicit transaction for readings?

Yes, your code is correct.

BTW, is there a way to know the exact error code produced by MySQL?

It is better to start a new thread for a new question...

 

[udg]

It is better to start a new thread for a new question...

You are right. I'll post it in the Wish section.

Thank you for your help.

 

[keirS]

3. Only code that writes to the database needs to be in a transaction. The important point is to always close the connection.

Why? The default mode for a connection object is auto-commit which implicitly wraps each update to the DB in a transaction. The following code returns true so it appears that the SQL library does not override the default behavior unless you actually call begin transaction in which case it appears to set auto-commit to false?

Dim J As JavaObject =SQLConnection
J = J.GetFieldJO("connection")
Return J.RunMethod("getAutoCommit",Null)

 

[Erel]

Creating a transaction for SELECT queries is less critical or important.