Determine lost connection with remote SQL-DB

[Joe Byrne]

If an application that accesses a remote MySQL server sits too long, MySQL appears to close the connection. That makes sense, and is not something I want to circumvent, however, I'm looking for ideas on the best way to detect this state and recover from it.

FWIW, I'm using SQLTools Pro and a version 4.1 MySQL server.

[Michael Mattias]

I'll suggest the obvious.... Don't hold the connection open:Open, use, close.

I do this with all DB access, never a problem with timeouts or anything else.

Nothing to recover from, so end of problem.

I do the same thing with disk files: I never hold open waiting on a user action.

MCM

[Joe Byrne]

Ok, that seems to work. I do notice a 'slight' performance hit in one or two places, but nothing that's a show stopper. I imagine that it will totally disappear when the app is moved to a LAN instead of accessing across the Internet.

Appreciate the recommendation!

[Don Dickinson]

i wonder if the mysql odbc driver can be told to implement connection pooling? if it can, this might eliminate your issue. the open/close commands you execute simply connect you to a pre-existing connection (except the first connects) and the close command just frees the driver to use the connection with another call. under this scenario it would be up to the odbc driver to determine if the connection is no longer valid. perhaps the odbc driver has a clever and speedy way of doing this.

just a thought.

-don

[Michael Mattias]

>wonder if the mysql odbc driver can be told to implement connection pooling

SqlSetEnvAttr
...
SQL_ATTR_CONNECTION_POOLING
(ODBC 3.0)

[Don Dickinson]

yeah ... but i wonder if his particular odbc driver implements it. i haven't been enthralled with the odbc drivers for free dbms's (mysql, sqlite, etc)

-don

[John McWilliams]

I'll suggest the obvious.... Don't hold the connection open:Open, use, close.

I do this with all DB access, never a problem with timeouts or anything else.

Nothing to recover from, so end of problem.

I do the same thing with disk files: I never hold open waiting on a user action.

MCM

Same here. I never leave anything open. The overhead to open and close is small compared to "dropped connections".

[Michael Mattias]

>>SqlSetEnvAttr
>> SQL_ATTR_CONNECTION_POOLING

> yeah ... but i wonder if his particular odbc driver implements it

SQLSTATE
HYC00 Optional feature not implemented The value specified for the argument Attribute was a valid ODBC environment attribute for the version of ODBC supported by the driver, but was not supported by the driver.

MCM