Retry a database connection

Introduction

Retry a database using an ODBC query to a MySQL DB when an error occurs, this can be adapted for retrying LLP etc.

You should only retry database errors that indicate transitory problems which can be overcome with enough retries. However other errors can be more serious and may want to stop the interface instead. Fortunately using Iguana gives you complete control over identifying errors and implementing your desired response.

This page presents database retry code that you can copy directly into your project. However if you prefer to experiment with some sample code that uses the retry module then you should consider importing the Retry periodic failure channel from the Builtin: Iguana Tools repository.

Code to Retry a Database [top]

local retry = require 'retry'
 
function main(Data)
   local R, R2, M = retry.call{func=DoInsert, retry=1000, pause=10, funcname='DoInsert', errorfunc=myError}
end
 
function DoInsert(T)
   -- call db.connect each time Iguana Polls
      conn = db.connect{
         api=db.MY_SQL,
         user='root',
         password='', -- no password
         name='test',
         live=true
      }
   
   -- NOTE: query for testing purposes only
   -- replace the query with your select/insert/update code
   local R = conn:query('SELECT * FROM patient')
 
   -- housekeeping (more efficient than garbage collection)
   if conn and conn:check() then conn:close() end 
   
   return R
end
 
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

Using the Retry code [top]

The code uses the retry.lua module for retrying failed database connections.

How to use the retry database code:

  1. Add the retry.lua module to your shared modules in any Translator project.
  2. Then paste the retry database code into the main module.
  3. Modify the code to suit your requirements.

More Information [top]

Leave A Comment?