How do I handle retry logic for unreliable external resources?

Database Connection


Our first example shows how we can retry an ODBC query to an MySQL database. If an error occurs we simply retry the query.

Preparation [top]

You will need to have a MySQL ODBC database connection for use with the sample code.

If you match the following credentials, as used in the code, it should work immediately:

Alternatively you could change the db.connect() parameters to connect to a different database.

The database will also need to contain a Patient table so this query from DoInsert() will work:

If you do not have a Patient table then substitute another SQL query that works against your database.

Tip: Some APIs like the ODBC connection API to SQL server can take a long time to time out. The default Iguana script timeout is 5 minutes, so if a database timeout takes longer than this, then the channel will be stopped immediately after the database call returns.

If you want to prevent the channel being stopped you can use iguana.setTimeout() to set a longer timeout for the script.

How it works [top]

We apply the retry module by performing the following steps:

  1. Add require'retry' to the shared module source code, as shown below.
  2. Invoke retry.call() with a table containing these entries:
    • func = The function you wish to call
    • arg1/2/3…N = Multiple arguments to pass in to the function
    • retry (optional) = The maximum number of failed retries
    • pause (optional) = The pause between each retry attempt (in seconds)
    • funcname (optional) = The name of the function ( informational for errors and logging)
  3. Place the call to db.connect() in the DoInsert() function so it runs every time Iguana polls

    Warning: If db.connect() is outside DoInsert() it will raise an error before retry.call() is called, and therefore the retry logic will never be invoked.

If the MySQL database is unavailable then you will get a connection error and retry message. We stopped the MySQL database service/daemon to simulate a connection error:

Tip: If you want to customize the error messages, simply change the generic messages near the top of the retry module:

Screen Shot 2014-03-03 at 14.29.25

Testing The Code [top]

We ran the code in an From Translator component, and then verified that it worked as expected by stopping and starting the MySQL database. Below you can see how the channel dashboard light changes, and how the errors are logged.

The orange light comes on when an error occurs:

Screen Shot 2014-03-03 at 13.51.54

The green light comes on when we recover from the error:

Screen Shot 2014-03-03 at 14.08.36

Here is how the errors look in the Iguana Logs:

Screen Shot 2014-03-03 at 14.21.31

Warning: Be aware that the retry module will retry for all MySQL errors (not just connection errors),which is probably not what you want.

For example if you misspell the patient table name in a SQL Query:

This can be solved by creating an error function that prevents retries for everything except database connection errors, this is then passed as a parameter to retry.call{}:

function myError(Success, ErrMsgOrReturnCode)
   local funcSuccess
   if Success then
      -- successfully read the data
      funcSuccess = true -- don't retry
   else
      -- these are MySQL error codes - they will be different for other databases
      if ErrMsgOrReturnCode.code == 2002 or ErrMsgOrReturnCode.code == 2006 then
         -- retry *only* for failed connection (error 2002 or 2006)
         iguana.logInfo('Retrying DB connection: '..tostring(ErrMsgOrReturnCode))
         funcSuccess = false -- retry
      else
         -- then raise error for all other DB issue (error ~= 2002 or 2006)
         error(tostring(ErrMsgOrReturnCode))
      end
   end
   return funcSuccess
end

See the last page in this section Customize the retry logic using an error function for instructions on using an error function with the retry module.

Adapting the code for your own needs [top]

You can easily adapt the code we supplied to meet your needs. If you are retrying a database (a very common scenario) then you can just add your own database code to the DoInsert() function. If you want to retry another operation then replace DoInsert() with your own function. For example: to access a RESTful web service you could create a DoHttpGet() function using net.http.get(), using our net interface module.

Sample Code [top]

Import the Retry_Database_From_Translator.zip project into a From Translator component, it contains all the necessary code.

Otherwise you can get the code for How to retry a database connection from our repository.
Note: If you don’t already have the retry module you can get it from our repository server.

Leave A Comment?