• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Retry Failed Insert Attempt?

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am working on an application that sends large quantities of data over the Internet to a remote Mysql database. When I wrote the application and tested locally, everything worked fine & dandy, but when I ran it on remote database I began getting lots of "Communications link failure" errors.

I have managed to compensate for the vast majority of these errors by performing a validity check on the Connection before each insert is made.

Code ran before each update is run ...



Code to get connection in MysqlDatabaseProxy class ...



Validity check ...




Connection validation checks work 99% of the time, but I am still getting isolated link failure errors.

The reason for this is that even though the connection is validated before an insert is made, the connection can still fail on any particular insert.

Is there a way to perform a check on the success on an insert based on the occurrence of a link failure exception and then have the program try again?


Example exception thrown


Any advice will be greatly appreciated.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Aryeh,
Why not check the SQL state on the actual insert as opposed to the connection? Then if you get the error one time - try again. For more than once give up so you don't have an infinite loop.

Also, have you considered batching the inserts so you have less trips to the remote database?
 
Aryeh Golob
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the tips Jeanne,

I cannot check the result of the insert statement, because Sql link state error exception is thrown before I can get the status of the insert.

I am looking into re-architecting to accommodate batch inserts, but am nevertheless concerned about what happens if the link goes down in the middle, so I will take things step by step.

In the interim, I am now having all Connection's accessed through a globally accessible static method and am performing my validity/reconnect checks there.

I put the insert statements as well as exception handling inside a loop and reattempt a failed insert a set number of times before aborting.



Thanks again for the help.

-Aryeh
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic