-----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]
