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.close90 has
already been called".
This is what I've come up with, but wonder if it is is good and efficient:
<Resource name="jdbc/myname"
auth="Container"
type="javax.sql.DataSource"
driverClassName="my.custom.mysql.Driver"
url="[EMAIL PROTECTED]:mysql://localhost:3306"
username="user"
password="encode:abcdefg"
initialSize="1" maxActive="15" maxIdle="15"
minEvictableIdleTimeMillis="43200000"
maxWait="5000" removeAbandoned="true" removeAbandonedTimeout="30"
logAbandoned="true"
testOnBorrow="true" testWhileIdle="true" validationQuery="select
1"/>
The my.custom.mysql.Driver class decodes and decrypts the password, and passes
these to the base class, which is com.mysql.jdbc.NonRegisteringDriver.
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 MySQL connection timeout is 12 hours, so I set
minEvictableIdleTimeMillis="43200000". From the doc at
http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/doc/jdbc-pool.xml,
it looks like this is the minimum time that the connection may sit idle in the
pool until it is considered for eviction/removal. So I set the value to 12
hours. So I imagine that after 12 hours, the validation query will run and it
will fail because the connection has been closed on the server, and so the idle
connection will be removed.
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.
Also, I read that the validationQuery adds performance overhead, and maybe the
Java code should handle exceptions. 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.
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().
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?
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
