Automatic Retry on Error

Databases
1

I am currently working on a project that has to communicate intensively with databases in a very unstable network. The number of lost packets is very high and therefore errors such as timeouts, dataloss and the like often occur.

Since I have to live with the network as it is, I am trying to teach my project to deal with these errors better. Better should mean that the user may notice that something is not running smoothly, but the project is trying its best.

That is why I added the following method to my mySQLCommunityServer subclass:

Public Function Load(SQLQuerry As String, ParamArray Params() As Variant) As RowSet
  Var rs As RowSet
  Var RetryCounter As Integer = 0
  
  While RetryCounter < 3
    
    Try
      
      If Not Self.IsConnected Then Self.Connect
      
      If Params <> Nil Then
        
        rs = Self.SelectSQL(SQLQuerry, Params)
        
      Else
        
        rs = Self.SelectSQL(SQLQuerry)
        
      End If
      
      Exit While
      
    Catch err As DatabaseException
      
      Self.ReportError(err, "OTRS Database Connection (" + SQLQuerry + ")")
      RetryCounter = RetryCounter + 1
      
    End Try
    
    If DebugBuild And RetryCounter > 0 Then Break
    
  Wend
  
  Return rs
End Function

It seems to work quite well, but I would like to hear further opinions and am open to suggestions for improvement.

Thank you for constructive criticism. :slight_smile:

2

Just for the sake of completeness:

Public Function Save(SQLQuerry As String, ParamArray Params() As Variant) As Boolean

  Var RetryCounter As Integer = 0
  While RetryCounter < 3
    Try
      If Params <> Nil Then
        Self.ExecuteSQL(SQLQuerry, Params)
      Else
        Self.ExecuteSQL(SQLQuerry)
      End If
      Return True
    Catch err As DatabaseException
      Self.ReportError(err, "OTRS Database Connection (" + SQLQuerry + ")")
      RetryCounter = RetryCounter + 1
    End Try
  Wend
  Return False

End Function

BTW: It is intentional that the errors are not returned to the calling functions.

3

On a query error, I attempt another connection, then redo the query. You can reboot the database server and the user will never notice.

1 Like
4

There are many Users working at the same time with my App and various Services are depending on those Servers. The Issues are only at one Site (long distance connections via Satelite and WiMax/Unify).

A reconnect is agood idea. Thank you. :slight_smile:

5

So do we…

…except if it’s a query within a Transaction :wink:

Would you rename that to SQLQuery, please? :melting_face:

1 Like
6

What we’re doing differently is that we don’t try to re-try/connect on every DatabaseException/Error. It doesn’t make sense should you e.g. pass in an invalid SQL Statement.

It certainly makes sense when Connection is lost (-> re-connect), on Timeouts or Locks (try again later, after increasing delays).

1 Like
7

Of course. We use a more elaborate mechanism.

8

I wouldn’t do this, as Self.IsConnected makes a roundtrip to the database (I know this because there was a bug in the postgres-Plugin and I saw the invalid SQL in the log file of my server. It has been fixed by William in the 2024r4 release I think) and thus slows your “Load” method down. The principal strategy should be to assume that the query should “just work” and only react accordingly to errors if they actually occur. So if your databaseexception indicates you lost (or never established) the connection only then should you reconnect. No biggie of course.

1 Like
9

A db.Connect is fast, i think i’ll just (re)connect before i start a new transaction. :slight_smile:

10

How about this (still very simplified):

While RetryCounter < 3
  Try
    If RetryCounter > 0 Then Self.Connect // New
    If Params <> Nil Then
      Self.ExecuteSQL(SQLQuery, Params)
    Else
      Self.ExecuteSQL(SQLQuery)
    End If
    Exit While
  Catch err As DatabaseException
    Self.ReportError(err, "OTRS Database Connection (" + SQLQuery + ")")
    RetryCounter = RetryCounter + 1
    Self.RollbackTransaction // New
    Self.Close // New
  End Try
Wend
11

Until i have a smarter solution, i’ve limited my approach to select statements and to execute statements that are not done within a transaction:

For Select:

Public Function Load(SQLQuery As String, ParamArray Params() As Variant) As RowSet
  Var rs As RowSet
  Var RetryCounter As Integer = 0
  
  While RetryCounter < 3
    
    Try
      
      If RetryCounter > 0 Then Self.Connect
      
      If Params <> Nil Then
        
        rs = Self.SelectSQL(SQLQuery, Params)
        
      Else
        
        rs = Self.SelectSQL(SQLQuery)
        
      End If
      
      Exit While
      
    Catch err As DatabaseException
      
      Self.ReportError(err, "OTRS Database Connection (" + SQLQuery + ")")
      RetryCounter = RetryCounter + 1
      Self.Close
      
    End Try
    
  Wend
  
  Return rs
End Function

And for Execute:

Public Function SaveWithoutTransaction(SQLQuery As String, ParamArray Params() As Variant) As Boolean
  Var RetryCounter As Integer = 0
  
  While RetryCounter < 3
    
    Try
      
      If RetryCounter > 0 Then Self.Connect
      
      If Params <> Nil Then
        
        Self.ExecuteSQL(SQLQuery, Params)
        
      Else
        
        Self.ExecuteSQL(SQLQuery)
        
      End If
      
      Return True
      
    Catch err As DatabaseException
      
      Self.ReportError(err, "OTRS Database Connection (" + SQLQuery + ")")
      Self.Close
      RetryCounter = RetryCounter + 1
      
    End Try
    
  Wend
  
  Return False
End Function

Thanks again to everyone who participated in this discussion. :slight_smile: