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 <b...@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 <b...@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
