Announcement

Collapse
No announcement yet.

Determine lost connection with remote SQL-DB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Determine lost connection with remote SQL-DB

    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.
    Software makes Hardware Happen

  • #2
    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
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      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!
      Software makes Hardware Happen

      Comment


      • #4
        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
        Don Dickinson
        www.greatwebdivide.com

        Comment


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

          SqlSetEnvAttr
          ...
          SQL_ATTR_CONNECTION_POOLING
          (ODBC 3.0)
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            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
            Don Dickinson
            www.greatwebdivide.com

            Comment


            • #7
              Originally posted by Michael Mattias View Post
              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".

              Comment


              • #8
                >>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
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment

                Working...
                X