SQL reconnect procedures

[Peter Haworth]

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
>