The second message -- connection has already been closed -- most likely arises
from my code structure, which is:
Connection connection = null;
PreparedStatement prepStmt = null;
try
{
Connection connection = getConnection();
PreparedStatement prepStmt = connection.prepareStatement(...);
...
}
finally
{
if (prepStmt != null) prepStmt.close();
if (connection != null) connection.close();
}
The call to getConnection() in the try block gets a connection from the pool.
But the server has closed this connection. Therefore the call to
prepareStatement throws a SQLException which is the last packet exception.
And we then enter the finally block, which tries to close the connection, and
we get a new SQLException that the connection has already been closed.
In some places in my code I solve the issue of throwing a new exception by a
new local variable as follows:
SQLException caught = null;
Connection connection = null;
PreparedStatement prepStmt = null;
try
{
Connection connection = getConnection();
PreparedStatement prepStmt = connection.prepareStatement(...);
...
}
catch (SQLException e)
{
caught = e;
throw e;
}
finally
{
if (prepStmt != null)
{
try
{
prepStmt.close();
}
catch (SQLException e)
{
if (caught == null) caught = e;
}
}
if (connection != null)
{
try
{
connection.close();
}
catch (SQLException e)
{
if (caught == null) caught = e;
}
}
if (caught != null) throw caught;
}
But it's just too much code to write!
Probably with the validationQuey I won't have to worry about the last packet
exception. Let me try it out.
I removed minEvictableIdleTimeMillis and testWhileIdle.
Is there any doc about the ping in "/*ping*/ select 1"?
And yes, I got these errors even without my driver.
FYI, my driver class is
import com.mysql.jdbc.NonRegisteringDriver;
public class DecryptPasswordMysqlDriver extends NonRegisteringDriver
{
public static final String urlPrefix = "mydriver@";
private static final String usernamePropertyName = "user";
private static final String passwordPropertyName = "password";
static
{
try
{
DriverManager.registerDriver(new DecryptPasswordMysqlDriver());
}
catch (SQLException e)
{
throw new RuntimeException("Can't register " +
DecryptPasswordMysqlDriver.class.getName());
}
}
public DecryptPasswordMysqlDriver() throws SQLException
{
// Required for Class.forName().newInstance()
}
public boolean acceptsURL(String string) throws SQLException
{
if (string.startsWith(urlPrefix))
{
return super.acceptsURL(string.substring(urlPrefix.length()));
}
return false;
}
public Connection connect(String string, Properties properties) throws
SQLException
{
if (!acceptsURL(string)) return null;
// when the connection is created,
// the username and password come from context.xml and are therefore
encoded
// when the connection is borrowed from the pool,
// the username and password come from values saved by the pool, which
are the decoded values
String encodedPassword = (String) properties.get(passwordPropertyName);
if (MapUtils.canDecrypt(encodedPassword))
{
String username = (String) properties.get(usernamePropertyName);
String password = MapUtils.decrypt(username, encodedPassword);
username = baseUsername + '_' + username;
properties.put(usernamePropertyName, username);
properties.put(passwordPropertyName, password);
}
Connection connection =
super.connect(string.substring(urlPrefix.length()), properties);
return connection;
}
}
--- On Fri, 12/5/08, Christopher Schultz <[EMAIL PROTECTED]> wrote:
> From: Christopher Schultz <[EMAIL PROTECTED]>
> Subject: Re: configure JNDI to avoid error "Last packet sent to the server
> was xxxxx ms ago"
> To: "Tomcat Users List" <users@tomcat.apache.org>
> Date: Friday, December 5, 2008, 9:20 AM
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> To whom it may concern,
>
> [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
