How to retry a database connection

Verified
Added by iNTERFACEWARE

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

Source Code
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
Description
Retry an ODBC query to a MySQL DB when an error occurs, can be adapted for retrying LLP etc.
Usage Details

This example shows how to retry an ODBC query to a MySQL database. When an error occurs we simply retry the query.

This example will need to be modified for production use, as retrying all DB errors is not ideal. For example: This code would retry a query to a non-existent table, which is not what you want. Best practice is to test for the specific errors you want to retry and either log an error or stop the channel.

See this example Customize the retry logic using an error function for more information.

How to use the code:

  • Use a From Translator component
  • Either paste the code into the script or load the attached project zip file
  • Adapt the code to suit your requirements

See this article for various ways to adapt this base code: Retrying unreliable external resources.