RE: Questions about connection and timeout with Connector/J

[LAFONTAINE Julien - LYO ([e-mail])]

Hi everyone!

Thanks to your answers I think I've found what was wrong with my program.
As Frank suggested, it was a problem with the way I was managing my
connections.
The DB was accessed by sevral classes who all had in common the same
abstract super class. The connection object was a static attribute of the
super class.
After class was instanciated to access the database. It seems that the
Connection object was somehow kept alive by our application server (a home
made one). And if more that 8 hours went by until another object was
instaciated to access the databases, an SQLException occured as MySQL has
closed the connection that the Connection object was try to use.

I've got the following Exception:
java.sql.SQLException: Communication link failure: java.io.IOException
  at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
  at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
  at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
  at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
  at org.gjt.mm.mysql.Statement.executeUpdate(Unknown Source)
  ...

I had a look at Connector/J 2.0.13's source code and it seems that this
exception could be sent when the Driver try to use a connection that has
been closed by MySQL. But I'd be really grateful if someone with a better
understanding of how the MysqlIO class work could give me is opinion.

Once again thanks for your help everyone!

Julien

Driver: Connector/J 2.0.13
OS: AIX 4.3.3
JDK IBM 1.3.1

-----Message d'origine-----
De : Frank Gates [mailto:fgates@stripped]
Envoyé : mercredi 12 février 2003 17:33
À : Gustavo Cebrian
Cc : LAFONTAINE Julien - LYO (jlafontaine@stripped);
Mark Matthews; java@stripped
Objet : Re: Questions about connection and timeout with Connector/J


Hi,

<resending because it was bounced by the listserver... sorry if this is
a duplicate...>

The issue I was addressing with Julien is a possibility only; not every
connection pool will implement a maximum check out time.   The pools
that do
have a max time should document this.   (A small timeout, like a minute,
is probably reasonable for server side connections, but could be a
problem for
user interfaces.)

The ConnectionPoolDataSource is an interface of JDBC 3.0.   Connection
pools written for jdbc 3.0 will probably use this interface.  Most
application
writers never need to know about it.  I mentioned it because some people
may have implemented their own connection pooling.  I ought to have been

more clear.  Sorry.

I am not sure that I addressed your concerns, but let me know if there
is anything else.

Frank


Gustavo Cebrian wrote:

> I use Connection Pool and works quite well.
>
> At the end of the day we need to make a query any time.
>
> The Pool Connection Class actually opens connections if they get
> close. It is possible to specify the maximun number of connections
>
> However, I do not know much about performance problems.
>
> What is exactly ConnectionPoolDataSource
>
> Do we need this kind of problems with all the databases.
>
> Sorry, I am sure there is something I do not understand.
>
> Gustavo.
>
>  Frank Gates <fgates@stripped> wrote:
>
>      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
>      >
>      > __ ___ ___ ____ __
>
>
> -----------------------------------------------------------------------
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day