tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <ch...@christopherschultz.net>
Subject Re: configure JNDI to avoid error "Last packet sent to the server was xxxxx ms ago"
Date Fri, 05 Dec 2008 17:20:30 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To whom it may concern,

removeps-groups@yahoo.com wrote:
> How to configure JNDI in order to avoid the error "Last packet sent 
> to the server was xxxxx ms ago."? I've seen two other errors which
> seem to be from the same cause: "Communications link failure" and 
> "Connection.close() has already been called".

I think the first one is a MySQL error message, so properly configuring
your DataSource is key. The second one looks like you are being sloppy
with your connections and closing them multiple times.

> The my.custom.mysql.Driver class decodes and decrypts the password,
> and passes these to the base class, which is
> com.mysql.jdbc.NonRegisteringDriver.

Aah, the old "why can't I encrypt my password in my <Resource>"
question. Do you have any of these problems if you just use the standard
MySQL driver (com.mysql.jdbc.Driver) directly? Just trying to rule-out
problems caused by your delegating driver. Could you post the entire
code of your Driver class?

> I read that the validationQuery="select 1" validates the connection
> before returning it to the user.  If the select statement fails, then
> the connection is removed from the pool and a new one is created.
> 
> What I'm not sure is if I need the testOnBorrow="true" and
> testWhileIdle="true".

The default value for testOnBorrow is "true", so if you set a
validationQuery, it will be used. I wouldn't bother setting
testWhileIdle to "true".

> The MySQL connection timeout is 12 hours, so I set
> minEvictableIdleTimeMillis="43200000".

That's a looong time. I suppose it doesn't matter much, but why set the
timeout for so long?

> In my case, the last packet was sent about 57xxx milliseconds ago,
> which is less than a minute, which is much less than the 12 hour
> MySQL connection timeout.  Yet the error happens only after around 12
> hours.  I expect the error to be "... 43200000 ms ago" or something
> like that.

Yeah, I would expect that, too.

> Also, I read that the validationQuery adds performance overhead

Meh. It's a small price to pay to get known-good connections to the
database. Try a query like "/* ping */ SELECT 1". In recent versions of
the driver, this is the lightest-weight connection test you can perform,
since it won't actually issue a query: it just checks the connection
status to make sure you're good. If you have an older version of the
driver, the "ping" is ignored and you'll execute a "SELECT 1" query
which is pretty fast.

> and maybe the Java code should handle exceptions.

I'm not sure what you mean, here.

> But how would this
> work?  I'm guessing you catch the SQLException, and if it is "Last
> packet sent to the server was xxxxx ms ago" then call
> connection.reconnect and attempt the operation again.  But
> java.sql.Connection does not have a function reconnect.

Right: you'd need to close that connection (ignoring any errors) and get
a new connection from the pool. I wouldn't recommend this. Instead, use
validationQuery and let the pool do the checking for you.

> The other thing I could try in code, is that after getting the
> connection by calling ctx.lookup("java:comp/env/jdbc/myname"), is to
> call connection.isValid() or connection.isClosed(), and if the
> connection is closed then call something to remove it from the pool.
> But java.sql.Connection does not have a function
> removeMeFromThePoolIBelongTo().

All of this stuff should be taken care of by the pool manager, so forget
about it.

> Finally, I'm concerned about maxIdle="15" and removeAbandoned="true".
> If a connection is used for a SQL statement then closed, it becomes
> idle.  But then, will it be removed?  Or does removeAbandoned="true"
> only apply to connections that are still active (i.e.
> connection.close() has not been called on it) for
> removeAbandonedTimeout="30" seconds?

An "abandoned" connection is one that has been checked out of the pool,
but hadn't been returned by the time the removeAbandonedTimeout has
expired. So, if your code checks-out a connection and then never returns
it, that connection will eventually be removed from the pool (and
replaced with a new one). I would recommend enabling "logAbandoned" so
you'll get error messages when a connection is not returned to the pool.
You'll get a stack trace of where the connection was requested, so it
helps you track-down the place where you have a connection leak.

As for your "too many closes" problem, I suspect it is one of two problems:

1. Your custom driver is improperly delegating to MySQL's driver

2. Your application code is sloppy

I'll give you the advice I give everyone having JDBC connection
problems: make sure all your code looks like the following. Check /all/
your database accesses. Even one sloppy piece of code can leak
connections or cause a pileup of resources on your database server.

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try
{
   conn = ... (get your connection)

   /*
     ... do your query stuff.

     Do not close the connection, here!!

     If you close your ResultSet or Statement objects,
     set them to null, too.
   */
}
finally
{
  if(null != rs)
       try { rs.close(); }
       catch (SQLException sqle) { /* log me! */ }

  if(null != ps)
       try { ps.close(); }
       catch (SQLException sqle) { /* log me! */ }

  if(null != conn)
       try { conn.close(); }
       catch (SQLException sqle) { /* log me! */ }
}

If you are using autocommit=false, things get more complicated. Let me
know if you need the Full Monty.

- -chris

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkk5Yt4ACgkQ9CaO5/Lv0PB2lgCgt5tEVfaa3IimE90CqrUXkGPg
CmQAoJUXABF2vI5WQhgPHifY3fSyX7rE
=Ld95
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message