Re: Questions about connection and timeout with Connector/J

[Frank Gates]

Normally a connection would not be closed after a few minutes.  Connection
pooling would not work (or work efficiently) if connections closed after just a
few minutes.  So, the problem may be in how your application obtains and manages
connections.

For example, if you are using a ConnectionPoolDataSource or are obtaining
connections from within an application server, then connection timeouts may
depend on how connection pooling is implemented or configured.   Some pools may
have a max checked out time for a connection, say a minute, before it forces the
physical connection to be returned to the pool.  The logical connection used by
the client would become invalid and cause SQLExceptions to be thrown.  See the
JDBC 3.0 spec, section 11.4.   While a max checked out time is not a requirement
of the spec, some implementations may do this.

Or, a simpler scenario is that your application is stepping on itself, where one
thread closes a connection that another assumes is still open.  Or your network
may be unreliable and causing the connection to be lost.

If your application is obtaining its connections manually (through
datasource.getConnection ()), then, to debug this you could create a logical
connection class that implements the Connection interface and receives the
physical connection as a constructor parameter.   The logical connection would
log all activity it has to the console or a file before calling the physical
connection (or maybe log only the close()).

If the app is stepping on itself, you should see the "Connection closed"
message; if the network is goofy, then you should have the same problem without
any closed messages.


public class SQLConnection implements Connection
{
  /**
   * this holds the physical connection.
   */
  protected Connection connection = null;

    public SQLConnection (Connection connection)
    {
        this.connection = connection;
    }
    . . .


  public void close () throws SQLException
  {
    System.out.println ("Connection closed.");
    // or do a "new Exception ("Connection closed").printStackTrace ();"
    connection.close ();
    connection = null;
  }
}


Frank



"LAFONTAINE Julien - LYO (jlafontaine@stripped)" wrote:

> Thanks for your answer Mark, and sorry for not checking the readme file
> before.
>
> But would it be possible that the connection is closed (or lost) if left
> unused for only a few minutes (and not 8 hours or what else is specified by
> wait_timeout or interactive_timeout)
> I'm using Connector/J 2.13 and MySQL 2.23.49 but I haven't seen anything
> about a bug that could cause that in the change log
> of MySQL and Connector/J.
>
> Thank you again for your answer.
>
> Regards
>
> Julien
>
> -----Message d'origine-----
> De : Mark Matthews [mailto:mark@stripped]
> Envoyé : mercredi 12 février 2003 14:59
> À : LAFONTAINE Julien - LYO (jlafontaine@stripped)
> Cc : java@stripped
> Objet : Re: Questions about connection and timeout with Connector/J
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> LAFONTAINE Julien - LYO (jlafontaine@stripped) wrote:
> > Hi everyone,
> > I've got a few questions about connection in MySQL and Connector/J. It
> would
> > help me a lot if someone could give me any information.
> > What happens if a Connection object is left unused for a few minutes.
> Could
> > the connection possibly be lost or at least closed by a MySQL server or
> the
> > driver? Then would a SQLException be thrown when a statement is created or
> a
> > query executed on a Statement object created by this connection? It seemed
> > to me that the MySQL server would closed the connection after
> "wait_timeout"
> > or "interactive_timeout" seconds.
>
> You get an SQLException.
>
> This is covered in the README that comes with Connector/J :)
>
> Look at the bottom, in the 'troubleshooting' section.
>
>         -Mark
>
> - --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
> For technical support contracts, visit https://order.mysql.com/?ref=mmma
>
>      __  ___     ___ ____  __
>     /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
>    / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
>   /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
>          <___/ www.mysql.com
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.1.90 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQE+SmEhtvXNTca6JD8RAuZyAKCdmM1F0lMQsOVhj0v3+0gGjiX9zwCff1Sr
> VWAHQ3Op50bV4gf8p6+e+t0=
> =5od7
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/doc/         (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <java-thread5031@stripped>
> To unsubscribe, e-mail
> <java-unsubscribe-jlafontaine=lyon-partdieu.sema.slb.com@stripped>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/doc/         (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <java-thread5032@stripped>
> To unsubscribe, e-mail <java-unsubscribe-fgates=yahoo.com@stripped>