SQL reconnect procedures
bobs at twft.com
Tue Oct 9 19:02:54 EDT 2012
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.
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
> 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
> 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!
> 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.
>> 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
>>> transaction, unless one is hitting it repeatedly for a single result (as
>>> 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
>>> connect. This is assuming that the DNS is cached; the first fetch will
>>> 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
>>>> 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.
>>>> very graceful. I want to make my software more robust.
>>>> So I am wondering what happens when there is a transaction in effect,
>>>> 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
>>>> 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?
>>>> use-livecode mailing list
>>>> use-livecode at lists.runrev.com
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>> 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:
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
More information about the Use-livecode