Retry a database connection

Introduction

Sometimes errors can occur when you are connecting to a Database, because network connections to a database are never 100% perfect. And if you connect to a remote database (over the internet) you are even more likely to have connections issues.

  • Network connections can sometimes fail.
  • Database can be temporarily overloaded with traffic.

While database connection issues are infrequent they can be very frustrating — so you cannot rely every request to succeed on the first try. The good news is it’s easy to use retry logic in the Translator to make your interfaces reliable, even when database connections fail.

The example code on this page retries MySQL ODBC connection errors, but it can easily be adapted for any for any database connection.

If you need more help using the retry module please contact us at support@interfaceware.com.

Note: You should only use retry for database errors that indicate transitory problems.

Other errors can be more serious and you should stop the interface instead.

Tip: We use the same approach (retry module + error function) to retry a web service. And it can easily be adapted for any unreliable connection/resource, like LLP etc.

If you would like to experiment with some sample code that uses the retry module then you can import the Retry periodic failure channel from the Builtin: Iguana Tools repository.

Task [top]

Retry a Database Query when an error occurs.

Implementation [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.

Code [top]

local retry = require 'retry'
 
function main(Data)
   local R, R2, M = retry.call{func=DoInsert, retry=1000, pause=10, funcname='DoInsert', errorfunc=DbError}
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 DbError(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
en

How it works [top]

We use the retry.lua module with a custom error handler function DbError() that handles common database error codes. The code forms a template for database retry scenarios. You will need to customize the error handler function DbError() for the database you are using, and to meet your own business requirements.

Possible DbError() error handler function Customizations:

  • Change error codes to match the database that you are using
  • Add other error codes that you require
  • Change the wording of the logged errors (though we tried to make them helpful)
  • Add more complex business logic for error handling

Error conditions handled:

  1. The MySQL database connection has failed:
    • 2002: Can’t connect to local MySQL server through socket …
    • 2006: The MySQL server has gone away

More Information [top]

Tagged: