SQL reconnect procedures

Bob Sneidar bobs at twft.com
Tue Oct 9 18:02:54 CDT 2012


Thanks Pete. 

What I do now is I have my own library of calls for opening a connection (which is really my own internal system of checking to make sure the parameters are in order and setting a global if successful) then for reconnecting, which is really making a connection in the LC sense, then idling, which is really disconnecting. 

I have a database connection card for all my apps where a user can enter information like the database server url, the user name and password, and type of database etc. When opening a database enabled app, I go to that card with the screen locked, which attempts the connection and sets a global to true and another global with the database id. All "reconnect" calls in the future check for the connected global, then open a connection and attempt a basic query. If all is well, the function returns true and away we go. 

I don't leave connections open during user interaction. Old habit I learned when attempting to resolve some deadlocks in Foxpro due to bad programming habits. I basically get to the point where I have all the info from the user I need, then I open the connection, do my business and then close it. This has resulted in a very stable methodology for me. Others may think it overkill. 

Still, if something can go wrong... you know the old adage. I will have to poke around a bit to see what the manual may have to say on premature disconnects during transactions.

Bob


On Oct 9, 2012, at 3:28 PM, Peter Haworth wrote:

> I'm assuming this is mySQL?
> 
> There is a server setting that determines the inactivity timeout period.
> It's either wait_timeout for a non-interactive client or
> interactive_timeout for an interactive client.  I assume that LC opens
> mySQL dbs as an interactive client.  According to the mySQL documentation,
> if you get an error on an SQL command because the connection has timed out,
> all you have to do is re-issue the command and the connection will be
> re-established.
> 
> However, I'm not sure how that plays with LC since it will likely flag an
> error on the database ID that you pass to it and, as I recall, that's a
> runtime error in the script, not an error code (don;t understand why that
> is the case but that's another issue).  If you can find out what the error
> is, you could wrap all your db calls in try/catch, check for the error and
> proceed accordingly. Alternatively, I guess you could keep track of the
> interval since the last SQL command you issue and the one you are about to
> send and if it's more than the timeout, re-establish the connection
> yourself.
> 
> On the issue of a network error, I'm not sure what would happen.  If mySQL
> is in the middle of a transaction, I'd like to think it would not execute
> the COMMIT if the connection to the client had gone away, but that would
> depend on when it becomes aware of the problem.  I think you can be pretty
> certain that you would not end up with parts of the transaction being
> reflected in the database and others not.  Either the COMMIT executes in
> which case the complete transaction will be in the database, or it doesn't
> and none of the transaction will be in the database.  There's also the
> question of how LC detects that the connection died in the middle of a
> transaction and if/how it communicates that back to your script.
> 
> Interesting topic!
> 
> Pete
> lcSQL Software <http://www.lcsql.com>
> 
> 
> 
> On Tue, Oct 9, 2012 at 2:50 PM, Bob Sneidar <bobs at twft.com> wrote:
> 
>> I was getting disconnected from On-Rev within an hour of inactivity, and
>> all my database calls failed when the connection was closed by the host.
>> Also, certain network outages or problems can cause managed switches to
>> reset, notably excessive broadcasts, and when this happens, the SQL
>> connection is reset. Same result. I have experienced this first hand in our
>> accounting application when someone had installed some mesh wireless in
>> bridge mode, and we found out later that this cause a low level packet
>> storm that was wreaking havoc with our managed switches.
>> 
>> I'm not sure how you could rollback a transaction if the connection had
>> been reset and the server did it automatically. I guess what I am asking is
>> how do SQL servers deal with connection failures in the middle of a
>> transaction. Does it rollback or commit? If commit, that would be bad if
>> the transaction were not complete. If rollback, than attempting to restore
>> the connection and continue with the transaction could be equally bad.
>> 
>> Bob
>> 
>> 
>> On Oct 9, 2012, at 12:44 PM, stephen barncard wrote:
>> 
>>> well "COMMIT/ROLLBACK" should handle the possibility of data loss.
>>> 
>>> As far as "connections" I've found that there is little time difference
>>> between being 'always connected' and making a connection open and close
>> per
>>> transaction, unless one is hitting it repeatedly for a single result (as
>> I
>>> had to to for a certain database system a few years ago). A greater time
>>> lag is introduced when getting the returned data than the time required
>> to
>>> connect. This is assuming that the DNS is cached; the first fetch will
>> take
>>> longer.
>>> 
>>> On Tue, Oct 9, 2012 at 11:23 AM, Bob Sneidar <bobs at twft.com> wrote:
>>> 
>>>> I've been pondering what the ramifications to sql session disconnects
>> are.
>>>> I have seen in other "professionally developed" applications, like our
>>>> accounting software used here, that if the user gets disconnected for
>>>> anything longer than a few seconds, the software completely bails out
>>>> through a series of errors that I have to abort to get the app to quit.
>> Not
>>>> very graceful. I want to make my software more robust.
>>>> 
>>>> So I am wondering what happens when there is a transaction in effect,
>> and
>>>> there is an unexpected disconnect. Will reconnecting restore the
>>>> transaction state or is it flushed after the sql timeout? If I can
>>>> reconnect and the transaction is still in effect, well and good, but if
>> I
>>>> proceed as though the transaction is still in effect and it is not, bad
>>>> things could conceivably happen.
>>>> 
>>>> Is completely bailing out the best approach after all?
>>>> 
>>>> Bob
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> use-livecode at lists.runrev.com
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> 
>>> 
>>> 
>>> Stephen Barncard
>>> San Francisco Ca. USA
>>> 
>>> more about sqb  <http://www.google.com/profiles/sbarncar>
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode




More information about the use-livecode mailing list